C# SqlCommand - Setting resultset to a variable

Question

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 hardcode the whole sqlcommand (instead of using the TrialId variable) it still throws the exception. What is going on here?

Answer 1

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.

Answer 2

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());