VB.net访问数据库错误 [英] Access Database Error with VB.net

问题描述

我最近使用Access数据库创建了一个应用程序.我使用OLEDBConnection，OLEDBDataAdapter和DataSet.直到保存功能都可以.当我使用OLEDBCommandBuilder和DataAdapter.Update编写保存功能时，它在INSERT INTO语句中显示语法错误.为什么会出现此错误?我不明白请有人帮我！

这是我的代码:

I have recently create an application using Access Database. I use OLEDBConnection, OLEDBDataAdapter and DataSet. It''s OK until Save function. When I write save function using OLEDBCommandBuilder and DataAdapter.Update, it shows Syntax Error in INSERT INTO statement. Why I got this error? I''m not understand. Please somebody help me!

Here''s my code:

Private Sub conSave()

    Dim con As OleDbConnection
    Dim conAdapter As OleDbDataAdapter
    Dim dbCommand As OleDbCommand
    Dim ds As New DataSet

    Dim cb As OleDbCommandBuilder

    con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\hp\Documents\dbLedger.mdb")
    con.Open()
    dbCommand = New OleDbCommand("SELECT * FROM tbItemList", con)
    conAdapter = New OleDbDataAdapter
    conAdapter.SelectCommand = dbCommand
    conAdapter.Fill(ds, "tbItemList")

    cb = New OleDbCommandBuilder(conAdapter)
    Dim dsNewRow As DataRow

    dsNewRow = ds.Tables("tbItemList").NewRow()

    With dsNewRow
        .Item("ID") = Val(lblID.Text)
        .Item("iName") = txtName.Text
        .Item("ItemType") = cbbItemType.Text
        .Item("UnitName") = txtUnit.Text
        .Item("SubunitName") = txtSubunit.Text
        .Item("DefaultUnit") = cbbDefaultUnit.Text
        .Item("Algorithm") = txtAlgorithm.Text
        .Item("Note") = txtNote.Text
    End With

    ds.Tables("tbItemList").Rows.Add(dsNewRow)

    conAdapter.Update(ds, "tbItemList")


    con.Close()
    con.Dispose()
    conAdapter.Dispose()
    dbCommand.Dispose()
    ds.Dispose()
    cb.Dispose()

    MsgBox("Save complete!")
End Sub

推荐答案

CommandBuilder使用SELECT语句字符串为其编写UPDATE语句.搞砸的是"SELECT *"部分.不要使用*返回所有列.拼出整个SELECT语句，指定要返回的所有列名.然后可以构建UPDATE语句.

使用CommandBuilder还有很多限制，但是从您的代码段来看，您并没有遇到任何限制.

The CommandBuilder uses the SELECT statement string to write an UPDATE statement for it. What''s screwing it up is the "SELECT *" portion. Don''t use * to return all columns. Spell out the entire SELECT statement, specifying all the column names that you want returned. Then the UPDATE statement can be built.

There are a bunch more restrictions for using CommandBuilder, but from your code snippet, you''re not hitting any of those.

这篇关于VB.net访问数据库错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！