HELP: Syntax error in INSERT INTO statement

[Ms.passion]

Hi everyone
I'm really exhausted with this error
I'm using visual studio and I'm trying to build a contact form
And all the information should be saved in contact table in the database

Would you please help me with this error?
I tried a lot to figure what's wrong, but I couldn't
I'm sure that the spelling is correct and the order of the field

each time I click submit no error except this {Syntax error in INSERT INTO statement}
What's wrong with my code?

Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
        Dim myConnection As OleDb.OleDbConnection
        Dim myCommand As OleDb.OleDbCommand
        Dim sql As String
        Dim ra As Integer
        myConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.Oledb.12.0;Data Source=N:\Web\cupcake\cupcakeDB.accdb;")
        Try
            myConnection.Open()
            sql = "Insert into contact (fname, email, msg) values ('" & txtname.Text & "', '" & txtmail.Text & "', '" & txtmsg.Text & "', )"
            myCommand = New OleDb.OleDbCommand(sql, myConnection)
            ra = myCommand.ExecuteNonQuery()
            lblmessage.Text = "msg sent " & ra
            myConnection.Close()
        Catch ex As Exception
            lblmessage.Text = ex.Message
        Finally
            myCommand = Nothing
            myConnection = Nothing
        End Try
    End Sub

Thanx in advance

[farooqaaa]

You have added an unexpecting comma ',' after & txtmsg.Text & "' . Try removing that.

sql = "Insert into contact (fname, email, msg) values ('" & txtname.Text & "', '" & txtmail.Text & "', '" & txtmsg.Text & "')"

[Ms.passion]

thanks alot for ur replay
Im wondering, beacuse I have the same code for a registeration form and it still give me the same error

anyway,

now another msg shows
{Index or primary key cannot contain a Null value}

[detoxx]

That can be solved through the Access database itself. Look in the design view of the table and in the field properties you can set a primary key or set that particular field to allow null values

[Ms.passion]

I can't find this property, what I just delete the primary key cause no need for it;)
but may I know in which part I can find this property???

one more question guys, can you figure what's wrong with this code too:$

Dim myConnection As OleDb.OleDbConnection
        Dim myCommand As OleDb.OleDbCommand
        Dim sql As String
        Dim ra As Integer
        myConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.Oledb.12.0;Data Source=N:\Web\cupcake\cupcakeDB.accdb;")
        Try
            myConnection.Open()
            sql = "Insert into users (firstname, lastname, DOB, phone, mobile, home, pobox, gender, city, username, password) values ('" & txtfname.Text & "', '" & txtlname.Text & "', #" & txtbirthdate.Text & "#, '" & txtphone.Text & "', '" & txtmobile.Text & "', '" & txthome.Text & "', '" & txtpobox.Text & "', '" & rbgender.SelectedValue & "', '" & txtcity.Text & "',   '" & txtnewuser.Text & "', '" & txtnewpass.Text & "')"
            myCommand = New OleDb.OleDbCommand(sql, myConnection)
            ra = myCommand.ExecuteNonQuery()
            lblmessage.Text = "Thank you for your regestration, you may log in now " & ra
            myConnection.Close()
        Catch ex As Exception
            lblmessage.Text = ex.Message
        Finally
            myCommand = Nothing
            myConnection = Nothing
        End Try
    End Sub

many thanx

[farooqaaa]

What error are you getting in that code?

[detoxx]

There is always a need for a primary key.

Typically I always create an auto incrementing "ID" field which for each input from the program so you dont need to include it in the INSERT sql statement.

This also allows you to record how many records there are, search by ID number and easily identify identicle records.

[Ms.passion]

What error are you getting in that code?

the same {Syntax error in INSERT INTO statement}

There is always a need for a primary key.

Typically I always create an auto incrementing "ID" field which for each input from the program so you dont need to include it in the INSERT sql statement.

This also allows you to record how many records there are, search by ID number and easily identify identicle records.

ahaaa , now I get it, thaaaanx a lot

[detoxx]

sql = "Insert into users (firstname, lastname, DOB, phone, mobile, home, pobox, gender, city, username, password) values ('" & txtfname.Text & "', '" & txtlname.Text & "', #" & txtbirthdate.Text & "#, '" & txtphone.Text & "', '" & txtmobile.Text & "', '" & txthome.Text & "', '" & txtpobox.Text & "', '" & rbgender.SelectedValue & "', '" & txtcity.Text & "',   '" & txtnewuser.Text & "', '" & txtnewpass.Text & "')"

whats with the hashes (#)? this is your problem. Replace them with the required syntax (')

[Ms.passion]

what I know that we use (#) when the field type is DATE
is it wrong??

[detoxx]

I'm not particularly up to scratch with SQL.

When inputting dates I've always used them as "date" in the database, but as a string in the program. Try without the hash and see what happens.

[Ms.passion]

the same error :$

[detoxx]

the same error :$

sql = "Insert into `users` (firstname, lastname, DOB, phone, mobile, home, pobox, gender, city, username, password) values ('" & txtfname.Text & "', '" & txtlname.Text & "', '" & txtbirthdate.Text & "', '" & txtphone.Text & "', '" & txtmobile.Text & "', '" & txthome.Text & "', '" & txtpobox.Text & "', '" & rbgender.SelectedValue & "', '" & txtcity.Text & "',   '" & txtnewuser.Text & "', '" & txtnewpass.Text & "')"

^^^^ try this ^^^^

[Ms.passion]

still the same :(

[detoxx]

sql = "Insert into `cupcakeDB`.`users` (firstname, lastname, DOB, phone, mobile, home, pobox, gender, city, username, password) values ('" & txtfname.Text & "', '" & txtlname.Text & "', '" & txtbirthdate.Text & "', '" & txtphone.Text & "', '" & txtmobile.Text & "', '" & txthome.Text & "', '" & txtpobox.Text & "', '" & rbgender.SelectedValue & "', '" & txtcity.Text & "',   '" & txtnewuser.Text & "', '" & txtnewpass.Text & "')"

only other thing I can think of

[Ms.passion]

Thaaanx alot 4 ur great effort

still having the same error :(
may I ask u what cause this statement???

thanx

[kvprajapati]

Thaaanx alot 4 ur great effort

still having the same error :(
may I ask u what cause this statement???

thanx

Always use Parametrized query,

sql = "Insert into [users] ([firstname], [lastname], [DOB], [phone], [mobile], [home], [pobox], [gender], [city], [username], [password]) values (@firstname, @lastname, @DOB, @phone, @mobile, @home, @pobox, @gender, @city, @username, @password)"

Dim cmd as new OleDBCommand(sql,cn)
cmd.Parameters.AddWithValue("@firstname",txtfname.Text)
cmd.Parameters.AddWithValue("@lastname",txtlname.Text)
....
....
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()

[Ms.passion]

adatapost

part of ur way was the solution of the problem
what I learned today that some words are reserved and can't be use as a table name or a field like username and password. it a new information for me:)

so to solve the problem just put a square brackets [] or whatever u call it:-/

sql = "Insert into [users] (firstname, lastname, DOB, phone, mobile, home, pobox, gender, city, [username], [password]) values ('" & txtfname.Text & "', '" & txtlname.Text & "', '" & txtbirthdate.Text & "', '" & txtphone.Text & "', '" & txtmobile.Text & "', '" & txthome.Text & "', '" & txtpobox.Text & "', '" & rbgender.SelectedValue & "', '" & txtcity.Text & "',   '" & txtnewuser.Text & "', '" & txtnewpass.Text & "')"

and it wooooooooork:icon_cheesygrin:
thanx all 4 ur help