SqlDataReader.HasRows返回自SQL 2008的升级假 [英] SqlDataReader.HasRows returns false since SQL 2008 upgrade

问题描述

我有一个ASP.NET 2.0的网站连接到SQL数据库。我已经升级，从2000 SQL服务器2008年，从那时起，一页拒绝工作。

I've got an ASP.NET 2.0 website that connects to a SQL database. I've upgraded the SQL server from 2000 to 2008 and since then, one page refuses to work.

我已经计算出的问题是，调用SqlDataReader.HasRows是返回false，即使数据集不是空的，取消勾选允许遍历reader.Read（）来访问预期的数据。

I've worked out the problem is that the call to SqlDataReader.HasRows is returning false even though the dataset is not empty and removing the check allows the loop through reader.Read() to access the expected data.

    _connectionString = WebConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString;
    SqlConnection connection = new SqlConnection(_connectionString);
    SqlCommand command = new SqlCommand(searchtype, connection);
    SqlParameter _parSeachTerm = new SqlParameter("@searchterm", SqlDbType.VarChar, 255);
    _parSeachTerm.Value = searchterm;
    command.Parameters.Add(_parSeachTerm);
    command.CommandType = CommandType.StoredProcedure;
    try
    {
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        if (reader.HasRows) //this always returns false!?
        {
            while (reader.Read())
            {...

没有任何人有任何想法是怎么回事？有在哪里HasRows返回正确的值的其他页面类似code块。

Does anybody have any idea what's going on? There are similar code blocks on other pages where HasRows returns the correct value.

编辑 - 只是为了澄清，存储过程不会返回我已经确认的结果，因为循环经过精细跑，如果我删除HasRows检查。更改连接字符串中的SQL服务器的只是名字在SQL 2000上运行相同的数据库，使问题消失。我检查了NOCOUNT是关闭的，这样可以让还有什么HasRows返回false时并非如此??

EDIT- Just to clarify, the stored procedure DOES return results which I have confirmed because the loop runs through fine if I remove the HasRows check. Changing just the name of the SQL server in the connection string to an identical database running on SQL 2000 makes the problem go away. I've checked that NOCOUNT is off, so what else could make HasRows return false when that's not the case??

EDIT2-这里的SP

EDIT2- Here's the SP

CREATE PROCEDURE StaffEnquirySurnameSearch

@searchterm varchar(255)

AS

SELECT  AD.Name, AD.Company, AD.telephoneNumber, AD.manager, CVS.Position, CVS.CompanyArea, CVS.Location, CVS.Title, AD.guid AS guid,
AD.firstname, AD.surname
FROM ADCVS AD
LEFT OUTER JOIN CVS ON
AD.Guid=CVS.Guid 
WHERE AD.SurName LIKE @searchterm
ORDER BY AD.Surname, AD.Firstname
GO

提前非常感谢。

推荐答案

HasRows 需要滚动游标。

你都带回行包含任何大的图像/ BLOB 数据？

Do the rows you are bringing back contain any large image/BLOB data?

由于别人的建议，我想张贴存储过程可能会引发对此事的一些轻......

As someone else suggested, I think posting the Stored Procedure might throw some light on the matter...

这篇关于SqlDataReader.HasRows返回自SQL 2008的升级假的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！