none
SQLReader HasRows = true, ResultsView empty, Enumeration yielded no results RRS feed

  • Question

  • I queried a table in VS and got two rows as result.  Copied the select query into string query, then replaced search values with params as below

    string selectQuery = "select * from Table where ColumnX = @Value1 and ColumnY= @Value2";

    SqlCommand Query = new SqlCommand(selectQuery, Connection);

    Query.Parameters.AddWithValue("@Value1", object.GetValue1()); Query.Parameters.AddWithValue("@Value2", object.GetValue2());

    During execution the correct getter values are being used, yet after executing

    SqlDataReader reader = Query.ExecuteReader();

    reader HasRows is true, ViewResults empty. Enumeration yielded no results.  How can a query return rows but no results?  Logically to me, if ViewResult is empty, HasRows should be false.  What could be causing this?

    Wednesday, April 19, 2017 12:46 PM

All replies

  • You need to run your code through the debugger to see what values are being passed to the command. In the following example I hard coded values so that makes things a bit clearer.

    public void Demo()
    {
        using (SqlConnection cn = new SqlConnection(ConnectionString))
        {
    
            string commandText = @"SELECT Identifier,CompanyName,ContactName " + 
                                    "FROM Customers " + 
                                    "WHERE ContactTitle = @ContactTitle AND Country = @Country";
    
            using (SqlCommand cmd = new SqlCommand(commandText, cn))
            {
                cmd.CommandText = commandText;
                cmd.Parameters.AddWithValue("@ContactTitle", "Owner");
                cmd.Parameters.AddWithValue("@Country", "Mexico");
    
                cn.Open();
                SqlDataReader  reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"Id: {reader.GetInt64(0)} Company: {reader.GetString(1)}");
                    }
                }
            }
        }
    }

    Result shown in the IDE Output window

    Id: 2 Company: Ana Trujillo Emparedados y helados
    Id: 3 Company: Antonio Moreno Taquería
    Id: 53 Company: Tortuga Restaurante

    Ran the query in SQL Server Management Studio to verify results


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by CoolDadTx Wednesday, April 19, 2017 2:10 PM
    Wednesday, April 19, 2017 1:53 PM
  • I think its my connection string that is causing the problem.  I hard-coded the select query and still got no results. 
    Wednesday, April 19, 2017 2:32 PM
  • Connection.ServerVersion threw an exception of type System.InvalidOperationException.  Trying to troubleshoot it.
    Wednesday, April 19, 2017 2:35 PM
  • Hi RichardDunneBSc,

    Thank you for posting here.

    According to your question is more related to ADO.NET, I will move it to ADO.NET Managed Providers forum for suitable support.

    The Visual C# discuss and ask the C# programming language, IDE, libraries, samples and tools.

    If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.

    Thanks for your understanding and cooperation.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 20, 2017 7:01 AM
  • There is no point in checking HasRows on a SqlDataReader. In fact, it can sometimes be an erroneous check. Just Read() which will be true if there are rows to fetch, false otherwise.

    while (reader.Read())
    {
      // your code
    }

    Also check your connection string and make sure you can run the query direct.



    william xifaras

    Thursday, April 20, 2017 3:24 PM
  • This is the behaviour I am seeing in VS.  Reading multiple rows from a SQL Server table having a PK.  Using

    if (Reader.HasRows)
    {
    }

    Creating an object for each row if multiple rows returned.  After reading the first row, the 1st object has the correct PK.  After the second read, the first object has the same PK as the second.  After the 3rd read the 1st & 2nd objects have the same PK as the 3rd. And so on.  VS knows there are multiple objects, bu for some reason is only reading 1 of them, and therefore only displaying 1 in the datagridview.  Looks/feels like a bug to me.

    Thursday, April 20, 2017 3:41 PM
  • Probably you create a single object then reuse it, which is not suitable in this case. You should have a ‘new MyData()’ for each row.

    Or show details.

    Thursday, April 20, 2017 3:46 PM
  • from what I can see, creating a column and adding it to the datagridview creates a row[0] by default.  By doing a single read, data can be assigned to row[0].  I have an object created in the class.  The first read fills this object.  Is there a way of determining how many rows are being returned?  That way I could use a loop at the end of the read to create only the necessary number of objects.

    Thursday, April 20, 2017 4:33 PM
  • Hi Richard,

    Could you show a bit more of your code ... it's easier for us to see what you've done and what you are trying to do if we could look at the code you're running. You know the old saying "A picture is worth a thousand words" ... same thing applies here: "The code is worth a thousand words".  ;0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, April 21, 2017 4:55 AM
  • This the code 

    using (SqlConnection Connection = new SqlConnection(connectionString))
                {
                    
                    for (int x = 0; x < Form3.List1.Count; x++)
                    {
                        string selectFrom = "select Column1, Column5 from Table where Value = @Value1 and Value2 = @Value2 and Value3 = @Value3";
                        SqlCommand Query = new SqlCommand(selectFromInsurance, InsuranceConnection);
                        Query.Parameters.AddWithValue("@Value1", Form3.List1[x]);
                        Query.Parameters.AddWithValue("@Value2", className.GetValue2());
                        Query.Parameters.AddWithValue("@Value3", className.GetValue3());
                        Connection.Open();
                        SqlDataReader Reader = Query.ExecuteReader();
                        try
                        {
                            while (Reader.Read())
                            {
                                for (int i = 0; i < List.Count; i++)
                                {
                                    if (Reader["Column1"].ToString() == List[x].Value1())
                                    {
                                        List[x].SetValue3(Reader["Column3"].ToString()));
                                    }
                                }
                            }
                            
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e.Message);
                        }
                        Reader.Close();
                        Connection.Close();
                    } 
                }

    What would cause the Connection State to be open one minute and closed on the next run.  Assuming the server instance is running, shouldn't the Connection State be always open with the correct Connection string?  Without changing the connection string, the connection state went from open to closed between runs.  Reader was null even with the connection state open.  If the query returns in a separate query, I'm only substituting value for @params, the correct values are being fed to the @params, I checked.

    Wednesday, April 26, 2017 10:04 PM
  • Hi Richard,

    >>What would cause the Connection State to be open one minute and closed on the next run<<

    I assume by "the next run", do you mean the next iteration of the for loop? What is the exception you're getting?

    >>Reader was null even with the connection state open<<

    If Reader was null, then perhaps there is something wrong with your query?


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, April 27, 2017 1:45 AM
  • The last code you posted is malformed with unknown variables. Even so if this compiles for you and the connection closes unexpectedly there is something external to the shown code that is causing the problem. 

    With that all I can do is review code and not assist as per above.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, April 27, 2017 1:51 AM
  • Finding SQLReader very inconsistent.  I have several methods reading from various databases and I'm getting data from them, populating class objects using setters, despite the Reader View being Empty: Enumeration yielded no results.

    SQlReader is also displaying behaviour I mentioned previously.  Its reading two lines from a table with a primary key, yet when the read has completed, inspection shows both List objects have the same primary key, which I think means Reader is only able to see one of the objects, displaying it regardless of which object the user views (I reported this as a bug in VS back when View was actually displaying results)

    In one of my methods, hasRows is true and the While(Reader.read()) is executing, but when it tries reading the first selected column, it gives an exception: Invalid attempt to read when no data is present. 

    Monday, May 1, 2017 4:03 PM
  • I've used the reader for SQL-Server and never had issues as described here and would only suspect issues within the database.

    Not only have I had no issues with the SqlClient reader, no issues with OleDb or IDB2 reader.

    Never seen the same key come back in a read while statement either.

    Lastly, try creating a text file in your project, give it an extension of .sql and run your query from there using DECLARE and SET to setup values. Do you get expected results?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, May 1, 2017 6:00 PM
  • Is it possible to read from multiple databases simultaneously?  
    Tuesday, May 2, 2017 8:34 PM
  • Simultaneously, as in both in one query? Yes, if both databases were on the same SQL Server and you didn't specify the database in your connection string (just the server and login credentials). Your queries would have to specify the database, so you could have:

    string query = "SELECT * FROM MyDatabaseOne..MyTableX; SELECT * FROM MyDatabaseTwo..MyTableY";

    But does this have something to do with your original problem?


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, May 2, 2017 8:46 PM
  • Dealing with one issue at a time, what server configuration (if any) could prevent SQLDataReader View from displaying data? I have a method reading from three tables in sequence without a problem and displaying data in a datagridview, despite SQLDataReader View being empty for each SQL read.  I have another method where I am comparing two values, (one from the Reader) that I know are the same, but the comparison is failing.  I'm trying to troubleshoot blind, View is empty.  

    Monday, May 8, 2017 7:12 PM
  • Is SQLDataReader Results View Empty when hasRows is true a bug or a configuration conflict perhaps?  Its hard to troubleshoot when I can't see what a query result is.

    I uninstalled VS 2017 Community and installed 2012 Express for Windows Desktop.  Same issue, Results View Empty when hasRows is true.

    Thursday, May 11, 2017 7:01 PM