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

Re: HELP: Syntax error in INSERT INTO statement 80 80

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 & "')"
Re: HELP: Syntax error in INSERT INTO statement 80 80

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}

Re: HELP: Syntax error in INSERT INTO statement 80 80

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

Re: HELP: Syntax error in INSERT INTO statement 80 80

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

Re: HELP: Syntax error in INSERT INTO statement 80 80

What error are you getting in that code?

Re: HELP: Syntax error in INSERT INTO statement 80 80

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.

Re: HELP: Syntax error in INSERT INTO statement 80 80

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

Re: HELP: Syntax error in INSERT INTO statement 80 80
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 (')

Re: HELP: Syntax error in INSERT INTO statement 80 80

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

Re: HELP: Syntax error in INSERT INTO statement 80 80

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.

Re: HELP: Syntax error in INSERT INTO statement 80 80

the same error :$

Re: HELP: Syntax error in INSERT INTO statement 80 80

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

Re: HELP: Syntax error in INSERT INTO statement 80 80

still the same :(

Re: HELP: Syntax error in INSERT INTO statement 80 80
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

Re: HELP: Syntax error in INSERT INTO statement 80 80

Thaaanx alot 4 ur great effort

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

thanx

Re: HELP: Syntax error in INSERT INTO statement 80 80

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()
Re: HELP: Syntax error in INSERT INTO statement 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.