0

I am retrieving data from SQL Server using code that has worked in other projects. When getting to the ExecuteReader() section, it all works fine.... BUT if you step over into the using block, the reader goes from having data in the returned section to saying

enumeration yielded no results

This only needs to return 1 row.

SO when I step past this line:

using (SqlDataReader reader = cmd.ExecuteReader())

The object is populated data from the query. The values match that in the table.

When I go one more step I hover over reader in the above line and it says

enumeration yielded no results

I cannot see any issues with my code and I'm stuck.

To make matters more confusing if I search for a ordinal position by name it works. Meaning the metadata of the column names seems to be available but not the data.

All I want to do is retrieve a row of data from a SQL Server table and populate an object class defined in my app.

I have tried returning a simple select all, removing the parameters etc and it seems to not have any effect.

    public ConnectionDetails GetCustomerConnectionDetails(string Customer_GUID)
    {
        ConnectionDetails _conDetails = new ConnectionDetails();

        try
        {
            using (SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["someconstring"].ToString()))
            using (SqlCommand cmd = new SqlCommand(@"select Customer_ID ,Customer_Connection_Alias ,Customer_GUID from dbo.Connections where Customer_GUID = @Customer_GUID", sqlConn))
            {
                //[Customer_ID] ,[Customer_Connection_Alias] ,[Customer_GUID]
                cmd.Parameters.Add("@Customer_GUID", SQL.SqlDbType.NVarChar).Value = Customer_GUID;

                sqlConn.Open();

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        _conDetails.Customer_ID = reader.SqlStringValueNullCheck(reader.GetOrdinal("Customer_ID"));
                        _conDetails.Customer_Alias = reader.SqlStringValueNullCheck(reader.GetOrdinal("Customer_Connection_Alias"));
                        _conDetails.Customer_GUID = reader.SqlStringValueNullCheck(reader.GetOrdinal("Customer_GUID"));
                        _conDetails.Connection_Status = reader.SqlStringValueNullCheck(reader.GetOrdinal("Connection_Status"));
                        _conDetails.Connection_Date_Time = reader.SqlDateTimeValueNullCheck(reader.GetOrdinal("Connection_Date_Time"));
                        _conDetails.Connection_GUID = reader.SqlStringValueNullCheck(reader.GetOrdinal("Connection_GUID"));
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return _conDetails;
    }

Any help would be appreciate. Cheers!

9
  • Run SqlProfiler from SSMS what do you see in the query? Commented Aug 14, 2019 at 1:23
  • "Meaning the metadata of the column names seems to be available but not the data." - I believe this is normal when you don't receive any rows back. It seems likely that your query results in no results. Commented Aug 14, 2019 at 1:23
  • 3
    reader.Read() reads the next row of data in memory. And you can access the data of that row only by using various GetMethods such as GetString, GetInt32 etc. Have look here Commented Aug 14, 2019 at 1:42
  • Did you try and use the SqlDataAdapter instead ? This behavior is usually witnessed with SqlDataReader. Check this for better understanding :- stackoverflow.com/questions/4940350/… Commented Aug 14, 2019 at 1:47
  • 2
    use while(reader.read()) not if() Commented Aug 14, 2019 at 3:59

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.