SQL INSERT and Numbers

<i><b>Originally posted by : Mike Horton (mhorton@planet.eon.net)</b></i><br /><br />I'm using a form to get information to insert into a database. I know from using Response.Write that the information passed is correct but I keep getting the following error: Syntax error in INSERT INTO statement.<br /><br />Here's my INSERT code:<br />SQLstmt = "INSERT INTO Drivers (First Name,Last Name,Country,Helmet)"<br />SQLstmt = SQLstmt & " VALUES (" <br />SQLstmt = SQLstmt & "'" & First_Name & "',"<br />SQLstmt = SQLstmt & "'" & Last_Name & "',"<br />SQLstmt = SQLstmt & "'" & Country & "',"<br />SQLstmt = SQLstmt & "'" & Helmet & "'"<br />SQLstmt = SQLstmt & ")"<br /><br />Now Country and Helmet are numbers (I've got them tied into relationships). I've used this exact same code when all the fields were text and it worked great. I've also seen where you use a # around a date so I've tried that with now success. Any help would be greatly appreciatted. BTW there's also a Yes/No field in the table. Does that have to be filled on the INSERT statement?<br /><br />Thanks

<i><b>Originally posted by : Devendra Tiwari (smriti786@netzero.net)</b></i><br />To get around this problem try this...<br />Call function to convert string data type to<br />a appropriate number datatype (like integer,long,double or currency).<br />ex :<br />01. ccur for currency <br />var xyz<br />xyz = ccur("123.456")<br />02. cdbl for double<br />03. cdate for date<br />04. cint for integer.<br />05. clng for long.<br /><br />I hope this helps.<br /><br />Thanks,<br />Devendra.<br /><br />------------<br />Mike Horton at 1/29/2000 7:28:32 PM<br /><br /><br />I'm using a form to get information to insert into a database. I know from using Response.Write that the information passed is correct but I keep getting the following error: Syntax error in INSERT INTO statement.<br /><br />Here's my INSERT code:<br />SQLstmt = "INSERT INTO Drivers (First Name,Last Name,Country,Helmet)"<br />SQLstmt = SQLstmt & " VALUES (" <br />SQLstmt = SQLstmt & "'" & First_Name & "',"<br />SQLstmt = SQLstmt & "'" & Last_Name & "',"<br />SQLstmt = SQLstmt & "'" & Country & "',"<br />SQLstmt = SQLstmt & "'" & Helmet & "'"<br />SQLstmt = SQLstmt & ")"<br /><br />Now Country and Helmet are numbers (I've got them tied into relationships). I've used this exact same code when all the fields were text and it worked great. I've also seen where you use a # around a date so I've tried that with now success. Any help would be greatly appreciatted. BTW there's also a Yes/No field in the table. Does that have to be filled on the INSERT statement?<br /><br />Thanks

I know this is probably never going to help the original person, but google rated this article to my first page of results when I was looking for a similar answer.

*Quote*
Syntax error in INSERT INTO statement.<br /><br />Here's my INSERT code:<br />SQLstmt = "INSERT INTO Drivers (First Name,Last Name,Country,Helmet)"<br />SQLstmt = SQLstmt & " VALUES (" <br />SQLstmt = SQLstmt & "'" & First_Name & "',"<br />SQLstmt = SQLstmt & "'" & Last_Name & "',"<br />SQLstmt = SQLstmt & "'" & Country & "',"<br />SQLstmt = SQLstmt & "'" & Helmet & "'"<br />SQLstmt = SQLstmt & ")"<br /><br />Now Country and Helmet are numbers (I've got them tied into relationships). I've used this exact same code when all the fields were text and it worked great. I've also seen where you use a # around a date so I've tried that with now success. Any help would be greatly appreciatted. BTW there's also a Yes/No field in the table. Does that have to be filled on the INSERT statement?<br /><br />Thanks
*End Quote*

The SQL above looks like this:
INSERT INTO Drivers (First Name,Last Name,Country,Helmet)" VALUES ('" & First_Name & "','" & Last_Name & "','" & Country & "'," & Helmet & "')

The appropriate SQL should read as follows note the missing appostrophes and added brackets:
INSERT INTO Drivers (First Name,Last Name,[Country],[Helmet])" VALUES ('" & First_Name & "','" & Last_Name & "'," & Country & "," & Helmet & ")

This has been tested and worked correctly using MS Access Databases.

The appropriate SQL should read as follows note the missing appostrophes and added brackets:
INSERT INTO Drivers ([First Name],[Last Name],[Country],[Helmet])" VALUES ('" & First_Name & "','" & Last_Name & "'," & Country & "," & Helmet & ")