Trouble with a datareader in visual basic and SQL

Question

Hello, i am working on a search feature for a program i am working on and i found a tutorial online that provides some insight but the actual code, after being modified to fit my aplication, does not work. i get two different errors as of right now, one that tells me "the value of type 'System.data.sqlclient.sqldatareader' cannot be converted to '1-dimensional array of system.data.sqlclient.sqldatareader" and the other that says"argument not specified for parameter 'array' of 'Public shared function... anyway i am kinda new to this and here is what i have so far. any advice?

Private Sub SearchOKButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchOKButton.Click

Dim TrNum
    Dim dr As SqlDataReader()

    TrNum = Me.SearchText.Text()

   Using connection As New SqlClient.SqlConnection("Data Source=XXXXX;Initial Catalog=YYYYY;Integrated Security=True;Pooling=False;Encrypt=False"), _
    cmd As New SqlClient.SqlCommand("SELECT [YYYYY] FROM (TrackingNumber) WHERE TrackingNumber = 'TrNum'", connection)

        connection.Open()
        dr = cmd.ExecuteReader()
        While dr.AsReadOnly()
            MsgBox("TrackingNumber" + "Date")
        End While
        connection.Close()

    End Using

End Sub

Answer 1

I am no VB expert but I do alot of these type of connections inside SCOM. My WHILE loop looks like this:

while dr.eof <> true and dr.bof <> true
    MsgBox(dr("TrackingNumber") + dr("Date"))
    dr.movenext
wend
connection.close

How many rows is your SQL statement returning?

Answer 2

Your first error message tells you what you need to know: that you're trying to create an array of DataReaders when you shouldn't be:

Dim dr As SqlDataReader()

Change it to this:

Dim dr As SqlDataReader

Answer 3

There are multiple problems here...

Edited:

I assume that TrackingNumber is the table you are querying, and that table contains columns TrackingNumber and Date.

Dim TrNum as String = Me.SearchText.Text ' <== Text is a property, not a function

Dim connectionString as string = "Data Source=XXXXX;Initial Catalog=YYYYY;Integrated Security=True;Pooling=False;Encrypt=False"

Dim cmdText as string = "SELECT TrackingNumber, [date] " & _
                        "FROM TrackingNumber " & _
                        "WHERE TrackingNumber = @trackingNumber"

Using connection As New SqlClient.SqlConnection(connectionString)

    Dim cmd As New SqlClient.SqlCommand(cmdText, connection)

    ' assign TrNum to @trackingNumber
    cmd.Parameters.AddWithValue("@trackingNumber", TrNum)    
    connection.Open()
    dim dr as SqlDataReader = cmd.ExecuteReader()

    While dr.Read()       ' <== this is probably what you want
        MsgBox(string.Format("{0} {1}", dr("TrackingNumber"), dr("Date"))
    End While

End Using

The point of the Using syntax is to automatically close the connection when the variable goes out of scope.

You should probably keep your connection string in the web.config.