populateing textbox based on combo selection

[wilsonchama]

Hi guys,
I am a new member to the group.

I am trying to build an inventory management application using VB.net 2008 express edtion and MS access 2007.

I have managed to load the Combo box (cmID) on form(frmSales) with product Ids from tblProducts.

I have two textboxes on the same form txtDescription and txtPrice. txtDescription should display product description from tblProducts and txtPrice should display the product price from the same table, based on the Product ID selected in combo box cmbID on the same form. I have tried to search for the code to make this work, but the suggestions I have gathered so far are not just doint it.
The code loading the combo box is:

Imports System.Data.OleDb
Public Class frmSales

    Public myConnToAccess As OleDb.OleDbConnection
    Dim mySQLCommand As OleDbCommand
    Dim mySQLStrg As String
    Dim ds As DataSet
    Dim da As OleDbDataAdapter
    Dim tables As DataTableCollection



    Private Sub bntMenu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bntMenu.Click
        frmMenu.Show()
        Me.Hide()
    End Sub

    Private Sub frmSales_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        myConnToAccess = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Delta\Documents\Visual Studio 2008\Projects\easysaling\posdb.accdb")
        myConnToAccess.Open()

        ds = New DataSet
        tables = ds.Tables

        da = New OleDbDataAdapter("SELECT pID from tblProducts", myConnToAccess)
        da.Fill(ds, "tblProducts")

        Dim view1 As New DataView(tables(0))

        With cmbID
            .DataSource = ds.Tables("tblProducts")
            .DisplayMember = "pID"
            .ValueMember = "pID"
            .SelectedIndex = 0
            .AutoCompleteMode = AutoCompleteMode.SuggestAppend
            .AutoCompleteSource = AutoCompleteSource.ListItems
        End With


    End Sub

The above code as I said earlier is successfully populating the combo box with product IDs, with the drop down property.
But, the code to fill the text boxes with Product description and price based on the Id selected in the combo box is not working. This is the code I have so far.

Private Sub cmbID_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbID.SelectedIndexChanged
        myConnToAccess = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\Users\Delta\Documents\Visual Studio 2008\Projects\easysaling\posdb.accdb")
        myConnToAccess.Open()


        Dim query As String = ("SELECT Description, Price FROM tblProducts WHERE pID = ' " & cmbID.Text & " ' ")
        Dim cmd As New OleDb.OleDbCommand(query, myConnToAccess)
        cmd.CommandText = query

        txtDescription.Text = cmd.ExecuteScalar().ToString()
        txtPrice.Text = cmd.ExecuteScalar().ToString()

        myConnToAccess.Close()


    End Sub

when I run this code, I am getting an error message "Object reference not set to an instance of an object"

May someone help me out please....

[Reverend Jim]

What line is giving the error?

You should use a DataReader. Try

Dim query As String

qry = "SELECT Description, Price " &
      "  FROM tblProducts " &
      " WHERE pID = ?"

Dim cmd As New OleDb.OleDbCommand(query, myConnToAccess)    
cmd.Parameters.AddWithValue("@parm", cmbID.Text)
Dim rdr As OleDbDataReader = cmd.ExecuteReader()

If rdr.Read Then
    txtDescription.Text = rdr.GetString(0)
    txtPrice.Text = rdr.GetString(1)
End If

rdr.Close()

[wilsonchama]

Thank you so much Jim.
This worked just like I wanted.
Thank you once again.