ExecuteReader returns no results, when inspected query does

Question

Consider the following code:

        StringBuilder textResults = new StringBuilder();
        using(SqlConnection connection = new SqlConnection(GetEntityConnectionString()))
        {
            connection.Open();
            m.Connection = connection;
            SqlDataReader results = m.ExecuteReader();
            while (results.Read())
            {
                textResults.Append(String.Format("{0}", results[0]));
            }
        }

I used Activity Monitor within Sql Server Mgmt Studio on the database to inspect the exact query that was being sent. I then copied that query text to a query editor window within SSMS, and the query returned the expected results. However, SqlDataReader results is always empty, indicating "The enumeration returned no results."

My suspicion is that somehow the results are not being returned correctly, which makes me think there's something wrong with the code above, and not the query itself being passed.

Is there anything that would cause this in the code above? Or something I've overlooked?

EDIT:

Here is the query as indicated by the SQLCommand object:

SELECT DISTINCT StandardId,Number 
FROM vStandardsAndRequirements 
WHERE StandardId IN ('@param1','@param2','@param3') 
ORDER BY StandardId

Here is the query as it appears in Activity Monitor:

SELECT DISTINCT StandardId,Number 
FROM vStandardsAndRequirements 
WHERE StandardId IN ('ABC-001-0','ABC-001-0.1','ABC-001-0') 
ORDER BY StandardId

The query is working against a single view.

When I ran the second query against the database, it returned 3 rows.

The SqlDataReader indicates 0 rows.

Answer 1

try to use Sqldata adapter instead of sqldatreader.

StringBuilder textResults = new StringBuilder();

        using (var conn = new SqlConnection(GetEntityConnectionString())))
        {
            using (
                var cmd = new SqlCommand(
            "SELECT DISTINCT StandardId,Number" +
                "FROM vStandardsAndRequirements " +
            "WHERE StandardId IN (@param1,@param2,@param3)" +
            "ORDER BY StandardIdl"

       , conn))
            {

                var dSet = new DataSet();
                var dt = new Datatable();

                var da = new SqlDataAdapter(cmd);

                cmd.Parameters.Add("@param1", SqlDbType.VarChar, 50).Value = "ABC-001-0";
        cmd.Parameters.Add("@param2", SqlDbType.VarChar, 50).Value = "ABC-001-0.1";
                cmd.Parameters.Add("@param3", SqlDbType.VarChar, 50).Value = "ABC-001-0";
                try
                {

                    da.Fill(dSet);

        dt = dSet.Tables[0];

        foreach(Datarow a in dt.Rows)
        {

            textResults.Append(a["StandardId"].tostring()).AppendLine();


        }

        Messabox.Show(textResults.tostring);

                }
                catch (SqlException)
                {
                    throw;
                }

       finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
        }

Regards.

Answer 2

Are you sure it is

WHERE StandardId IN ('@param1','@param2','@param3') 

instead of this?

WHERE StandardId IN (@param1,@param2,@param3) 

Parameters should not be quoted, not in the SQLCommand object.

Answer 3

Very nice behavior I've observed

I looked for errors in code:

 ... dr = command.ExecuteReader()  ... If dr.Read Then ...

and found that 'dr.Read' works fine, but... when I mouseover on 'dr', to lookup for data, return values disappeared !