none
Having problems with SqlDataReader.Read() RRS feed

  • Question

  • Hi guys, I'm having problems with SqlDataReader.Read(), my codes are as below. I've tested the sqlQuery and it returns 1 row, the conn variable is ok, but objReader.HasRows and objReader.Read() always return false. Any idea how do I read the record from objReader?

    SqlCommand cmd = new SqlCommand(sqlQuery, conn);

    SqlDataReader objReader = cmd.ExecuteReader();

    while (objReader.Read())

    {}

    Friday, October 5, 2012 2:47 AM

Answers

  • I would recommend posting your SQL statement. If no rows are returned and there are no exceptions (errors) then no data that satisfies the query criteria is being found.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by dymc Tuesday, October 9, 2012 12:31 PM
    Friday, October 5, 2012 12:02 PM
  • I agree with Paul's assessment.  Also, make sure you are also connecting the correct server and database.  For troubleshooting, you can also run a Profiler trace against the development server to capture the SQL:BatchCompleted statement event, which will include the actual SQL statement sent to SQL Server. 

    Not directly related to your problem but is this a static SQL statement?  I don't see any parameters in your code snippet.  If your query has a WHERE clause with variable values, it is usually best to pass those as command parameters instead of using string concatentation to build the query.  This is more secure while improving performance and maintainability.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by dymc Tuesday, October 9, 2012 12:29 PM
    Friday, October 5, 2012 12:26 PM

All replies

  • I would recommend posting your SQL statement. If no rows are returned and there are no exceptions (errors) then no data that satisfies the query criteria is being found.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by dymc Tuesday, October 9, 2012 12:31 PM
    Friday, October 5, 2012 12:02 PM
  • I agree with Paul's assessment.  Also, make sure you are also connecting the correct server and database.  For troubleshooting, you can also run a Profiler trace against the development server to capture the SQL:BatchCompleted statement event, which will include the actual SQL statement sent to SQL Server. 

    Not directly related to your problem but is this a static SQL statement?  I don't see any parameters in your code snippet.  If your query has a WHERE clause with variable values, it is usually best to pass those as command parameters instead of using string concatentation to build the query.  This is more secure while improving performance and maintainability.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by dymc Tuesday, October 9, 2012 12:29 PM
    Friday, October 5, 2012 12:26 PM
  • Thanks guys! sorry for the late reply. I managed to solve the problem by myself before you posted. The problem was in the settings of SQL which did not return any data on the queries.
    Tuesday, October 9, 2012 12:31 PM