Link to home
Start Free TrialLog in
Avatar of JackW9653
JackW9653Flag for United States of America

asked on

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
Avatar of miketonny
miketonny

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
Avatar of JackW9653

ASKER

Thanks for the reply but strItem is still repeating.
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
Avatar of Nasir Razzaq
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
ASKER CERTIFIED SOLUTION
Avatar of miketonny
miketonny

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
Thanks miketonny, solution was excellent.
JackW9653
Thanks CodeCruiser but miketonny's solution worked and I had already started with him.
Jack