CODEWITHSUNDEEP
vb.net
Randy
Randy

Reputation: 127

Displaying multiple records

  Private Sub Line_Change2()
    Dim cn As New SqlClient.SqlConnection("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")
    Dim cmd As New SqlClient.SqlCommand
    Dim tbl As New DataTable
    Dim da As New SqlClient.SqlDataAdapter
    Dim reader As SqlClient.SqlDataReader
    Try
        cn.Open()
        Dim sql As String
        sql = "select Id,Payroll_Id,ProductCode,Description,Qty from dbo.SmLine where Payroll_Id ='" + Txt1.Text + "'"
        cmd = New SqlClient.SqlCommand(sql, cn)
        reader = cmd.ExecuteReader
        While reader.Read
            TextBox1.Text = reader.Item("Id")
            Cmb1.Text = reader.Item("ProductCode")
            Des1.Text = reader.Item("Description")
            Qty1.Text = reader.Item("Qty")

            TextBox2.Text = reader.Item("Id")
            Cmb2.Text = reader.Item("ProductCode")
            Des2.Text = reader.Item("Description")
            Qty2.Text = reader.Item("Qty")

            TextBox3.Text = reader.Item("Id")
            Cmb3.Text = reader.Item("ProductCode")
            Des3.Text = reader.Item("Description")
            Qty3.Text = reader.Item("Qty")

        End While
        cn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

I am new to vb coding just want to help with displaying multiple rows on multiple textboxes. Above code picks up Payroll Id from a textbox from another table and then it goes through dbo.Smline table below. I want to display the multiple records under the same payroll Id in different textboxes. This code doesn't seem to be working properly.

My Table example

VB Form

Upvotes: 1

Views: 104

Answers (1)

Code Pope
Code Pope

Reputation: 5449

On your form you have three set of controls. Thus, you are able to display just up to three products for each clicked Payroll_id. Your code inserts the same value in all sets. Change your code to the following: 

 Private Sub Line_Change2()
    ResetControls()
    Dim cn As New SqlClient.SqlConnection("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")
    Using cn
        cn.Open()
        Dim cmd As New SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader
        Try
            Dim sql As String
            sql = "select Id,Payroll_Id,ProductCode,Description,Qty from dbo.SmLine where Payroll_Id ='" + Txt1.Text + "'"
            cmd = New SqlClient.SqlCommand(sql, cn)
            reader = cmd.ExecuteReader
            Dim counter as Integer = 1
            While reader.Read
                CType(me.Controls.Find("TextBox" + CType(counter,String),False)(0),TextBox).Text = reader.Item("Id").ToString()
                CType(me.Controls.Find("Cmb" + CType(counter,String),False)(0),ComboBox).Text = reader.Item("ProductCode").ToString()
                CType(me.Controls.Find("Des" + CType(counter,String),False)(0),TextBox).Text = reader.Item("Description").ToString()
                CType(me.Controls.Find("Qty" + CType(counter,String),False)(0),TextBox).Text = reader.Item("Qty").ToString()
                counter += 1
                if counter =3 then Exit While
            End While
            reader.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Using
End Sub
Public Sub ResetControls()
    For counter = 1 to 3
        CType(me.Controls.Find("TextBox" + CType(counter,String),False)(0),TextBox).Text = ""
        CType(me.Controls.Find("Cmb" + CType(counter,String),False)(0),ComboBox).Text = ""
        CType(me.Controls.Find("Des" + CType(counter,String),False)(0),TextBox).Text = ""
        CType(me.Controls.Find("Qty" + CType(counter,String),False)(0),TextBox).Text = ""
    Next
End Sub

The above code exits the reading when it has more than three products for a Payroll_id, because you just have three sets of controls. But if you could have more than three products for each clicked Payroll_id and you want to display all of them, then you have to build your controls dynamically.

Upvotes: 1

Related Questions