SQL and ACCESS DATABASE

[tvb2727]

I have this String that I am trying to insert information into a database. It Keeps giving me a-- '[Microsoft][ODBC Microsoft Access Drive] Syntax error in INSERT INTO statement.'

here is the code:

String stmtSource = "INSERT into tblsyst" + "(Box, Height1, Level1, Main)" + "values ('" + one + "', '" +
two + "', '" +
three + "', '" +
four + "')";

what is causing this error?

[rt_roh]

may be there is blank space missing between Main)" & "values ..
make it Main)" + " values...
similarly check for other places where there should be a blank inbetween concatenation..

[tvb2727]

Hey, I tried that and it still gives me the same error. I don't know whats causing all of this? Any more suggestions.

[LICKY]

Try placing the last " after the ; eg +");". You may also like to replace the + with an &.

[rt_roh]

what are the values of variables one two three and four? these are the 4 variables u r trying to insert. are these 4 having proper values

this doesn't seem to be access syntax the way it is written-i.e. the string declaration. can u tell how exactly have u written this code.

are u able to run this qry in standalone mode i.e. straight as qry on database not from program. i.e. run straight on ur access db.

if this doesn't run from database then u will get a better description of error.

real wild guess.
- by any chance data type of any of the four fields is not string??

[LICKY]

Had a closer look at your problem and came up with the following routine. It is an append query, not a make table query. The following was written for Access. You don't need the single quote, eg ' , for numeric values.

Function test()

Dim stmSource As String
Dim one As String, two As String, three As String, four As String

one = "One"
two = "Two"
three = "Three"
four = "Four"

stmSource = "INSERT INTO tblsyst ( Box, Height1, Level1, Main ) Values ('" & one & "','" & two & "','" & three & "','" & four & "');"
DoCmd.RunSQL (stmSource)

End Function

[S'qeghl]

Man, SQL in Access is a frickin' headache!

[Cowski]

I ran this through a quick VB6 project I threw together. The syntax is fine. The question that remains unanswered is, What datatype fields are you inserting this data into? For example if Height1 is a numeric, your sending a character value to it by encapsulating it in the tick ' marks. It would be like trying to send the word 'cat' to a field where only numerics are allowed.

Check the datatypes of each field your sending. Chances are that's where your issue is at.

And trust me....SQL in Access is cake! It's all the obscure databases that are rough.

[yasserhy]

instead of using the regular connection varbiable:

dbPath = Server.MapPath("datasource.mdb")
dbConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
dbConnection.Open dbPath

you can use one of the following:

1. dbPath = Server.MapPath("datasource.mdb")
dbConnection.Provider = "Microsoft Access Driver (*.mdb)"
dbConnection.Open dbPath

2. dbPath = Server.MapPath("datasource.mdb")
dbConnection.ConnectionString= "Driver={Microsoft Access Driver (*.mdb)};DSN=;DBQ=" & dbPath
dbConnection.Open