Visual basic sql command not returning anything to variables

Question

Hi, i am working on this code that is going to query a database for an entry and return the value of the tracking number and the time to print on a label if the tracking number exist in the databse. it does not right now it only seems to reprint the number that is enetered in a text box and it does not grab the date. any ideas? here is the code.

    Dim TrNum As String = Me.DupTNText.Text
    Dim TrDate As <=something if it is even needed

    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 YYYYY " & _ 
                        "WHERE TrackingNumber = @TrackingNumber, Date = @Date" 

    Using connection As New SqlClient.SqlConnection(connectionString) 

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

    cmd.Parameters.AddWithValue("@TrackingNumber", TrNum) 
    cmd.Parameters.AddWithValue("@Date", TrDate) 

    connection.Open() 

    'this is where my print code begins and it works fine it just uses TrNum for the tracking number and TrDate for the date.

Here is my edited code with the changes applied, am i getting closer?

    Dim dupText
    Dim trackingNumber As String
    Dim dateSent

    dupText = Me.DupTNText

    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 YYYYY " & _
                            "WHERE TrackingNumber = @TrackingNumber, Date = @Date"

    Using connection As New SqlClient.SqlConnection(connectionString)

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

        cmd.Parameters.AddWithValue("@TrackingNumber", dupText)
        cmd.Parameters("@TrackingNumber").Direction = ParameterDirection.Input

        connection.Open()

        Dim sdr As SqlDataReader = cmd.ExecuteReader()

        If (sdr("TrackingNumber") IsNot Nothing) Then
            trackingNumber = Convert.ToString(sdr("TrackingNumber"))
            dateSent = Convert.ToString(sdr("Date"))
        End If

EDIT #3

Changes Applied, i am pretty sure its the query string now. i get

"The parameterized query '(@TrackingNumber nvarchar(4000),@Date datetime)SELECT TrackingNu' expects the parameter '@TrackingNumber', which was not supplied."

witht the string as it is below. What other part of the code would you need to see to help determine the problem? I feel like i am close and thanks i am learning a bunch from this! sorry also for the change of variable names.

Private Sub DupOKButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DupOKButton.Click

    Dim dupText
    Dim trackingNumber As String
    Dim dateSent

    dupText = Me.DupTNText

    Dim connectionString As String = "Data Source=XXXXX;Initial Catalog=ScannedDB;Integrated Security=True;Pooling=False;Encrypt=False"

    Dim cmdText As String = "SELECT TrackingNumber, Date FROM[ScannedDBTable] WHERE TrackingNumber = @TrackingNumber AND Date = @Date"

    Using connection As New SqlClient.SqlConnection(connectionString)

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

        cmd.Parameters.Add(CreateSqlParameter("@TrackingNumber", DbType.String, ParameterDirection.Input, trackingNumber))
        cmd.Parameters.Add(CreateSqlParameter("@Date", DbType.DateTime, ParameterDirection.Input, dateSent))

        connection.Open()

        Dim sdr As SqlDataReader = cmd.ExecuteReader()

        If (sdr("TrackingNumber") IsNot Nothing) Then
            trackingNumber = Convert.ToString(sdr("TrackingNumber"))
            dateSent = Convert.ToString(sdr("Date"))
        End If

'Printing code...

EDIT #4

Private Sub DupOKButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DupOKButton.Click


    Dim trackingNumber As String
    Dim dateSent As DateTime

    trackingNumber = Me.DupTNText.Text

    Dim connectionString As String = "Data Source=XXXXX;Initial Catalog=ScannedDB;Integrated Security=True;Pooling=False;Encrypt=False"

    Dim cmdText As String = "SELECT TrackingNumber, Date FROM[ScannedDBTable] WHERE TrackingNumber = @TrackingNumber AND Date = @Date"

    Using connection As New SqlClient.SqlConnection(connectionString)



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

        cmd.Parameters.Add(CreateSqlParameter("@TrackingNumber", DbType.String, ParameterDirection.Input, trackingNumber))
        cmd.Parameters.Add(CreateSqlParameter("@Date", DbType.DateTime, ParameterDirection.Input, dateSent))

        connection.Open()

        Dim sdr As SqlDataReader = cmd.ExecuteReader()

        If (sdr("TrackingNumber") IsNot Nothing) Then
            trackingNumber = Convert.ToString(sdr("TrackingNumber"))
            dateSent = Convert.ToString(sdr("Date"))
        End If

EDIT #5

Private Sub DupOKButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DupOKButton.Click


    Dim trackingNumber As String
    Dim dateSent As DateTime

    trackingNumber = Me.DupTNText.Text

    Dim connectionString As String = "Data Source=XXXXX;Initial Catalog=ScannedDB;Integrated Security=True;Pooling=False;Encrypt=False"

    Dim cmdText As String = "SELECT TrackingNumber, [Date] FROM ScannedDBTable WHERE TrackingNumber = @TrackingNumber"

    Using connection As New SqlClient.SqlConnection(connectionString)

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

        cmd.Parameters.Add(CreateSqlParameter("@TrackingNumber", DbType.String, ParameterDirection.Input, trackingNumber))
        'cmd.Parameters.Add(CreateSqlParameter("@Date", DbType.DateTime, ParameterDirection.Input, dateSent))

        connection.Open()

        Dim sdr As SqlDataReader = cmd.ExecuteReader()

        If (sdr("TrackingNumber") IsNot Nothing) Then
            trackingNumber = Convert.ToString(sdr("TrackingNumber"))
            dateSent = Convert.ToString(sdr("Date"))
        Else
        End If

Answer 1

Firstly, this line here:

cmd.Parameters.AddWithValue("@Date", TrDate)

Sets the @Date parameter in the query to the value of TrDate. It does not pull out a value into TrDate.

Secondly I can't see where you actually call cmd.Execute() and retrieve the result set, is that further on in the code?

Answer 2

Dim TrNum As String = Me.DupTNText.Text  
Dim TrDate As <=something if it is even needed  

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 YYYYY " & _   
                    "WHERE TrackingNumber = @TrackingNumber, Date = @Date"   

Using connection As SqlConnection = New SqlClient.SqlConnection(connectionString)   

    Using cmd As SqlCommand = New SqlClient.SqlCommand(cmdText, connection)   

        cmd.Parameters.AddWithValue("@TrackingNumber", TrNum)   
        cmd.Parameters.AddWithValue("@Date", TrDate)   
        cmd.Parameters("@TrackingNumber").Direction = ParameterDirection.Input
        cmd.Parameters("@Date").Direction = ParameterDirection.Input

        connection.Open()   

        Dim sdr As SqlDataReader = cmd.ExecuteReader();

        If (sdr.HasRows) Then _
            While (sdr.Read())
                '' Printing data
                Console.WriteLine(String.Format("Track#: {0}, Date: {1}" _
                    , Convert.ToString(sdr("TrackingNumber")) _
                    , DateTime.Parse(Convert.ToString(sdr("Date")), New CultureInfo("en-Us"))
            End While
    End Using
End Using

Disclaimer: This code hasn't been tested and is provided as-is to simply demonstrate an idea that, let's hope so, will guide you to your solution.

Please see SqlCommand.ExecuteReader Method for further details on the topic.

EDIT #1

how would the sdr feed the tracking number and date to separate variables?

You would simply feed your variables like so:

' Assuming you have already declared your variables here...'
If (sdr("TrackingNumber") IsNot Nothing) Then 
    trackingNumber = Convert.ToString(sdr("TrackingNumber"))
    dateSent = DateTime.Parse(Convert.ToString(sdr("Date")), New CultureInfo("en-CA"))
End If

This is just an example. You don't have to use DateTime.Parse unless you want the full format control over your DateTime regional settings.

EDIT #2

@dretzlaff17 where would that go? (speaking of ParameterDirection enum)

You would put it exactly where you're calling the Parameters.AddWithValue() method. Instead of calling the method like you're actually doing, you might as well do the following:

Dim trackingNumberParameter As SqlParameter = New SqlParameter()
trackingNumberParameter.ParameterName = "@TrackingNumber"
trackingNumberParameter.Direction = ParameterDirection.Input
trackingNumberParameter.Value = TrNum

cmd.Parameters.Add(trackingNumberParameter)

Dim dateParameter As SqlParameter = New SqlParameter()
...

cmd.Parameters.Add(dateParameter)

Or even like so, which might better suits your code:

cmd.Parameters("@TrackingNumber").Direction = ParameterDirection.Input

Please see my code sample revision with the ParameterDirection enum above (initial sample).

EDIT #3

"The parameterized query '(@TrackingNumber nvarchar(4000),@Date nvarchar(4000))SELECT (@Tr' expects the parameter '@TrackingNumber', which was not supplied." when the query is "SELECT (@TrackingNumber) FROM[XXXXX] WHERE TrackingNumber = @TrackingNumber"

This seems, at first view, that the data types are not the right. I may be wrong as I don't know your code.

If that is so, you might also provide the DbType of your parameters.

cmd.Parameters("@TrackingNumber").DbType = SqlDbType.String
cmd.Parameters("@Date").DbType = SqlDbType.DateTime ' If the data column is indeed a DateTime data type.

But what I would do to make sure I provided the parameters respectively, is what I have written in EDIT #2, that is, the first section when declaring and creating instances of SqlParameter for both parameters, and specifying their respective data type throught the DbType property. (See SqlParameter Members for more details). Perhaps a helper method could do the job for you, as you might want to do the same with other queries as well.

Public Function CreateSqlParameter(ByVal name As String, ByVal dbType As DbType, ByVal direction As ParameterDirection, ByVal value As Object) As SqlParameter
    Dim parameter As SqlParameter = New SqlParameter()
    parameter.ParameterName = name
    parameter.DbType = dbType
    parameter.Direction = direction
    parameter.Value = value
    Return parameter
End Function

And then creating them within your code:

cmd.Parameters.Add(CreateSqlParameter("@TrackingNumber", DbType.String, ParameterDirection.Input, TrNum))
cmd.Parameters.Add(CreateSqlParameter("@Date", DbType.DateTime, ParameterDirection.Input, TrDate))

Neverhelss, check for the InnerException as it might provide further details on the error. The least I can say is that you have progressed.

EDIT #4

Perhaps this might help: The parameterized query ... expects the parameter ..., which was not supplied.

EDIT #5

"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM." Is it getting confused because i am only typing in a tracking number and asking for a tracking number and a date and time?

Yes, it is somehow getting confused. The defaut value of a non-nullable DateTime data type is:

Dim dateSent As DateTime

dateSent already has a value which is 01, January 0001 00:00:00.000 AM as per MSDN reference about the DateTime Structure. Since the SQL Server DateTime data type covers the period specified in the error message, the @dateSent DateTime variable cannot be earlier than 1/1/1753 12:00:00 AM, it complains about an value out of range.

If you don't feel like the DateTime parameter is required, just remove it from the query, and subtract the code adding this parameter to your cmd variable. Use only what is necessary for you to get the information you need. If this is the tracking number, then only use the @TrackingNumber parameter. So, your SQL DQL might look like :

select TrackingNumber
        , [Date]
    from YYYYY
    where TrackingNumber = @TrackingNumber

Then, there will no longer be the need for the @Date parameter. But then again, only you know whether you need this parameter or not. But since you do not provide any value to it, it seems to me that you don't need it.

Answer 3

Make sure you set the direction property on your parameter object

myParm.Direction = ParameterDirection.Output;

or

myParm.Direction = ParameterDirection.ReturnValue;