Filling a listbox with multiple rows from a mysql command

[corey19981]

I'm new at mysql.. wanted some help if you wouldn't mind.

this is what I have:

 Dim conn As MySql.Data.MySqlClient.MySqlConnection
    Dim sqlquery As String
    Dim myAdapter As New MySql.Data.MySqlClient.MySqlDataAdapter()
    Dim myData As MySql.Data.MySqlClient.MySqlDataReader
    Dim myCommand As New MySql.Data.MySqlClient.MySqlCommand()
Dim i As Integer = 0

 conn = New MySql.Data.MySqlClient.MySqlConnection()
        conn.ConnectionString = "server=0.0.0.0; user id=username; password=password; database=database1"
        'see if connection failed.
        Try
            conn.Open()
        Catch myerror As System.Data.SqlClient.SqlException
            MessageBox.Show("Error Connecting to login server. program cannot run without a connection! " & myerror.Message)
            Me.Close()
        End Try




 sqlquery = "SELECT username FROM pool WHERE css='1' LIMIT 0 , 30"
            myCommand.Connection = conn
            myCommand.CommandText = sqlquery
            myAdapter.SelectCommand = myCommand
            myData = myCommand.ExecuteReader()
            myData.Read()

            If myData.HasRows = True Then
                Dim ddddd As Object
                i = 0
                For Each ddddd In myData.Item(i)
                    ListBox1.Items.Add(myData.Item(i))
                    i = i + 1

                Next

            Else
                MessageBox.Show("no accounts found")
            End If

With that code, the first account (item index 0) loads into the listbox, but it when it goes round the loop again, to try to input the next row (index 1), it says this error:

Index was outside the bounds of the array.


so I can easily do it if there is only 1 row, but if I want to display multiple results in the listbox it always comes up with this error.

could anyone help?


I'm using .net mysql connector by the way

Thanks you

[gep13]

Hello,

From the look of it, you are not actually looping through the records in the reader. You use myData.Read() once, which moves to the first record, and this gets loaded into the ListBox, but you need to call myData.Read() again, in order to move to the next record. Rather then use the For Loop that you have there.

I normally use a While myData.Read() loop to loop through all the records in the Reader. I don't have any example at hand to show you, but hopefully you will figure it out.

If not, post back.

Gary

[corey19981]

so if i keep saying mydata.read over and over it will automatically move to the next row?

I tried that and it's the method I'm using but it requires hitting the database more than once.

I'd rather load the entire table in a single sql command and then parse the stuff out of it.

[gep13]

Hey,

Using a DataReader means that you are inspecting the information once row at a time, and that it is forward only, meaning that once you inspect a row, and move to the next one, you can't then go back again. This technique is vary useful, but it depends on what it is you are trying to do with the data, and this is down to you.

Have a look at jmcilhinney's post here:

http://www.vbforums.com/showthread.p...hlight=ADO.NET

It explains all the different methods that you can return data from your database. Hopefully it will then be clear what approach is right for you. Don't worry that the example is specifically written for SQL Server, the examples are interchangable with MySQL, normally by simply writing My and the start of each object.

Hope that helps!!

Gary