Syntax Error on Insert to Database

D

DonnaF

Active member
Joined
Mar 20, 2003
Messages
30
Im getting a syntax error on an Insert statement, but I dont see anything wrong with it. It has the correct database names, and they are in the correct order. All of the fields are defined as text on the database. Does anyone see anything wrong with this?

Thanks, Donna

DataAdapter1.InsertCommand.CommandText = _
"INSERT INTO Customers(CustomerID, FirstName, " & _
"LastName, Address, City, State, Zip, " & _
"Password) " & _
"VALUES (" & strCustID & " , " & _
"" & mFirstName & " , " & _
"" & mLastName & " , " & _
"" & mAddress & " , " & _
"" & mCity & " , " & _
"" & mState & " , " & _
"" & mZip & " , " & _
"" & mPassword & ")"
 
I have this code, that does the catch on the error. When the message box pops up, it says: Syntax Error in Insert/Into Statement. Thats all it says.

Try
DataAdapter1.InsertCommand.ExecuteNonQuery()
frmNew.lblMessage.Text = "Customer Number " & _
strCustID & " Registered Successfully"
Catch exceptionparameter As Exception
MessageBox.Show(exceptionparameter.Message)
End Try
 
Hi DonnaF,

you must use Apostrophe for your Statement.
e.g
INSERT INTO People (Name, Age)
VALUES (Bugs Bunny, 7).
I dont know the value of yours variables. (mFirstName and so on)


Regards Datahighway
 
sometimes, i take out the catch clause and just let it crash. the server error usually gives a little more detail as to what the problem is.
 
Code: 
DataAdapter1.InsertCommand.CommandText = "INSERT INTO Customers(CustomerID, FirstName, LastName, Address, City, State, Zip, Password) VALUES (" & strCustID & "," & mFirstName & "," & mLastName & "," & mAddress & "," & mCity & "," & mState & "," & mZip & "," & mPassword & ")"
Sorry for the long page formatting, but this seemed to work for me.
 
Code: 
DataAdapter1.InsertCommand.CommandText = "INSERT INTO Customers (CustomerID, FirstName, LastName, Address, City, State, Zip, Password) VALUES (@CustomerID, @FirstName, @LastName, @Address, @City, @State, @Zip, @Password)"

DataAdapter1.InsertCommand.Parameters("@CustomerID").SqlDbType = SqlDbType.Int
DataAdapter1.InsertCommand.Parameters("@CustomerID").Value = mCustomerID

DataAdapter1.InsertCommand.Parameters("@FirstName").SqlDbType = SqlDbType.NVarChar
DataAdapter1.InsertCommand.Parameters("@FirstName").Size = 64
DataAdapter1.InsertCommand.Parameters("@FirstName").Value = mFirstName

 etc...
 
Does anyone know if password is a VB reserved word? When I looked at the Windows generated code for the Insert Command, I noticed it had password bracketed like there was something special about it.

Me.OleDbInsertCommand1.CommandText = "INSERT INTO Customers(Address, City, CustomerID, FirstName, LastName, [Password]," & _
" State, Zip) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"

Password is what I called the field in the database, but maybe I should have called it something else.
 
Password is an SQL keyword, but i use it in my SQL tables all the time. Just wrap it in []s and you can use it with no problems.
 
Back
Top