Syntax error in INSERT INTO statement.

[Jasima DJ]

I wrote the following code for my submit button...

Private Sub tb_submit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles tb_submit.Click
Dim strSQL As String = _
"INSERT INTO Members
(userId,password,verify,email,country,addess) VALUES('" + tb_userId.Text
+ "', '" + tb_password.Text + "', '" + tb_verify.Text + "', '" +
tb_email.Text + "', '" + tb_country.Text + "', '" + tb_address.Text +
"')"
OleDbConnection1.Open()
Dim cmd As New OleDbCommand(strSQL, OleDbConnection1)
cmd.ExecuteNonQuery()
OleDbConnection1.Close()
End Sub
===========================================================

But i keep getting the following error.....I cant seem to fix...can you
elp me fix it? plzzz....

Syntax error in INSERT INTO statement.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in
INSERT INTO statement.

Source Error:


Line 53: OleDbConnection1.Open()
Line 54: Dim cmd As New OleDbCommand(strSQL, OleDbConnection1)
Line 55: cmd.ExecuteNonQuery()
Line 56: OleDbConnection1.Close()
Line 57: End Sub

 

[William \(Bill\) Vaughn]

It looks like you're trying to concatenate fields together. This should be
done using an & operator instead of a "+" operator . However, this is not a
good idea. I suggest you use a Command and a Parameters collection to
execute this INSERT. This will eliminate a number of potential issues (one
of which you might have already encountered). More importantly, it will help
prevent SQL injection attacks.

Unfortunately, you're using OleDb which means you can't simply turn on the
Profiler and see what's getting sent to the server for processing.

In any case I also suggest you add a Try/Catch block to the code to trap the
exceptions--this way you can tell what's really going on.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Stan Sainte-Rose]

Hi

Are you sure about this field :

(userId,password,verify,email,country,addess)

addess ?? isn't address ??

By the way, take care if a user writes some fields using quotes (')
I think you will get errors

Stan

 

[William Ryan eMVP]

First, you definitely want to use Parameters instead of concatenating SQL
together. Too many problems to mentinod, but if you try to insert "O'Ryan"
as a field, that will cause a syntax error b/c the rest of the string will
be interpreted differently.

You are also using a reserved word in your string and I know Access for one
will blow up with that and it's nothing but headaches.

But a breakpoiknt after the Dim strSQL line and make sure you have a valid
statement. and then change the field name.

HTH,

Bill