Syntax error in INSERT INTO statement. in VB.net 2010

Question

I have been this problem for a week and searching every existing forum for an answer maybe this time that i post my own problem.

My problem was in saving a data in a database. I have a datagrid that was bind to it but appear nothing. I'm using .mdb access database. the mdb table name was tblinformation.

It appears that my problem was in INSERT INTO statement, because there was a msgbox appears that everytime i try to save a data from textbox. and lastly, I'm new to vb.net >..<

btw here's my code:

Imports System.Data.OleDb

Public Class frmbookinfo
Dim cnn As New OleDb.OleDbConnection

Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click
    Try
        cnn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\AmosBooks_System\AmosBooks_System\database.mdb")
        Dim command As String
        command = "INSERT INTO tblinformation(title, author, isbn, category, edition, pages, language, yearofpublication, bcode, price) VALUES (@title, @author, @isbn, @category, @edition, @pages, @language, @yearofpublication, @bcode, @price)"
        cnn.Open()
        Dim cmd As OleDbCommand
        cmd = New OleDbCommand(command, cnn)
        cmd.Parameters.AddWithValue("@title", txttitle.Text)
        cmd.Parameters.AddWithValue("@author", txtauthor.Text)
        cmd.Parameters.AddWithValue("@isbn", txtisbn.Text)
        cmd.Parameters.AddWithValue("@category", txtcategory.Text)
        cmd.Parameters.AddWithValue("@edition", txtedition.Text)
        cmd.Parameters.AddWithValue("@pages", txtpages.Text)
        cmd.Parameters.AddWithValue("@language", cmblanguage.Text)
        cmd.Parameters.AddWithValue("@yearofpublication", dtyearpub.Text)
        cmd.Parameters.AddWithValue("@bcode", txtbcode.Text)
        cmd.Parameters.AddWithValue("@price", txtprice.Text)


        cmd.ExecuteNonQuery()
    Catch exceptionObject As Exception
        MessageBox.Show(exceptionObject.Message)
    Finally
        cnn.Close()
    End Try
End Sub

Can you give more details as to what exception you are getting and when ? — Guru Kara, Apr 12 '13 at 05:24
+1 for including the code, -1 for not including the error message you get. ;-) — Heinzi, Apr 12 '13 at 05:24
You need a space here ".... tblinformation(title, ..." like this ".... tblinformation (title, ..." — Guru Kara, Apr 12 '13 at 05:26
Hint: If you remove the catch clause, Visual Studio will tell you *which line* threw the error. Alternatively, you can go to Debug/Exceptions and tell it to break also on handled exceptions, which has the same effect. — Heinzi, Apr 12 '13 at 05:26
[See this other SO question](http://stackoverflow.com/questions/1476770/oledbcommand-parameters-order-and-priority) - according to this, the `OleDbCommand` for MS-Access doesn't support named parameters (like the `SqlCommand` for SQL Server does) - you need to use `?` instead of named parameters. — marc_s, Apr 12 '13 at 05:29
@Guru Kara, I'm wishing to add my data that has been entered in the textbox to be placed in ms access. — Reign De Leon, Apr 12 '13 at 05:32
@Heizi here's the message. "Syntax error in INSERT INTO" it was in a form of msgbox. — Reign De Leon, Apr 12 '13 at 05:32
@heinzi, a new error appear somewhat like this, sorry but i can't post picture yet. OleDbException was unhandled Syntax error in INSERT INTO statement. highlighting: cmd.ExecuteNonQuery() — Reign De Leon, Apr 12 '13 at 05:41

score 3 · Answer 1 · answered Apr 12 '13 at 07:06

The syntax error is caused by the word LANGUAGE in the field names.
This word is reserved in JET-SQL and thus should be enclosed in square brackets

command = "INSERT INTO tblinformation(title, author, isbn, category, edition, pages, " + 
          "[Language], yearofpublication, bcode, price) VALUES " + 
          "(@title, @author, @isbn, @category, @edition, @pages, " + 
          "@language, @yearofpublication, @bcode, @price)"

Syntax error in INSERT INTO statement. in VB.net 2010

1 Answers1