Question

请看看下面的图案:

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 )

以及以下代码:

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

此代码是实时系统函数的一部分。当我在开发模式( 或活过) 中使用 ORDER BY 运行全部应用程序时; 数据编辑器没有记录, 显示一个信息框显示没有行( 我单独运行上述代码时不会出现这种情况 ) 。在对 ORDER 条款进行评论后, 返回正确的行数。没有例外被丢弃。是否有方法看 SQLDataReader 是否出错?

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?

Answer 1

为了进一步调查,我建议使用 SQL 剖析器。您不仅能够从应用程序中看到准确的查询, 而且除了 SQL 查询计划之外, 您还可以返回可能产生的任何错误( 但没有返回 ) 。

Answer 2

我怀疑 ORDER by 省略了对密钥的行投影导致失败的任何行。例如,您在该命令中使用了许多字符串操作,包括子字符串操作。如果子字符串参数不在字符串范围之内,会发生什么情况?

我建议你试试:

SELECT [insert order by projection here] FROM Person

并查看“它们”那里会发生什么情况 <它们> - 从方程式中删除 ORDER 。我还建议您从 SQL 管理工作室( 或其它什么) 做这个, 而不是通过代码进行实验 :

友情链接