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
If Not cnn2.State = ConnectionState.Open Then
cmd5.Connection = cnn2
cmd5.CommandText = "Select motonum from moto"
myData5 = cmd5.ExecuteReader
'code to return results here
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.