正在从Microsoft Access数据库更新组合框中的项目

Question

我正在VB.NET中制作一个家具租赁系统，以更新Microsoft Access数据库中的ComboBox。这个更新ComboBox的代码不起作用；它只对一个项目有效，对其他项目显示"未找到记录"。

我的数据库使用Microsoft Access。

Public Class Form8
Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersjeevaDesktopVB Project18HU5A1015.accdb")
Private Sub update_Load(sender As Object, e As EventArgs) Handles MyBase.Load
cn.Open()
Dim cm As New OleDb.OleDbCommand("select * from customerinfo", cn)
Dim dr As OleDb.OleDbDataReader = cm.ExecuteReader
While dr.Read
ComboBox1.Items.Add(dr(0).ToString)
ComboBox2.Items.Add(dr(1).ToString)
ComboBox3.Items.Add(dr(2).ToString)
ComboBox4.Items.Add(dr(3).ToString)
End While
dr.Close()
cn.Close()
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim customername = TextBox1.Text
Dim customerid = TextBox2.Text
Dim customeraddress = TextBox3.Text
Dim customeraadharno = TextBox4.Text
Try
cn.Open()
Dim cmd As New OleDb.OleDbCommand()
cmd.CommandText = "Update customerinfo set customername='" + customername + "' where customername='" + ComboBox1.SelectedItem() + "'"
cmd.CommandText = "Update customerinfo set customerid='" + customerid + "' where customername='" + ComboBox2.SelectedItem() + "'"
cmd.CommandText = "Update customerinfo set customeraddress='" + customeraddress + "' where customername='" + ComboBox3.SelectedItem() + "'"
cmd.CommandText = "Update customerinfo set customeraadharno='" + customeraadharno + "' where customername='" + ComboBox4.SelectedItem() + "'"
cmd.Connection = cn
Dim i = cmd.ExecuteNonQuery
If i > 0 Then
MsgBox("Record is updated successfully")
Else
MsgBox("No record found")
End If
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
End Sub

Answer 1

不要更改customerid，您需要使用唯一的customerid来更新表。看看下面的例子：

Dim cn As New OleDb.OleDbConnection("Provider=...;")
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
cn.Open()
Dim adapter As New OleDb.OleDbDataAdapter("select customerid,customername from customerinfo", cn)
Dim dt As DataTable = New DataTable
adapter.Fill(dt)
ComboBox1.DataSource = dt
ComboBox1.DisplayMember = "customername"
ComboBox1.ValueMember = "customerid"
cn.Close()
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Try
Dim cmd As New OleDb.OleDbCommand()
cmd.Connection = cn
cmd.CommandText = "Update customerinfo set customername = @customername, customeraddress = @customeraddress, customeraadharno = @aadharno where customerid=@customerid"
cmd.Parameters.AddWithValue("customername", TextBox1.Text)
cmd.Parameters.AddWithValue("customeraddress", TextBox2.Text)
cmd.Parameters.AddWithValue("aadharno", TextBox3.Text)
cmd.Parameters.AddWithValue("customerid", ComboBox1.SelectedValue)
cn.Open()
cmd.ExecuteNonQuery()
MsgBox("Successfully update customerinfo")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub