How to retrieve specific data from Access database VB.net?-CodePudding

I'm trying to retrieve a specific value from the customer account database which has the selected customer ID. I'm not sure how to do this exactly, this is what I have so far.

I will be selecting the customer ID from a combo box, so how exactly will I declare this in my code with 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:\Users\Hamza\Documents\POS 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()

CodePudding user response：

Don't mix you database code with you user interface code.

Connections and commands should have their Dispose methods called so they can release unmanaged resources. Using...End Using blocks will handle this for you (and closed the connection). You need to add a parameter for @ID.

The use of ExecuteScalar is addressed in comments.

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:\Users\Hamza\Documents\POS 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