How to return an array from SQL in VB?

Question

I'm trying to run a SQL query from VB that returns an array of all unique values (Btl) for a given day, in time ascending order.

I'm working off of someone else's code and modifying from a version that only returned a single int value each day. (e.g. '1' , but I would like it to modify it to return '1 2 3 4')

I've tried to convert the 'Btl' variable to an array like 'Btl()' but I get the error that "Value of type 'Integer()' cannot be converted to 'Integer'"

        'Get 'wherecl' table 
        Function GetBtl(ByVal tblRS As String, ByVal nday As Integer, ByVal sqlConn As 
     SqlConnection) As Integer
        ' Get the day's sample bottle
        Dim wherecl As String
        wherecl = "WHERE (DATEDIFF(d, { fn NOW() }, TmStamp) = " & nday & ")"
        Dim Q4 As String


        ' SQL cmd to get array of unique bottles each day 
        Q4 = "SELECT distinct BottleToSample FROM " & tblRS & " " &
    wherecl &
    " ORDER BY TmStamp ASC;"
        'End If

        Dim MCGQ4 As New SqlCommand(Q4, sqlConn)
        Dim Btl As Integer = MCGQ4.ExecuteScalar    'This is the bottle number
        Return Btl

    End Function

This version compiles, but doesn't display any results.

I would like it to deliver "1 2 3 4" for example

ExecuteScalar only returns one value, you'll have to use ExecuteReader — the_lotus, May 24 '19 at 16:54
@Mr.Tripodi Why is it overkill? So you can do `while DataReader.Read()` ??? — Ryan Wilson, May 24 '19 at 16:59
Just use .Load method on DataTable. Actually, id probably just create the array from Reader and not use DT — Mr. Tripodi, May 24 '19 at 17:00

Andrew Morton · Answer 1 · 2020-03-12T09:03:34.873

As an unknown (presumably) number of bottles will be read, it is a good idea to put the data into a List(Of Integer) - a List will expand its capacity automatically when items are added to it. If you actually need the data in an array, that can easily be done.

When using a connection to a database, that connection should be opened, the data read, and then the connection disposed of immediately. SQL Server Connection Pooling makes that efficient. In the case of the code in the question, I achieved that by using the connection string from the connection. The Using Statement takes care of freeing up unmanaged resources even if something goes wrong.

Function GetBtl(ByVal tblRS As String, ByVal nday As Integer, ByVal sqlConn As SqlConnection) As Integer()
    Dim sampleBottles As New List(Of Integer)

    ' Get the day's sample bottles
    Dim wherecl As String = "WHERE (DATEDIFF(d, { fn NOW() }, TmStamp) = " & nday & ")"
    ' SQL cmd to get array of unique bottles each day 
    Dim Q4 As String = "SELECT distinct BottleToSample FROM " & tblRS & " " & wherecl & " ORDER BY TmStamp ASC;"

    Using conn As New SqlConnection(sqlConn.ConnectionString),
          MCGQ4 As New SqlCommand(Q4, conn)

        conn.Open()
        Dim rdr = MCGQ4.ExecuteReader()

        While rdr.Read()
            sampleBottles.Add(rdr.GetInt32(0))
        End While

    End Using

    Return sampleBottles.ToArray()

End Function

Also, I notice that there is a line Dim Btl As Integer = MCGQ4.ExecuteScalar in the code in the question, which indicates that Option Strict is set to "Off". That is a sub-optimal condition; it would be a good idea to use Option Strict On. It could take some effort to correct all the code with that, but it will end up removing variable type problems.

How to return an array from SQL in VB?

1 Answers1