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 ?
razzmusitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flavoCommented:
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_scarlet7Commented:
Hi razzmusit,
Try this:

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


Cheers!
will_scarlet7Commented:
Dave,
>> numberic

I like that word :o)
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

razzmusitAuthor Commented:

>>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 ;)
flavoCommented:
Typo :-(
"INSERT INTO t_text (text_Language)  VALUES (" + cdbl(addProduct__strLanguage) + ");"
Dave
razzmusitAuthor Commented:
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

flavoCommented:
> 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
razzmusitAuthor Commented:
Nope - that didn´t do the trick..
pootle_flumpCommented:
> 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, "'", """") + "') "
flavoCommented:
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_flumpCommented:
>> 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, "'", "")) + ")"
razzmusitAuthor Commented:
Returns the error:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'cdbl'
flavoCommented:
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
pootle_flumpCommented:
What value do you have in addProduct__strLanguage at the time of running the code?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
razzmusitAuthor Commented:
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_flumpCommented:
Lol - you need to account for that in your input validation. Check out Dave (flavo's post) - IsNumeric will identify nulls too

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.