Databases
StackOverflow2013
Postsdbo
POSqlDataReader.HasRows returns false since SQL 2008 upgrade
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSqlDataReader.HasRows returns false since SQL 2008 upgrade
    primarykey
    data
    text
    <p>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. </p> <p>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. </p> <pre><code> _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()) {... </code></pre> <p>Does anybody have any idea what's going on? There are similar code blocks on other pages where HasRows returns the correct value.</p> <p>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??</p> <p>EDIT2- Here's the SP</p> <pre><code>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 </code></pre> <p>Many thanks in advance.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndy Lester
    1. USballpointpe0n
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSqlDataReader.HasRows returns false since SQL 2008 upgrade
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSqlDataReader.HasRows returns false since SQL 2008 upgrade
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSqlDataReader.HasRows returns false since SQL 2008 upgrade
      1. This table or related slice is empty.
      1. VTUpMod
    1. COGet out Profiler and see what's happening behind the scenes. Could be an issue with the migration. Profiler will show you the queries as they are sent so you can test them to make sure they're working properly.
      singulars
      1. POSqlDataReader.HasRows returns false since SQL 2008 upgrade
      1. USWill
    2. COIs there a print statement inside this stored procedure? (guessing)
      singulars
      1. POSqlDataReader.HasRows returns false since SQL 2008 upgrade
      1. USshahkalpesh
    3. COnope, it's just a simple select with one join
      singulars
      1. POSqlDataReader.HasRows returns false since SQL 2008 upgrade
      1. USballpointpe0n
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload