VB.NET How to correctly loop through a result set

Question

I have looked at many different code snippets on this site looking that would show me how to do something that should be fairly simple once I have the knowledge.

I want to query a database table for an array of values and then populate a combobox with those results.

Here is what I have so far:

Public Sub getMachines()
    Try

            Dim SQL As String = "SELECT MachineName from machine"


            Form1.machineName.DisplayMember = "Text"
            Dim tb As New DataTable
            tb.Columns.Add("Text", GetType(String))

            Using cn As New MySqlConnection(ConnectionString)
                Using cmd As New MySqlCommand(SQL, cn)

                    For Each cmd As String In cmd
                        'I want to add each value found in the database to "tb.Rows.Add"
                        'tb.Rows.Add(???)
                    Next

                    Form1.machineName.DataSource = tb

                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
                cn.Close()
            End Using

    Catch ex As MySqlException
        MsgBox(ex.Message)
    End Try
End Sub

[Create](https://stackoverflow.com/q/18609175/11683) the DataTable with [`MySqlDataAdapter.Fill`](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbdataadapter.fill?redirectedfrom=MSDN&view=netframework-4.8#System_Data_Common_DbDataAdapter_Fill_System_Data_DataTable_) and [assign it](https://stackoverflow.com/a/13300595/11683) to combobox. — GSerg, Jul 22 '19 at 18:28
@GSerg, thank you for the push in the right direction. I'll read the links now. Thanks again! Question: Using the Fill method doesn't require me to have a table on the form, does it? — NaN, Jul 22 '19 at 18:32
Generally, we would use databinding instead of looping through the result. — Joel Coehoorn, Jul 22 '19 at 18:46
@JoelCoehoorn, thanks Joel, I'm very lost with this language so I need to keep reading. I don't know what databinding is, but I'll look it up. Thanks for the advice. — NaN, Jul 22 '19 at 19:00

djv · Answer 1 · 2019-07-22T19:54:41.643

0

If you just want the MachineName to be displayed in the ComboBox, then just use that as the DisplayMember; don't bother creating another column called Text.

Public Sub getMachines()
    Try
        Dim cmd As String = "SELECT MachineName from machine"
        Dim ds As New DataSet()
        Using con As New MySqlConnection(ConnectionString)
            Using da As New MySqlDataAdapter(cmd, con)
                da.Fill(ds)
                With Form1.machineName
                    .DisplayMember = "MachineName"
                    .ValueMember = "MachineName"
                    .DataSource = ds
                End With
            End Using
        End Using
    Catch ex As MySqlException
        MsgBox(ex.Message)
    End Try
End Sub

edited Jul 22 '19 at 19:54

answered Jul 22 '19 at 18:43

djv

15,168
7
48
72

Thanks djv. I've tried your code snippet and what I see in the combobox is: System.Data.DataViewManagerListItemTypeDescriptor. This isn't working as expected. – NaN Jul 22 '19 at 18:52
Also, I noticed that there is no loop. Shouldn't there be a loop to handle every value it finds in the database? – NaN Jul 22 '19 at 18:54
@NaN that's kind of the point of data binding. It points the control to a list of data. You just need to let it know which field will be used for display. Sorry it's not working. I translated it from [this answer](https://stackoverflow.com/a/5660011/832052). I personally would use some sort of ORM like [EF](https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework60.html) because a lot of this work will be done for you already. – djv Jul 22 '19 at 19:19
@NaN check out [this answer](https://stackoverflow.com/a/35351683/832052) which hints that there is a strange bug which has to do with the order in which the DisplayMember and the DataSoruce are set. I've changed my answer by swapping the order. Try that. – djv Jul 22 '19 at 19:54
Hi djv, and thanks for the help. I've tried your edit but I get nothing returned to the combobox, it's empty. Thanks for pointing out data binding and how it can help me. I think at this point I'll look in that direction. – NaN Jul 23 '19 at 11:01

Joel Coehoorn · Answer 2 · 2019-07-22T20:46:10.970

I'll show a few examples, including using parameters, since that is important.

First up, a quick translation to run the existing query and loop through the results:

Public Sub getMachines()
    Try    
        Dim SQL As String = "SELECT MachineName from machine"

        Using cn As New MySqlConnection(ConnectionString), _
              cmd As New MySqlCommand(SQL, cn)

            cn.Open()
            Using rdr As MySqlDatareader = cmd.ExecuteReader
                While rdr.Read()
                    Form1.machineName.Items.Add(rdr("MachineName"))
                End While
            End Using
        End Using
    Catch ex As MySqlException
        MsgBox(ex.Message)
    End Try
End Sub

But better practice for a method like this is to isolate data access for the UI. This method should return results to the caller, which can decide what do with them. So I'll show two methods: one to get the data, and the other to loop through it and set up the combobox:

Private Function GetMachines() As DataTable
    'No try/catch needed here. Handle it in the UI level, instead
    Dim SQL As String = "SELECT MachineName from machine"
    Dim result As New DataTable

    Using cn As New MySqlConnection(ConnectionString), _
          cmd As New MySqlCommand(SQL, cn),
          da As New MySqlDataAdapter(cmd)

          da.Fill(result)
   End Using
   Return result
End Function

Public Sub LoadMachines()
    Try
        For Each item As DataRow in getMachines().Rows
            Form1.machineName.Items.Add(item("MachineName"))
        Next
    Catch ex As MySqlException
        MsgBox(ex.Message)
    End Try
End Sub

Or, we can use DataBinding:

Private Function GetMachines() As DataTable
    Dim SQL As String = "SELECT MachineName from machine"
    Dim result As New DataTable

    Using cn As New MySqlConnection(ConnectionString), _
          cmd As New MySqlCommand(SQL, cn),
          da As New MySqlDataAdapter(cmd)

          da.Fill(result)
   End Using
   Return result
End Function

Public Sub LoadMachines()
    Try
        Form1.machineName.DisplayMember = "FirstName";  
        Form1.machineName.ValueMember = "City" 
        Form1.machineName.DataSource = GetMachines()  
    Catch ex As MySqlException
        MsgBox(ex.Message)
    End Try
End Sub

If you ever want to use a filter, you might do this (notice the overloading):

Private Function GetMachines(ByVal machineFilter As String) As DataTable
    Dim SQL As String = "SELECT MachineName from machine WHERE MachineName LIKE @Filter"

    Dim result As New DataTable

    Using cn As New MySqlConnection(ConnectionString), _
          cmd As New MySqlCommand(SQL, cn),
          da As New MySqlDataAdapter(cmd)

        'Match the MySqlDbType to your actual database column type and length
        cmd.Parameters.Add("@Filter", MySqlDbType.VarString, 30).Value = machineFilter

       da.Fill(result)
   End Using
   Return result
End Function

Private Function GetMachines(ByVal machineFilter As String) As DataTable
    Return GetMachines("%")
End Function

Query parameters like that are very important, and if you were doing string concatenation to accomplish this kind of thing on your old platform, you were doing very bad things there, too.

Finally, let's get fancy. A lot of the time, you really don't want to load an entire result set into RAM, as is done with a DataTable. That can be bad. Instead, you'd like be able to stream results into memory and only work with one at a time, minimizing RAM use. In these cases, you get to play with a DataReader... but returning a DataReader object from within a Using block (which is important) doesn't work that well. To get around this, we can use functional programming concepts and advanced language features:

Private Iterator Function GetMachines(ByVal machineFilter As String) As IEnumerable(Of String)
    Dim SQL As String = "SELECT MachineName from machine WHERE MachineName LIKE @Filter"

    Using cn As New MySqlConnection(ConnectionString), _
          cmd As New MySqlCommand(SQL, cn)

        'Match the MySqlDbType to your actual database column type and length
        cmd.Parameters.Add("@Filter", MySqlDbType.VarString, 30).Value = machineFilter
        cn.Open()
        Using rdr As MySqlDatareader = cmd.ExecuteReader
            While rdr.Read()
                Dim result As String = rdr("MachineName")
                Yield Return result
            End While
        End Using
   End Using
   Return result
End Function

Private Function GetMachines() As IEnumerable(Of String)
    Return GetMachines("%")
End Function

Thank you, Joel, this is super helpful for understanding how things should be done. Again, thank you. I'll look this over now. — NaN, Jul 22 '19 at 19:03
Joel, before I leave for the day, I wanted to let you know that I tried your databinding snippet. If I'm going to learn this language, then I might as well learn best practices, as you're pointing out. As far as the try/catch blocks, I know better than to use them in a model. Thank you for pointing out my error. I;m not sure why, but your databinding snippet puts this in the combobox in place of the value I'm expecting: System.Data.DataViewManagerListItemTypeDescriptor. I also get this same exact behavior with djv's code above. — NaN, Jul 22 '19 at 19:15
You may need a "ToString()" call in there, or similar. Try my last example, which handles the Object => String conversion in the data method. — Joel Coehoorn, Jul 22 '19 at 19:17
Wow, Joel, thank you so much for taking so much time to explain this! You went over and above, thank you! I tried your last edit but was unable to get it to work. I did however get a lot of information out of each of your examples. I know the right way to code something like this in a language that I'm familiar with, but with VB.NET, I'm struggling to crawl. That's to say, thank you for explaining things! — NaN, Jul 23 '19 at 11:25

score 0 · Accepted Answer · answered Jul 22 '19 at 22:49

I proceeded much like you did. I used the Load method of the DataTable. It is not necessary to set the column name and type. The name of the column is taken from the Select statement and the datatype is inferred by ADO.net from the first few records.

Luckily a DataTable can be an Enumerable using the .AsEnumnerable method. Then we can use Linq to get all the values from the MachineName column. Calling .ToArray causes the Linq to execute. If you hold your cursor over names on this line you will see that the datatype is String(). Just what we need to fill a combo box.

Code for a class called DataAccess

Private ConnectionString As String = "Your Connection String"

Public Function GetMachineNames() As String()
    Dim tb As New DataTable
    Dim SQL As String = "SELECT MachineName from machine;"
    Using cn As New MySqlConnection(ConnectionString)
        Using cmd As New MySqlCommand(SQL, cn)
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    Dim names = dt.AsEnumerable().Select(Function(x) x.Field(Of String)("MachineName")).ToArray()
    Return names
End Function

In the form load you combo box like this.

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim DatAcc As New DataAccess() 
    Dim arr = DatAcc.GetMachineNames()
    machineName.DataSource = arr
End Sub

At the end of the day, this is what worked for me, thank you, Mary! What I was attempting to do was to make a single DataAccess, or DBAL that I can reuse in other projects. I'd like to have a skeleton class that I can just drop into other projects, with the connection string being the only thing that needs to change. Is this possible, or am I going to have to write a connection for each process that I code? — NaN, Jul 23 '19 at 11:14

VB.NET How to correctly loop through a result set

3 Answers3