SQL Reader 说不存在任何值，查询似乎没问题 [英] SQL Reader saying no values exist, query seems fine

问题描述

我正在用 VB 开发一个 ASP.NET 应用程序，并在 VB 中使用 SQL 命令和连接来获取页面的数据.我将两个部分都初始化为:

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],..."

以此类推.我遗漏了 eventQuery，因为它工作正常.旅行查询和事件查询也具有相同的连接字符串.程序执行事件查询，然后如果返回的 RdwyID 值之一符合特定范围，则执行 travelQuery.

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.

我在代码中将 RdwyID 设置为 187 以强制它拉取并发布旅行时间，当我尝试运行它时，它崩溃说没有值.这是我使用的确切代码.我使用 eventQuery 以完全相同的方式做事，它可以正常工作.我确保正确打开和关闭连接.时间被正确地声明为一个对象数组.

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.

我正在检查查询的 TripNum 是数据库中的常量值，数据类型为文本.

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.

按照您说的进行更改，执行读取器的 if 语句评估为 false.我对与读者合作一无所知，什么样的条件会使其失败?非常感谢.

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.

编辑 2:检查匹配条件的计数返回零，直接检查返回 15.非常感谢.是时候弄清楚为什么它在地球上不匹配了...

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...

推荐答案

我想你可能正在尝试这样的事情

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))

此外，如果 lblTrip.Text 不是数字，上面的代码将引发错误，因此您将使用 int.TryParse 或类似的东西.

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.

编辑 1

为了测试目的，首先检查这个

For testing purposes, Check first this

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

然后这个

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

第一个会给你满足条件的行，第二个会给你表的总行数，这样你就可以确定问题是读者还是数据源(db)

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)

这篇关于SQL Reader 说不存在任何值，查询似乎没问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！