Displaying multiple records

Question

  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.

Do you have just up to three records for each `Payroll_Id`? What if you have more than three products for a `Payroll_Id`? You only have built three columns of control set — Code Pope, Mar 28 '19 at 17:07

Code Pope · Accepted Answer · 2019-03-29T11:23:16.010

1

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.

edited Mar 29 '19 at 11:23

answered Mar 28 '19 at 17:22

Code Pope

5,075
8
26
68

1

Good approach, but avoid using `+` operator for string concatenation. You could even use string interpolation `"Cmb" + (counter)` >>> `$"Cmb{counter}"` – djv Mar 28 '19 at 17:26
1

@djv This is correct but $ - string interpolation is a feature of C# 6.0 and I am not sure if he is using C# 6 or above. – Code Pope Mar 28 '19 at 17:33
I'm positive he isn't using C# 6. Anyway I always assume a question applies to the latest framework and compiler, unless otherwise indicated. This came with VB 14 in 2015. He can also choose from `String.Format("Cmb{0}", counter)` or the good ol' `"Cmb" & counter.ToString()`, but `+` to concatenate strings is a big no-no – djv Mar 28 '19 at 18:09
I wouldn't say that it is a big no-no. I know that MS recommends using & vs + to eliminate ambiguity while still stating that both can be used. There is a good comparison on this thread: https://stackoverflow.com/questions/3006153/ampersand-vs-plus-for-concatenating-strings-in-vb-net. Nevertheless, I have changed it to &. – Code Pope Mar 28 '19 at 18:22
1

`&` always concatenates strings. `+` has some ambiguity depending on the input. The linked answer falls apart when `Option Strict On` is added to the code, and your original answer wouldn't have compiled with it either. I think Joel stretched to make that answer and I wonder how often he follows that advice in practice. +1 to your answer by the way and sorry for nit-picking. (i will delete my comments here to leave your answer nice and clean - have a good one!) – djv Mar 28 '19 at 18:55
@CodePope He is not using any version of C#. If he is using VB.net. Version 14 or later string interpolation is available. However neither is correct. String Interpolation or string concatenation should NOT be used in sql statements. It opens the door to sql injection. Use parameters. – Mary Mar 28 '19 at 20:50
@CodePope You never use the DataAdapter or the DataTable. – Mary Mar 28 '19 at 20:54
@CodePope Please turn on Option Strict. This is a 2 part process. First for the current project - In Solution Explorer double click My Project. Choose Compile on the left. In the Option Strict drop-down select ON. Second for future projects - Go to the Tools Menu -> Options -> Projects and Solutions -> VB Defaults. In the Option Strict drop-down select ON. This will save you from bugs at runtime. Your code in your answer will not compile with Option Strict On. – Mary Mar 28 '19 at 21:00
@CodePope If your database objects expose a `Dispose` method they should be disposed. One method of ensuring that connections and such are closed and disposed is to use Using...End Using blocks. This works even when there is an error. – Mary Mar 28 '19 at 21:04
Hi Thanks for your reply's "Code Pope", I have used your code but Iam getting the below error "Conversion from string "TextBox" to type Couble is not valid". Please help – Randy Mar 29 '19 at 08:53
@Randy, it seems that you are working with option strict on. So I changed the code to work for you. – Code Pope Mar 29 '19 at 10:17
@Mary about sql injection: Your comment is absolutely correct, but that would go beyond the mentioned question here. His focus is on something else, so that my answer was. Also about Option Strict: This is not an mandatory setting and for the data he is working you won't get any problem at runtime. But you are right that this will make the code safer. Regarding `Close` the connection: Your comment is right again, but this would be beyond his asked question. Additionally you can also use `finally` statement to get rid of the opened connection even when there is an error. And by the way... – Code Pope Mar 29 '19 at 10:28
@Mary ... he has also to close the SqlDataReader. Nevertheless, I have changed the source to also correct this issues. In a nutshell, when answering a question I focus on the problem of the question and not correcting every small issue in the code of the OP which would are neglectable like unused variables you mentioned. – Code Pope Mar 29 '19 at 10:31
Thanks for all your help guys the code is working now I had to replace " + " with the "&" sign "CType(Me.Controls.Find("TextBox" & (counter), False)(0), TextBox).Text = reader.Item("Id")" to reslove the error above. How do I clear all the textbox values before moving to show the next record? – Randy Mar 29 '19 at 10:48
Either fill the textboxes directly with the new value or just use the above code and assign `""` to each `textbox.text` i.e.: `CType(me.Controls.Find("TextBox" + CType(counter,String),False)(0),TextBox).Text =""` – Code Pope Mar 29 '19 at 11:03
Thanks for your reply, but I dont think it will reslove my issue. The problem is just say when first record is selected all the text box rows get filled. On the second record it only fills two rows, so third texbox row shouldb be blank. But it holds the value's of the first record without clearing, because the second record diddnt have values to fill the third textbox row. Hope this makes sence. – Randy Mar 29 '19 at 11:13
@Randy, I have changed the code to also include your requirement. I was just not sure what the `DropDownStyle` of your combobox is. I assumed it is `DropDown` – Code Pope Mar 29 '19 at 11:23

Displaying multiple records

1 Answers1