We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Populating Listbox with muliple columns from DataTable Rows

JackW9653
JackW9653 asked
on
Medium Priority
558 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks miketonny, solution was excellent.
JackW9653

Author

Commented:
Thanks CodeCruiser but miketonny's solution worked and I had already started with him.
Jack
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.