如何从Access数据库VB.net检索特定数据?

Question

我试图从客户帐户数据库中检索具有选定客户ID的特定值。我不确定如何做到这一点，这是我目前所拥有的。

我将从组合框中选择客户ID，那么我将如何在我的SQL代码中声明这一点?

Dim con As New OleDbConnection 'a new connection for the database is assigned 
con.ConnectionString = "Provider =Microsoft.ACE.OLEDB.12.0; data source= C:UsersHamzaDocumentsPOS system1.accdb "
con.Open()

Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
cmd = New OleDbCommand("Select Points from customers WHERE CustID= @CustID", con)  'query used to select all field
dr = cmd.ExecuteReader
txtdiscount.Text = Convert.ToInt32(dr)
con.Close()

Answer 1

不要把数据库代码和用户界面代码混在一起。

连接和命令应该调用它们的Dispose方法，以便它们可以释放非托管资源。Using...End Using块将为您处理此问题(并关闭连接)。

需要为@ID

添加一个参数。ExecuteScalar的使用在注释中说明。

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim ID = CInt(ComboBox1.SelectedItem)
Dim Points = GetPointsByID(ID)
txtdiscount.Text = Points
End Sub
Private Function GetPointsByID(ID As Integer) As String
Dim pts As String = ""
Using con As New OleDbConnection("Provider =Microsoft.ACE.OLEDB.12.0; data source= C:UsersHamzaDocumentsPOS system1.accdb "),
cmd As New OleDbCommand("Select Points from customers WHERE CustID= @CustID", con)
cmd.Parameters.Add("@CustID", OleDbType.Integer).Value = ID
con.Open()
pts = CStr(cmd.ExecuteScalar)
End Using
Return pts
End Function