CODEWITHSUNDEEP
asp.net.netsqlsql-server
w0051977
w0051977

Reputation: 15787

SQLDataReader - no results

Please have a look at the schema below:

CREATE TABLE Person (id int not null identity,[index] varchar(30),datecreated datetime)

insert into Person ([index],datecreated) values ('4,5,6','2011-01-01')
insert into Person ([index],datecreated) values ('1,2,3','2011-02-02')
insert into Person ([index],datecreated) values ('7,8','2012-02-02')

and the code below:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            Dim _ConString As String = WebConfigurationManager.ConnectionStrings("dbConnection").ConnectionString
            Dim connection As New SqlConnection(_ConString)
            Dim objCommand As New SqlCommand
            Dim objDR As SqlDataReader
            Dim sqlString As String

            sqlString = "SELECT * FROM Person WHERE datecreated < '2012-01-01' "


            objCommand.CommandText = sqlString & " ORDER BY left (substring([index],charindex(',',[index])+1,200),  " & _
            " charindex(',',substring([index],charindex(',',[index])+1,200))-1)"
            objCommand.Connection = connection
            connection.Open()
            objDR = objCommand.ExecuteReader
            If objDR.HasRows Then
                MsgBox("Has Rows")
            Else
                MsgBox("No Rows")
            End If
            connection.Close()
        Catch ex As Exception

        End Try

    End Sub

This code is part of a function in the live system. Whenever I run the full application in development mode (or live) with the ORDER BY; the DataReader has no records and a message box appears saying No Rows (this does not occur when I run the above code in isolation). The correct number of rows are returned after the ORDER BY clause is commented out. There is no exception thrown. Is there a way to see if the SQLDataReader has produced an error?

UPDATE Please don't post answers about memory leaks e.g. connection not closed etc or the fact that exceptions are not handled. I realise this. I produced the code above to attempt to recreate the problem.

UPDATE2 23/05/2012 19:30 gmt I have done some further testing and it apears that the discrepancy occurs when parametised queries are used i.e. a row will return in SQL Studio Manager but not in the application after the command object is executed. I know that parameterised queries are cached. Is there a reason why the parameterised execution plan could be different to the none parameterised execution plan?

Upvotes: 1

Views: 846

Answers (2)

Dave Simione
Dave Simione

Reputation: 1441

To investigate further, I would recommend using SQL Profiler. Not only will you be able to see the exact query coming from the application, but you can return any errors that may be generated (but not returned), in addition to the SQL Query plan.

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1500055

I suspect that ORDER BY omits any row where the projection of the row to the key causes a failure. For example, you're using many string operations in that ordering, including substring. What happens if the substring arguments are outside the range of the string?

I suggest you try:

SELECT [insert order by projection here] FROM Person

and see what happens there - to remove ORDER BY from the equation. I'd also suggest you do this from SQL Management Studio (or whatever) rather than experimenting via code :)

Upvotes: 2

Related Questions