当尝试将客户添加到数据库时(插入到语句oledb vb net中的语法错误)

Question

我正在做一项大学作业，我已经花了好几个小时想弄明白它，但我似乎不能让我的新客户把它保存到数据库中!如果你能看看我的代码，提出任何建议，或者让我知道更有效的方法，我将非常非常感激。下面我提供了一个示例代码。

首先，在表单加载时，我确定要放入数据库的新客户ID:

Private Sub frmRegister_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Me.CustomerTableAdapter.Fill(Me.DatabasePizzaPalaceDataSet.Customer)
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:Year 2Unit 17 Project PlanningWorkto doPizzaPalceProgramDatabasePizzaPalace.accdb"
    conn.Open()
    Dim Rows As Integer
    Dim sql As String = "SELECT * FROM Customer"
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet
    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds, "Customer")
    Rows = ds.Tables("Customer").Rows.Count
    NewCustomerID.Text = Rows + 1
    Customer_IDTextBox.Text = NewCustomerID.Text
    conn.Close()
End Sub

话虽如此，下面是我在单击保存按钮以通过新数据行添加记录时运行的代码。

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:Year 2Unit 17 Project PlanningWorkto doPizzaPalceProgramDatabasePizzaPalace.accdb"
    conn.Open()
    Dim inc As Integer
    Dim sql As String = "SELECT * FROM Customer"
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet
    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds, "Customer")
    inc = Customer_IDTextBox.Text
    If inc <> -1 Then
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("Customer").NewRow()
        dsNewRow.Item("Customer_ID") = Customer_IDTextBox.Text
        dsNewRow.Item("Username_Email") = Username_EmailTextBox.Text
        dsNewRow.Item("Password") = PasswordTextBox.Text
        dsNewRow.Item("First_Name") = First_NameTextBox.Text
        dsNewRow.Item("Surname") = SurnameTextBox.Text
        dsNewRow.Item("Mobile") = MobileTextBox.Text
        dsNewRow.Item("House") = HouseTextBox.Text
        ds.Tables("Customer").Rows.Add(dsNewRow)
        da.Update(ds, "Customer")
        MsgBox("New Record added to the Database")
        conn.Close()
        frmLogin.Show()
    End If
    'Dim cb As New OleDb.OleDbCommandBuilder(da)

    'Me.CustomerTableAdapter.Insert(Customer_IDTextBox.Text, Username_EmailTextBox.Text, PasswordTextBox.Text, First_NameTextBox.Text, SurnameTextBox.Text, MobileTextBox.Text, HouseTextBox.Text)
    'Me.CustomerTableAdapter.Fill(Me.DatabasePizzaPalaceDataSet.Customer)
    'Me.Validate()
    'Me.CustomerBindingSource.EndEdit()
        'Me.CustomerTableAdapter.Fill(DatabasePizzaPalaceDataSet.Customer)
        'Me.TableAdapterManager.UpdateAll(Me.DatabasePizzaPalaceDataSet)
    'da.Update(ds, "Customer")
    'MsgBox("You have been succesfully registerd with us. Thanks!")
    'conn.Close()
    'frmLogin.Show()
End Sub

在评论中你也可以看到我的老师提供的代码，我们应该改进，我只是希望找到一种方法，使这项工作!

非常感谢，非常感谢所有的帮助和建议。

Answer 1

我使用ds.Tables(0)代替ds.Tables("Customer")(或者您的表在DataSet内的任何索引)

    Dim con As New OleDbConnection(_myConn) ''_myConn should be your connection string
    con.Open()
    Dim da As OleDbDataAdapter
    Dim ds As New DataSet
    da = New OleDbDataAdapter("select * from customer", con)
    da.Fill(ds)
    Dim cb As New OleDbCommandBuilder(da)
    Dim dsNewRow As DataRow
    dsNewRow = ds.Tables(0).NewRow()
    dsNewRow.Item(1) = "1"
    dsNewRow.Item(2) = "Blah"
    dsNewRow.Item(3) = "Test"
    dsNewRow.Item(4) = "T"
    dsNewRow.Item(5) = "T"
    dsNewRow.Item(6) = "T"
    dsNewRow.Item(7) = "20000101"
    ds.Tables(0).Rows.Add(dsNewRow)
    da.Update(ds.Tables(0))
    con.Close()

实现数据库模式也很重要。如果第一列是标识自动递增列，则需要避免尝试向该列插入任何内容。我更喜欢使用Indexes，因为它更容易将列名拼写为字符串，尽管它可能不那么清楚。

因此，Customer_ID可能是(或不是)一个自动增量字段，这意味着试图将数据插入该列将导致错误。