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

Question

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()

You are missing adding an OleDbParameter to the OleDbCommand. — HardCode
– HardCode, Commented Oct 28, 2021 at 19:38
...also, your current code requires you to dr.Read() the reader, or use ExecuteScalar. — LarsTech
– LarsTech, Commented Oct 28, 2021 at 19:41
The other thing to consider, if you're only returning a single value from the DB, you could consider using ExecuteScaler. Also note, dr is an object that contains properties for each field returned that you then have to read — Hursey
– Hursey, Commented Oct 28, 2021 at 19:41
Cant you use dLookup? support.microsoft.com/en-us/office/… — nicomp
– nicomp, Commented Oct 28, 2021 at 21:00

Mary · Accepted Answer · 2021-10-29 05:20:43Z

1

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

edited Oct 29, 2021 at 5:20

answered Oct 29, 2021 at 4:53

Mary

15.1k3 gold badges20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hamza Asim Over a year ago

Thank you! It worked.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related