sql syntax help

[shadow314us]

Hi,

I am trying to insert textbox information into database using a simple SQL query, but it is throwing me an exception with the SQL statement syntax error. I know it should be something simple... but i couldnt.... so need ur inputs on this... here is the code iam working on

where myconn is the connection string to the database.

SQL = "INSERT INTO Table1(f1,f2,f3,f4,f5,f6,f7,f8) VALUES("
SQL = SQL & "'"
SQL = SQL & Text2.Text
SQL = SQL & "'"
SQL = SQL & ","
SQL = SQL & "'"
SQL = SQL & Text3.Text
SQL = SQL & "'"
SQL = SQL & ","
SQL = SQL & "'"
SQL = SQL & Text4.Text
SQL = SQL & "'"
SQL = SQL & ","
SQL = SQL & "'"
SQL = SQL & Text1.Text
SQL = SQL & "'"
SQL = SQL & ","
SQL = SQL & "'"
SQL = SQL & "ABC"
SQL = SQL & "'"
SQL = SQL & ","
SQL = SQL & "'"
SQL = SQL & Format(Now, "yyyymmdd")
SQL = SQL & "'"
SQL = SQL & ","
SQL = SQL & "'"
SQL = SQL & lblTime.Caption
SQL = SQL & "'"
SQL = SQL & ","
SQL = SQL & "'"
SQL = SQL & ""
SQL = SQL & "')"

Myconn.Execute SQL
Myconn.Close

[GaryMazzone]

ABC sould be inside single quotes. Also what is the backend DB, the date need to be in single quotes also or if Access the date, need to be inside pound signs (#). This goes for the time field also (if it is a date/Time field) or single qoutes in a text field.

[shadow314us]

i checked it, its not throwing me any error at that point, The only thing i more concerned is the closing of the " infront of the INSERT INTO statement.

[GaryMazzone]

So then what is the error and where is it at in the application? The problems I pointed out are still issues. Also if the are any single qoutes in the Text fields on the form the SQL statemment will not run because the parser thinks that the statement ends at the qoute.

[si_the_geek]

Why do you use so many lines? I'd use one per field, like this:

SQL = SQL & "'" & Text2.Text & "',"

I'd recommend adding "Debug.Print SQL" (without quotes) before you execute it - that way you can see (in the Immediate Window) exactly what SQL is being used.

Gary is completely right about the data types of the fields - you should use ' or # or nothing, depending on what data type each field is (and which database - as only Access uses # for dates).


As Gary mentioned, ut would be very useful if you could tell us exactly what the error message is.

[dee-u]

Also what is the backend DB, the date need to be in single quotes also or if Access the date, need to be inside pound signs (#). This goes for the time field also (if it is a date/Time field) or single qoutes in a text field.

When inserting records with a Date/Time field I think using single quote is just fine. I have the same thought as you until I encountered an error, I let the user enter the date in any format and when they used "April 9, 1980" then using the # failed so I used the single quote instead and it worked fine.

[GaryMazzone]

Yes the single qoute will also work. I simply use the # because that is what MS recomends. I alway format the date going into the database to the format that the database is expecting (normally short date and a second field for time as short time).