Syntax error in INSERT INTO statement. 1

[A1Pat]

hi all,
I'm getting this error:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

and this is where the error indicates,

sql = "INSERT INTO tblCustomers(custName, password, idLoc, channelNo, active) VALUES(" & _
		"'" & custname & "', '" & password & "', " & idLoc & ", '" & channelNo & "', " & active & ")"
	objConn.Execute sql

the weird thing is that it's working OK when I tested in my work's server, but not in my friend's server where I actually want it to be. Also, the same error appears when I tested locally utilizing localhost.

Please help!

 

[monksnake]

What is this for?

active) VALUES(" & [!]_[/!]

<.

 

[Chopstik]

Pat, did you try to response.write your SQL statement before you attempt to execute it? It's possible (I've had it happen to me before) that either your idLoc or channel values are blank which would cause a syntax error.

monksnake, that indicates that he's carrying his statement to his next line. It's the equivalent to:

sql = "INSERT INTO tblCustomers(custName, password, idLoc, channelNo, active) VALUES("
sql = sql & "'" & custname & "', '" & password & "', " & idLoc & ", '" & channelNo & "', " & active & ")"
    objConn.Execute sql

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[DotNetGnat]

try this:

sql = "INSERT INTO tblCustomers(custName, [password], idLoc, channelNo, [active]) VALUES(" & _
        "'" & custname & "', '" & password & "', " & idLoc & ", '" & channelNo & "', " & active & ")"
    objConn.Execute sql

-DNG

 

[monksnake]

monksnake, that indicates that he's carrying his statement to his next line

Thanks for the info Chopstik.

VBScript coding pssst.



<.

 

[A1Pat]

Hello Chopstik, here are the response.write info I tested:

custname: Patrick
password: 123
idLoc: 1
channelNo: 1
active: 0

 

[A1Pat]

Thank you DotNetGnat, you did it!!!

the [] around the password and active saved the day. I guessed this is used for reserved words, isn't it?

 

[monksnake]

I think I see the problem.

Is password and/or channelNo defined as numeric in your database?

In your statement, you are treating them as strings.

channelNo is my guess, should be numeric.

<.

 

[A1Pat]

actually monksnake, my channelNo is string because it contents my checkbox values which allow more than 1 channel saving.

 

[DotNetGnat]

yes they are reserved words...

-DNG