SQLDataReader - no results

Question

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?

What happens if you just use order by [index], or order by some other property? — Jon Skeet
– Jon Skeet, Commented May 22, 2012 at 19:47
@Jon Skeet, thanks. It returns two rows in correct order (no problem) — w0051977
– w0051977, Commented May 22, 2012 at 19:51
@w0051977: Okay - so I suspect a row is "removed" if computing its ordering "key" goes wrong, basically - e.g. because you're taking a substring outside the bounds. — Jon Skeet
– Jon Skeet, Commented May 22, 2012 at 19:55
@Jon Skeet, that was my first thought, but this does not seem to be the case. Is there a property on the SQLDataReader that would tell me if this has happened? — w0051977
– w0051977, Commented May 22, 2012 at 19:59

Jon Skeet · Accepted Answer · 2012-05-22 19:56:58Z

2

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

answered May 22, 2012 at 19:56

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jon Skeet Over a year ago

@w0051977: The original with ORDER BY, or the "just select the ordering key" version?

w0051977 Over a year ago

Can you suggest a way to investigate this further?

ulty4life Over a year ago

Try creating a stored procedure, and calling the stored procedure instead of just raw SQL. That should help synchronize differences between querying from the application and from within management studio.

w0051977 Over a year ago

@Jon Skeet, I have updated my question (update 2). Could you take a look please? The query results are different depending on whether I use parameterised queries or none parameterised queries.

Dave Simione · Accepted Answer · 2012-05-23 18:43:54Z

1

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.

answered May 23, 2012 at 18:43

Dave Simione

1,4412 gold badges22 silver badges32 bronze badges

3 Comments

w0051977 Over a year ago

Thanks. Do you know if there is a reason a query can be optimised in a different way depending on whether a parameterised query is used or an none parameterised query?

Dave Simione Over a year ago

There are many different reasons why queries are optimized in different ways - getting the query plans for both will help in the investigation.

w0051977 Over a year ago

Thanks. I have marked this as the correct answer because of the recommendation to using SQL Profiler. Using SQL Profiler I was able to see that the query was being optimised differently for parameterised queries and none parameterised queries. Using parameterised queries; the execution plan showed that the ORDER BY was running before the join and hence the failure.

Collectives™ on Stack Overflow

SQLDataReader - no results

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related