Populating Listbox with muliple columns from DataTable Rows

Hello Experts,

I need to populate a ListBox with Rows in a DataTable. Here is my code:

 Dim dt As New DataTable

        Dim strName As String = txtQ_Name2.Text
        Dim strKey As String = ""
        Dim strQuestion As String = ""
        Dim strRespone As String = ""
        Dim strQ_Type As String = ""
        Dim strQ_Trigger As String = ""
        Dim strQ_Trigger_Value As String = ""
        Dim strItem As String = ""


        Dim strSelect As String = "SELECT q.Q_Key, p.Question, p.Response, q.Q_Type, q.Q_Trigger, " & _
           "q.Q_Trigger_Value, q.Sequence " & _
           "FROM tblQuestionnaire q " & _
           "Left Join LookupIQ_QuestionPool p ON p.Q_Key = q.Q_Key " & _
           "WHERE q.Q_Name = '" & strName & "'" & _
           "ORDER BY q.Sequence "

        Dim sqlDA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(strSelect, cn)
        sqlDA.Fill(dt)

        If dt.Rows.Count > 0 Then
            strKey = dt.Rows(0).Item("Q_Key").ToString
            strQuestion = dt.Rows(0).Item("Question").ToString()
            strRespone = dt.Rows(0).Item("Response").ToString()
            strQ_Type = dt.Rows(0).Item("Q_Type").ToString()
            strQ_Trigger = dt.Rows(0).Item("Q_Trigger").ToString()
            strQ_Trigger_Value = dt.Rows(0).Item("Q_Trigger_Value").ToString()
        Else : MsgBox("No Items to Fill")
        End If

        strItem = strKey + ", " + strQuestion + " - " + strRespone + " - " + strQ_Type + " - " + strQ_Trigger + " - " + strQ_Trigger_Value


        For Each row As DataRow In dt.Rows
            lbQuestions.Items.Add(strItem)
        Next row

This copies the contents from Row(0) (as expected) to lbQuestions however many times as there are Rows in the DataTable. Obviously I want is for each row to go to the next set of values.

Thanks in advance,

JackW9653
JackW9653Asked:
Who is Participating?
 
miketonnyCommented:
so after modifying the code should be:
 For Each row As DataRow In dt.Rows
            Dim strKey As String = ""
            Dim strQuestion As String = ""
            Dim strRespone As String = ""
            Dim strQ_Type As String = ""
            Dim strQ_Trigger As String = ""
            Dim strQ_Trigger_Value As String = ""
            Dim strItem As String = "" 
            strKey = row.Item("Q_Key").ToString
            strQuestion = row.Item("Question").ToString()
            strRespone =row.Item("Response").ToString()
            strQ_Type = row.Item("Q_Type").ToString()
            strQ_Trigger = row.Item("Q_Trigger").ToString()
            strQ_Trigger_Value =row.Item("Q_Trigger_Value").ToString()
            strItem = strKey + ", " + strQuestion + " - " + strRespone + " - " + strQ_Type + " - " + strQ_Trigger + " - " + strQ_Trigger_Value
            lbQuestions.Items.Add(strItem)
    Next 

Open in new window


the reason it was still repeating was because inside for loop i kept retrieving the value from first datarow by dt.rows(0). let me know if that helps.
0
 
miketonnyCommented:
from what i can tell, your variable strItem doesn't change,
you should include it into your For, next loop

    For Each row As DataRow In dt.Rows
            strKey = dt.Rows(0).Item("Q_Key").ToString
            strQuestion = dt.Rows(0).Item("Question").ToString()
            strRespone = dt.Rows(0).Item("Response").ToString()
            strQ_Type = dt.Rows(0).Item("Q_Type").ToString()
            strQ_Trigger = dt.Rows(0).Item("Q_Trigger").ToString()
            strQ_Trigger_Value = dt.Rows(0).Item("Q_Trigger_Value").ToString()
            strItem = strKey + ", " + strQuestion + " - " + strRespone + " - " + strQ_Type + " - " + strQ_Trigger + " - " + strQ_Trigger_Value
            lbQuestions.Items.Add(strItem)
    Next
0
 
JackW9653Author Commented:
Thanks for the reply but strItem is still repeating.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
miketonnyCommented:
sorry missed the declaration
put this
 Dim strName As String = txtQ_Name2.Text
        Dim strKey As String = ""
        Dim strQuestion As String = ""
        Dim strRespone As String = ""
        Dim strQ_Type As String = ""
        Dim strQ_Trigger As String = ""
        Dim strQ_Trigger_Value As String = ""
        Dim strItem As String = ""

Open in new window

inside for each loop
0
 
CodeCruiserCommented:
Try this code

For i As Integer = 0 To dt.Rows.Count - 1
     lbQuestions.Items.Add(dt.Rows(i).Item("Q_Key") & ", " & dt.Rows(i).Item("Question") & ", " & dt.Rows(i).Item("Response") & ", " & dt.Rows(i).Item("Q_Type") & ", " & dt.Rows(i).Item("Q_Trigger") & ", " & dt.Rows(i).Item("Q_Trigger_Value"))
Next
0
 
JackW9653Author Commented:
Thanks miketonny, solution was excellent.
JackW9653
0
 
JackW9653Author Commented:
Thanks CodeCruiser but miketonny's solution worked and I had already started with him.
Jack
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.