asked on

Data type mismatch in criteria expression, when using INSERT INTO

When using an INSTER INTO statement i get a "Data type mismatch in criteria expression".
I am using VBscript and an Access database.
The field I am trying to write to is a number-field.

The code:

<%
if(Request("1Lang_ID") <> "") then addProduct__strLanguage = Request("1Lang_ID")
%>

<%
set addProduct = Server.CreateObject("ADODB.Command")
addProduct.ActiveConnection = MM_Seed2data_STRING
addProduct.CommandText = "INSERT INTO t_text (text_Language) VALUES ('" + Replace(addProduct__strLanguage, "'", "''") + "') "
addProduct.CommandType = 1
addProduct.CommandTimeout = 0
addProduct.Prepared = true
addProduct.Execute()

%>

what am i doing wrong ?

flavo

Hi razzmusit,

When refering to a numeric field, you don't need to wrap the value in ' (I'm guessing you mean text_Language is numberic data type)

Try this

addProduct.CommandText = "INSERT INTO t_text (text_Language) VALUES (" + addProduct__strLanguage+ ") "

Dave :-)

will_scarlet7

Hi razzmusit,
Try this:

"INSERT INTO t_text (text_Language) VALUES (" & Val(Replace(addProduct__strLanguage, "'", "''")) & ") "

Cheers!

will_scarlet7

Dave,
>> numberic

I like that word :o)

razzmusit

ASKER

>>Flavo
You solution generated the same error > "Data type mismatch in criteria expression"

>>will_scarlet7
Now I get " Type mismatch: 'Val' "

Perhabs I should convert it to a "numberic" value - which must be a third format I havent yet discovered - hehe ;)

flavo

Typo :-(
"INSERT INTO t_text (text_Language) VALUES (" + cdbl(addProduct__strLanguage) + ");"
Dave

razzmusit

ASKER

This is getting weirder and weirder - now I get the error:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: '[string: "INSERT INTO t_text ("]'
/seed2/add_prod_test.asp, line 41

flavo

> addProduct.CommandTimeout = 0

I think this should be a little higher, maybe? Never tried setting it to such a low value.

"Indicates how long to wait while executing a command before terminating the attempt and generating an error."

Maybe this is why there is an error???

Dave

razzmusit

ASKER

Nope - that didn´t do the trick..

pootle_flump

> addProduct.CommandTimeout = 0
I believe this means there is NO timeout, therefore it will keep trying to run until it returns a result or errors

What about i.e. a single double quote rather than a double single quote ;-)

VALUES ('" + Replace(addProduct__strLanguage, "'", """") + "') "

flavo

Are we 100% all table and field names are correct?

If all else fails, try wraping them in []

"INSERT INTO [t_text] ([text_Language]) VALUES (" + cdbl(addProduct__strLanguage) + ");"

pootle_flump

>> The field I am trying to write to is a number-field.

Oh! Do you not need to validate the input to check it is numeric (you can use IsNumeric)? Your replace implies that it is unlikely yo be. How about since you seem to expect single quotes in there):

"INSERT INTO [t_text] ([text_Language]) VALUES (" + cdbl(Replace(addProduct__strLanguage, "'", "")) + ")"

razzmusit

ASKER

Returns the error:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'cdbl'

flavo

Well Im guessing that addProduct__strLanguage isn't numeric

As pootle_flump eluded to earlier try this

If not IsNumeric(addProduct__strLanguage) Then
response.write("oh ohhh")
exit sub
else
addProduct.CommandText = "INSERT INTO [t_text] ([text_Language]) VALUES (" + addProduct__strLanguage + ");"
end if

ASKER CERTIFIED SOLUTION

pootle_flump

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

razzmusit

ASKER

Ah - "time of running the code" was the key - off course!!!!
"addProduct__strLanguage" had no value at the time of running the code.
thanxalot

pootle_flump

Lol - you need to account for that in your input validation. Check out Dave (flavo's post) - IsNumeric will identify nulls too