Syntax Error on Insert to Database

[DonnaF]

I'm getting a syntax error on an Insert statement, but I don't 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 & "')"

[kleptos]

Does it produce any errors?

[DonnaF]

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. That's 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

[Datahighway]

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

[LostProgrammer]

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.

[kleptos]

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.

[Derek Stone]

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...

[DonnaF]

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.

[kleptos]

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.