Do while loop with use datatable

[dre-logics]

I have a simple question.
Do while loop to check each record in a MySQL Table, see code
But i want certainly not just want not use recordset or MySqlDataReader.
I Is that possible?

Information:
I use Visual Basic 8.0 and Mysql 5.0
I have one table: Articles
Article nb, Arcticle Dis, Arcticle stock
1000 hamer 75
2000 saw 100
3000 drill 50
4000 Screwdrivers 150
etc....

Which code should I add the dots?
What code needs to add more?

Dim myConnString = "Database=test; Data Source=localhost; User Id='test'; Password=''test"
Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String
SQL = "SELECT * FROM articles order by articlenb"
conn.ConnectionString = myConnString
conn.Open()
myCommand.Connection = conn
myCommand.CommandText = SQL
myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)
.........
 Do while .......eof ()  'Article Tabel
   ........
   listbox1.items.add(........)
   ....... MoveNext  'next record 
  .........
  loop

Thanks in advance,

Andre

[Stevoni]

From looking at your loop statement I think I can provide a solution so long as your loop is not removing items from the datatable.

Try the following:

For Each drRow As DataRow In dtTemp.Rows
                listbox1.Items.Add(drRow.Item("ColumnName").ToString)
            Next

With this you wont have to use MoveNext or even the data adapter since you're already populating a table.

HTH

[dre-logics]

From looking at your loop statement I think I can provide a solution so long as your loop is not removing items from the datatable.

Try the following:

For Each drRow As DataRow In dtTemp.Rows
                listbox1.Items.Add(drRow.Item("ColumnName").ToString)
            Next

With this you wont have to use MoveNext or even the data adapter since you're already populating a table.

HTH

Thanks for the solution. It works perfectly.
I want still want to use do while Loop (Exit Do).
This for the following reason:
If a article number is found then the program must exit loop, this saves time and is much faster.
With the same conditions as before.
If so, can you provide example please?

Thanks again.

A healthy and happy 2010 wish!

[Stevoni]

If you want it to exit the for loop, find the article number and enter "Exit For" and the loop will exit.

Do loops and for next loops are almost identical in that you can exit both of them, but the for next loop automatically enumerates through the collection, hence why I recommend using it.

[dre-logics]

Thanks, now its works fine

Greets,

André

[SamR44]

Hi Steve,
I've been trying the code you included:

For Each drRow As DataRow In dtTemp.Rows
                listbox1.Items.Add(drRow.Item("ColumnName").ToString)
            Next

VS2008 (vb) asks for a datatype for dtTemp ("Name dtTemp is not declared").
I've tried a heap of different datatypes, what datatype would it be?

thanks
Sam

[Stevoni]

Hi Steve,
I've been trying the code you included:

For Each drRow As DataRow In dtTemp.Rows
                listbox1.Items.Add(drRow.Item("ColumnName").ToString)
            Next

VS2008 (vb) asks for a datatype for dtTemp ("Name dtTemp is not declared").
I've tried a heap of different datatypes, what datatype would it be?

thanks
Sam

Hey Sam,

dtTemp is a DataTable type. When writing that section, I used my own code to replace the original posters while loop which resulted in the variable name changing from myData to dtTemp. Sorry for the confusion.

If you'd like some more help, please provide the procedure that's giving the problem.

Hope this helps!

[SamR44]

Hi Steve,
thanks for replying so quickly!

I'm still getting errors.
I've taken a snapshot and attached the image to show the VS2008 error.
(btw line 176 in pic should be commented out)

[Stevoni]

Hi Steve,
thanks for replying so quickly!

I'm still getting errors.
I've taken a snapshot and attached the image to show the VS2008 error.
(btw line 176 in pic should be commented out)

Hey Sam,

You need to change two lines:
1) Delete

Dim dtTemp as DataTable

2) Change dtTemp to myData (since you kept the variable myData and that's what's getting populated at the top by the data adapter).

Hope that helps!

[SamR44]

thanks Steve,
It worked perfectly!

For anyone else who is looking for how you can loop through a recordset using VS2008 and connecting to mysql. I've included my now working code (thanks to Steve's help)

Dim myConnString = "Database=myDB; Data Source=192.168.1.100; User Id='auser'; Password='userpassword'"
        Dim conn As New MySql.Data.MySqlClient.MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim myData As New DataTable
        Dim SQL As String
        SQL = "SELECT * FROM teachers"
        conn.ConnectionString = myConnString
        conn.Open()
        myCommand.Connection = conn
        myCommand.CommandText = SQL
        myAdapter.SelectCommand = myCommand
        myAdapter.Fill(myData)

        For Each drRow As DataRow In myData.Rows
            ListBox1.Items.Add(drRow.Item("teacher_name").ToString)
        Next