locked
MSSQL and ADO.NET | Exception: “Invalid attempt to read when no data is present." RRS feed

  • Question

  • User-166723528 posted

    When trying to read the object coming back from SQL, I am given the exception "{ "Invalid attempt to read when no data is present."

    C# code:

    using (SqlConnection con = new SqlConnection(connection))
    {
    SqlCommand cmd = new SqlCommand("PROC", con);
    cmd.CommandType = CommandType.StoredProcedure;
    
    SqlParameter ParamName1 = new SqlParameter("@Name1", "XYZ123");
    SqlParameter ParamName2 = new SqlParameter("@Name2", "1234");
    
    cmd.Parameters.Add(ParamName1);
    cmd.Parameters.Add(ParamName2);
    
    con.Open();
    
    try
    {
        SqlDataReader rdr = cmd.ExecuteReader();
    
        while (rdr.Read())
        {
            string result = rdr["Item"].ToString();
        }
    
        rdr.Close();
    }
    
    catch (Exception ex)
    {
        throw ex;
    }
    
    }

    SQL dynamic query (EXEC and sp_executesql)

     ALTER PROC [dbo].[PROC]
    @Name1 varchar(128), @Name2 varchar(128) AS BEGIN --DECLARE @Name1 varchar(128) = 'XYZ123', --@Name2 varchar(128) = '1234' --DECLARE @SelectFromTable varchar(MAX) SET @SelectFromTable = 'SELECT [Item] FROM [dbo].[' + @Name1 + '] WHERE [ID] = ' + @Name2 + ' ' EXEC (@SelectFromTable) --PRINT (@SelectFromTable) END
    ALTER PROC [dbo].[PROC]
    (
        @Name1 varchar(128), --for an object I would prefer to use sysname instead
        @Name2 varchar(128)
    ) AS
    BEGIN                           
        DECLARE @SelectFromTable nvarchar(MAX)
    
        SET @SelectFromTable = N'SELECT Item
                                FROM dbo.' + QUOTENAME(@Name1) + '
                                WHERE ID = @Name2'
    
        exec sp_executesql @SelectFromTable, N'@Name2 varchar(128)', @Name2 = @Name2
        --PRINT (@SelectFromTable)
    END

    WHAT I HAVE TRIED

    I have tested the dynamic SQL query in SQL Server by using the PRINT function to see that I'm getting the right data back from the executed statement, and indeed I am. I have looked over my C# code but cannot find the fault there.

    NOTE: Problem seems to be with the SqlDataReader (rdr). Query works with both EXEC and sp_executesql

    Thursday, August 23, 2018 5:25 PM

Answers

  • User-166723528 posted

    The method that advances through the rows is the reader.Read() method, and any time it evaluates to 'true', it will advance to the next record and read what is in it. I truly cannot remember what actually caused this issue in the first place, since I remember using the .Read() method, I suspect it must have been a problem with my SQL query.

    I just checked my SQL query now and there is no difference between the sp_executesql version and the one I have now of it. However, with the normal exec version, I used a temp table instead to retrieve the values with this statement:

    SELECT Item FROM #temp WHERE ID = @Name2

    This is as much as I can gauge.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 30, 2018 5:25 PM

All replies

  • User753101303 posted

    Hi,

    It's been a while but what if you try to use SET NOCOUNT ON at the top of your SP ?

    Thursday, August 23, 2018 5:38 PM
  • User-166723528 posted

    Hi Patrice,

    Thanks for your reply and suggestion. I did put SET NOCOUNT ON Right after the BEGIN, but it did not help.

    Thursday, August 23, 2018 5:39 PM
  • User-166723528 posted

    DA924

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader

    hasrows…...

    I was able to solve the issue through a combination of checking my ADO.NET code and going over my stored procedure, but cannot remember exactly what the solution was.  Doing a Hasrows check was not the answer, and doesn't retrieve data whereas the reader does.

    Thursday, August 30, 2018 11:56 AM
  • User1120430333 posted

    The Hasrows() is a method that only lets one know that the datareader has rows in it., which is looking at a row count > 0. I suspect.  It is not the datareader that is actually reading rows in a forward only motion.   

    Thursday, August 30, 2018 4:27 PM
  • User-166723528 posted

    The method that advances through the rows is the reader.Read() method, and any time it evaluates to 'true', it will advance to the next record and read what is in it. I truly cannot remember what actually caused this issue in the first place, since I remember using the .Read() method, I suspect it must have been a problem with my SQL query.

    I just checked my SQL query now and there is no difference between the sp_executesql version and the one I have now of it. However, with the normal exec version, I used a temp table instead to retrieve the values with this statement:

    SELECT Item FROM #temp WHERE ID = @Name2

    This is as much as I can gauge.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 30, 2018 5:25 PM