Link to home
Start Free TrialLog in
Avatar of wayneray
waynerayFlag for United States of America

asked on

Second SQL Query Not Populating Data Table

I am filling labels on a form from two Access Database tables using VB.Net. My first SQL statement works perfectly and populates the datatable correctly. The second database connection is not working correctly but the SQL statement appears to be written correctly. In the second SQL statement I want to pull the QTY value from two different rows. The error I get during build is because the second data table is empty. Here is my code and thanks in advance Experts.

Dim cmd As New OleDbDataAdapter
        Dim cmd2 As New OleDbDataAdapter
        Dim iCalc1 As String
        Dim lCalc2 As Long
        ' Dim ds As DataSet
        Dim dt As DataTable = New DataTable()
        Dim dt2 As DataTable = New DataTable()
        ' ds.Tables.Add(dt)

        iCalc1 = TxtPartN.Text
        lCalc2 = TxtQty.Text
        Using conn As OleDb.OleDbConnection = CreateConnection()

            conn.Open()
            cmd = New OleDbDataAdapter("Select Customer, [Matl Code], [Box Code], [Part Wt Grams], [Piece Box] from [Parts Data] where [Part Number] = '" & iCalc1 & "'", conn)
            conn.Close()
            cmd.Fill(dt)

            LblMCode.Text = dt.Rows(0).Item(1)
            LblBCode.Text = dt.Rows(0).Item(2)
            LblMatNeed.Text = dt.Rows(0).Item(3) * lCalc2 / 453.59237 & " Lbs"
            LblPCount.Text = dt.Rows(0).Item(4)
            LblBoxNeed.Text = lCalc2 / LblPCount.Text

        End Using
        Using conn As OleDb.OleDbConnection = CreateConnection()

            conn.Open()
            cmd2 = New OleDbDataAdapter("Select Qty from Bom where ItemCode = '" & LblMCode.Text & "'" & " and ItemCode = '" & LblBCode.Text & "'", conn)
            cmd2.Fill(dt2)
            conn.Close()

            LblMoHand.Text = dt2.Rows(0).Item(0)
            LblBoxNow.Text = dt2.Rows(1).Item(0)
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Be advised, you have potentially a couple of issues.

            LblMoHand.Text = dt2.Rows(0).Item(0)
            LblBoxNow.Text = dt2.Rows(1).Item(0)

Open in new window


If your result doesn't return at least 2 rows you have an exception thrown.

Your results are also not sorted, which means you could potentially have a mismatched result.
Avatar of wayneray

ASKER

I changed the "and" to "or" and all worked well. Thanks a bunch. It has been about 10 years since I wrote sql statements.