• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

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
0
JackW9653
Asked:
JackW9653
  • 3
  • 3
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now