INSERT INTO语句语法错误.在VB.net 2008

Question

我已经为这个问题找了一个星期了，在每个现有的论坛上搜索答案，也许这次我发布了我自己的问题。

accessdate is DateTimePicker Control, barcodeno in Number, year in Number

Private Sub btn_Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Save.Click
    Dim cmd As New OleDb.OleDbCommand
    If Not con.State = ConnectionState.Open Then
        con.Open()
    End If
    'cmd.Connection = con
    cmd.CommandText = "INSERT INTO Book_Entry (accessdate,barcodeno,accessno,callno,author,subject,title,edition,pub,publi_place,[year],pages,size,vendor,cost,remark) VALUES (@accessdate1,@barcodeno1,@accessno1,@callno1,@author1,@subject1,@title1,@edition1,@pub1,@publi_place1,@year1,@pages1,@size1,@vendor1,@cost1,@remark1)"
    cmd.Connection = con
    cmd.Parameters.AddWithValue("@accessdate1", DTPicker1.Text)
    cmd.Parameters.AddWithValue("@barcodeno1", txt_BarCode.Text)
    cmd.Parameters.AddWithValue("@accessno1", txt_Accession.Text)
    cmd.Parameters.AddWithValue("@callno1", txt_CallNo.Text)
    cmd.Parameters.AddWithValue("@author1", txt_Author.Text)
    cmd.Parameters.AddWithValue("@subject1", cmb_Subject.Text)
    cmd.Parameters.AddWithValue("@title1", txt_Title.Text)
    cmd.Parameters.AddWithValue("@edition1", txt_Edition.Text)
    cmd.Parameters.AddWithValue("@pub", cmb_Publication.Text)
    cmd.Parameters.AddWithValue("@publi_place1", txt_PubliPlace.Text)
    cmd.Parameters.AddWithValue("@year1", txt_Year.Text)
    cmd.Parameters.AddWithValue("@pages1", txt_Pages.Text)
    cmd.Parameters.AddWithValue("@size1", txt_Size.Text)
    cmd.Parameters.AddWithValue("@vendor1", cmb_Vendor.Text)
    cmd.Parameters.AddWithValue("@cost1", txt_Cost.Text)
    cmd.Parameters.AddWithValue("@remark1", txt_Remark.Text)
    cmd.ExecuteNonQuery()
    MsgBox("Successfully Inserted", MsgBoxStyle.Information)
    con.Close()
End Sub

Answer 1

我不确定主题是否为保留名称，但在访问中使用保留名称作为字段名称将提供语法错误。

在您的例子中，将第6列的名称从subject更改为其他未保留的名称，它就会奏效。

否则，您的查询似乎是合法的。

希望能解决你的问题。

Answer 2

我不相信OleDb支持使用@符号命名参数。您应该尝试使用?来代替以下查询:

cmd.CommandText = "INSERT INTO Book_Entry 
(accessdate,barcodeno,accessno,callno,author,subject,title,edition,pub,publi_place,[year],pages,size,vendor,cost,remark) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

Answer 3

使用all in[]括号可以解决这个问题谢谢朋友…

cmd.CommandText = "INSERT INTO Book_Entry ([accessdate],[barcodeno],[accessno],[callno],[author],[hehehe],[title],[edition],[pub],[publi_place],[year2],[pages],[size],[vendor],[cost],[remark]) VALUES (@accessdate1,@barcodeno1,@accessno1,@callno1,@author1,@subject1,@title1,@edition1,@pub1,@publi_place1,@year1,@pages1,@size1,@vendor1,@cost1,@remark1)" cmd.Connection = con cmd.Parameters.AddWithValue("@accessdate1", DTPicker1.Value) cmd.Parameters.AddWithValue("@barcodeno1", txt_BarCode.Text) cmd.Parameters.AddWithValue("@accessno1", txt_Accession.Text) cmd.Parameters.AddWithValue("@callno1", txt_CallNo.Text) cmd.Parameters.AddWithValue("@author1", txt_Author.Text) cmd.Parameters.AddWithValue("@subject1", cmb_Subject.Text) cmd.Parameters.AddWithValue("@title1", txt_Title.Text) cmd.Parameters.AddWithValue("@edition1", txt_Edition.Text) cmd.Parameters.AddWithValue("@pub1", cmb_Publication.Text) cmd.Parameters.AddWithValue("@publi_place1", txt_PubliPlace.Text) cmd.Parameters.AddWithValue("@year1", txt_Year.Text) cmd.Parameters.AddWithValue("@pages1", txt_Pages.Text) cmd.Parameters.AddWithValue("@size1", txt_Size.Text) cmd.Parameters.AddWithValue("@vendor1", cmb_Vendor.Text) cmd.Parameters.AddWithValue("@cost1", txt_Cost.Text) cmd.Parameters.AddWithValue("@remark1", txt_Remark.Text) cmd.ExecuteNonQuery() MsgBox("Successfully Inserted", MsgBoxStyle.Information) con.Close()