PDA

View Full Version : Help "Data type mismatch in criteria expression."


malhyp
05-11-2006, 10:53 AM
Hi thre, I keep getting the following error when trying to update to an Access database.

Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
/html/user-info-standard.asp, line 150

CODE

If (Not MM_abortEdit) Then
' execute the update
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute <--------------------
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If

Anyone know how to trouble shoot this to find the reason?

malhyp
05-11-2006, 12:14 PM
I just noticed that if I enter a number rather than text, it works....

:(

Anyway around this by changing a little code?

davidj
05-11-2006, 01:33 PM
your using the wrong delimiters

>>[' for text]

>>[<none> for numerics]

>>[# for dates]

text = 'hello'
numeric = 5
date = #01/01/2006#

malhyp
05-11-2006, 01:39 PM
David, in which part of my code should I change that?

Update form, SQL or Access database?

davidj
05-11-2006, 01:52 PM
your update script will have some SQL like ...

UPDATE TABLE SET field=value

post this bit

malhyp
05-11-2006, 02:01 PM
This is the only thing that I can find that even looks like it.

MM_editConnection = MM_connSeek_STRING
MM_editTable = "tblSuppliers"
MM_editColumn = "UserName"
MM_recordId = "'" + Request.Form("MM_recordId") + "'"
MM_editRedirectUrl = "advertiser-basic-main.asp"
MM_fieldsStr = "Company_Name|value|christianName|value|familyName| value|Street|value|Suburb|value|State|value|PostCo de|value|Country|value|Telephone|value|Facsimile|v alue|Email|value|WebSite|value"
MM_columnsStr = "SupplierName|',none,''|ContactName|',none,''|Famil yName|',none,''|StreetAddress|',none,''|Suburb|',n one,''|iLocation|',none,''|PostCode|none,none,NULL |iCountry|',none,''|PhoneNumber|',none,''|Facsimil e|',none,''|EmailAddress|',none,''|WebsiteAddress| ',none,''"

malhyp
05-11-2006, 02:03 PM
' create the sql update statement
MM_editQuery = "update " & MM_editTable & " set "
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_editQuery = MM_editQuery & ","
End If
MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
Next
MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId
If (Not MM_abortEdit) Then

davidj
05-11-2006, 02:15 PM
please forgive me but i am a PHP guy.

please do a search in your pages for the word UPDATE

malhyp
05-11-2006, 02:21 PM
I did and the above is all I could find. Thats ok, it is a start.

Thanks David

davidj
05-11-2006, 02:28 PM
you have a var called MM_editQuery

find this in your code and do a response.write on it

response.write MM_editQuery = etc etc

this should print to the page when run the update statement in full

copy and paste this in this post

malhyp
05-12-2006, 08:24 AM
Error Message Was...

False
Microsoft JET Database Engine error '80040e14'

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

/html/advertiser-basic-details.asp, line 144

SQL and Line 144 is....

' create the sql update statement
response.write MM_editQuery = "update " & MM_editTable & " set "
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_editQuery = MM_editQuery & ","
End If
MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
Next
MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId
If (Not MM_abortEdit) Then
' execute the update
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute <--------------------- 144
MM_editCmd.ActiveConnection.Close
If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%>