How to add database query results to an array

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.