ken ken -4 years ago 127
Vb.net Question

How to add database query results to an array

I am trying to select string values from an Access database and then place them into an array of strings so that I can perform a loop statement on the array.

However I don't know how to place the result of the query into an array. I know how to query the database but all I need is how to put the result in an array.

My select statement is

Select motonum from moto
. I want to put
motonum
in an array.

The whole code to read the data is:

connect2()
If Not cnn2.State = ConnectionState.Open Then
'open connection
cnn2.Open()
'MessageBox.Show("chk2")
End If
cmd5.Connection = cnn2
cmd5.CommandText = "Select motonum from moto"
myData5 = cmd5.ExecuteReader
While myData5.Read
'code to return results here
End While`

Answer Source

There are any number of different ways to approach this, depending on the actual needs of your project. First and foremost, I would ask if you actually require a string array as the return type. For most cases, an array is less useful that a List(Of String) or other types which implement IEnumerable.

Here are two options, both of which involve a List(Of String). However, one returns the List to the caller, which can then choose to employ the many useful methods of the List type in working with the data:

THIS is the way I would recommend:

Public Function getListOfMotonum() As List(Of String)
    Dim SQL As String = "SELECT motonum FROM moto"

    Dim output As New List(Of String)()

    ' Set the connection string in the Solutions Explorer/Properties/Settings object (double-click)
    Using cn = New SqlConnection(Properties.Settings.[Default].MyConnectionString)
        Using cmd = New SqlCommand(SQL, cn)
            cn.Open()

            Try
                Dim dr = cmd.ExecuteReader()
                While dr.Read()
                    output.Add(dr("motonum").ToString())
                End While
            Catch e As SqlException
                ' Do some logging or something. 
                MessageBox.Show("There was an error accessing your data. DETAIL: " & e.ToString())
            End Try
        End Using
    End Using

    Return output

End Function

Here is a trivial example of code which consumes the output of this function:

Private Sub PrintListToConsole()
    Dim MyMotonumList = Me.getListOfMotonum()

    For Each item As String In MyMotonumList
        Console.WriteLine(item)
    Next
End Sub

If your project REQUIRES a string array, the approach may vary. You can return a string from the same function with a couple minor modifications:

' Change the return type in the function signature:
Public Function getArrayOfMotonum() As String()
    Dim SQL As String = "SELECT motonum FROM moto"
    Dim output As New List(Of String)()

    '  . . . Same Data Access code as above:

    ' Just use the .ToArray method of the List class HERE:
    Return output.ToArray()

End Function

Or, you can use the same method in your client code, consuming the original function which returns a list:

Private Sub PrintArrayToConsole()
    Dim MyMotonumArray = Me.getArrayOfMotonum()

    For Each item As String In MyMotonumArray 
        Console.WriteLine(item)
    Next
End Sub

Returning the List from your function provides a more flexible return type, with many useful methods.

As a side note, allow me to recommend the Using block when consuming data access resources. This handles the proper tear down and disposal of the Connection and Command objects for you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download