C# SqlCommand - Setting resultset to a variable

I'm running a SQL command in C# that returns one value (a String). I'm then trying to save it to a variable, but it always throws this exception:

A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll The thread '' (0x1bbc) has exited with code 0 (0x0). System.InvalidOperationException: Invalid attempt to read when no data is present. at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)

However, when I run the same command in SQL Server, it definitely outputs a value.

myCommand = new SqlCommand("SELECT TrialName FROM dbo.CT WHERE NumId='"+TrialId+"'", myConnection);    
SqlDataReader dr = myCommand.ExecuteReader();  
String TName = dr[0].ToString(); 

Even if I har开发者_运维问答dcode the whole sqlcommand (instead of using the TrialId variable) it still throws the exception. What is going on here?

You must call dr.Read() before reading from a data reader:

myCommand = new SqlCommand("SELECT TrialName FROM dbo.CT WHERE NumId='"+TrialId+"'", 

myConnection);                
SqlDataReader dr = myCommand.ExecuteReader();                
if(dr.Read())
{
    String TName = dr[0].ToString(); 
}

SqlDataReader.Read() advances the reader to the next record, and returns false when there are no more records.

As the other answers point out, you need to call the Read() method on the SqlDataReader.

However if you're only returning one value from your select statement, you should consider using the SqlCommand.ExecuteScalar method. It doesn't require as much code as using a SqlDataReader.

Something like this:

string name = Convert.ToString(myCommand.ExecuteScalar());

You need to call dr.Read() before you can read from it. DataReader.Read() advances the DataReader to the next row. You have to call it for the first row, too, so that you can use it in a while loop:

while ( dr.Read() )
{
    ...
}

Hope this helps!

May I suggest using a parameterized query to help defend against SQL injection vulnerabilities? If you require ad-hoc SQL statements, instead of stored procedures, consider using something like this:

You could get away with using the ExecuteScalar() command if you only require the first column's value from the first row:

string TName;        
using (var conn = new SqlConnection(SomeConnectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT TrialName FROM dbo.CT WHERE NumId=@Trial";
    cmd.Parameters.AddWithValue("@Trial", TrialId);
    TName = cmd.ExecuteScalar().ToString();
}

If you require more than the single value, continue to use the SqlDataReader:

string TName;        
using (var conn = new SqlConnection(SomeConnectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT TrialName FROM dbo.CT WHERE NumId=@Trial";
    cmd.Parameters.AddWithValue("@Trial", TrialId);
    using (var reader = cmd.ExecuteReader())
    {
        if (reader.Read())
        {
            TName  = reader["TrialName"].ToString();
        }
    }
}