SQL Reader saying no values exist, query seems fine

Question

I'm developing an ASP.NET application with VB, and using SQL Command and Connection in VB to grab the data for the page. I have both portions initialized as such:

travelQuery.CommandText = "SELECT [StartLoc], [EndLoc],[TravelTime], [AvgSpeed], [Distance]  FROM [TravelTimes] WHERE [TripNum] = '" + lblTrip.Text + "'"
travelConnection.ConnectionString = "..."

 eventQuery.CommandText = "SELECT [IncdntDate], [Roadway],..."

And so on, like that. I left out the eventQuery, because that one works fine. The travel query and event query also have the same connection string. The program has the event query executes and then if one of the values, RdwyID returned fits within a certain range, travelQuery executes.

I set RdwyID to 187 within the code to force it to pull and post the travel times, and when I try running it it crashes saying that there are no values. Here's the exact code I use. I do things in the exact same way with eventQuery where it works fine. I am making sure to open and close the connection properly. Time is properly declared as an array of objects.

The TripNum I'm checking the query against is a constant value inside the database with a data type of text.

Dim rdwyID As Integer
'rdwyID = events(9) - where the value is pulled from usually'
rdwyID = 187

 If (rdwyID >= 186 And rdwyID <= 225)
     FillWithTime("2", travelReader, time, newCell)


Private Sub FillWithTime(ByVal TripNum As String, ByRef travelReader As SqlDataReader, ByRef TimeData() As Object, ByRef Cell As System.Web.UI.WebControls.TableCell)

    lblTrip.Text = TripNum
    travelReader = travelQuery.ExecuteReader()
    travelReader.Read()
    travelReader.GetValues(TimeData)

    Cell.Text += "From: " + TimeData(0).ToString().Substring(9) + "<br />"
    Cell.Text += "To: " + TimeData(1).ToString().Substring(9) + "<br />"
    Cell.Text += "Travel Time: " + TimeData(2).ToString() + " minutes <br />"
    Cell.Text += "Average Speed: " + TimeData(3).ToString() + " MPH <br />"
    Cell.Text += "Distance: " + TimeData(4).ToString() + " miles <br />"

End Sub

Thanks for any help or suggestions you have.

Edit: Made the changes like you said, and the if statement for execute reader is evaluating to false. I know nothing about working with the reader, what sort of conditions would make it fail? Thanks a bunch.

Edit 2: Checking the Count for matching the condition returned zero, and checking it straight-up returned 15. Thanks so much. Time to figure out why on Earth it's not matching...

Answer 1

I think you could be trying something like this

    travelQuery.CommandText = "SELECT [StartLoc], [EndLoc],[TravelTime], [AvgSpeed], [Distance]  FROM [TravelTimes] WHERE [TripNum] = @trip"
    ' Assuming trip as integer
    travelQuery.CommandParameters.Add(@trip, Convert.ToInt32(lblTrip.Text))

Additionally, the code above will raise error if lblTrip.Text is not a number, so you would be using int.TryParse or something alike.

Aditionally, in the reader, check the read

If (travelReader.Read()) Then Begin
End If

This way you won't be raising error if the reader has problems reading data.

EDIT 1

For testing purposes, Check first this

travelQuery.CommandText = "SELECT COUNT(*) FROM [TravelTimes] WHERE [TripNum] = @trip"

And then this

travelQuery.CommandText = "SELECT COUNT(*) FROM [TravelTimes]"

First will get you the rows that satisfy your condition, The second one, will get you the total row count for the table, that way you can be sure whether the problem is the reader or the data source (db)

Answer 2

If you are using SQL Server: Try using SQL Server Profiler to have a look at the query that actually gets submitted to the server. My gess would be lblTrip.Text is not set yet.