Return value of a select statement

Question

I want to retrieve the resulting value of a select statement into a string variable like this:

OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = GetConnection();
cmd1.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
cmd1.ExecuteNonQuery();
     

I want to place the selected treatment value into a string variable. How can I do this?

Answer 1

Use ExecuteReader() and not ExecuteNonQuery(). ExecuteNonQuery() returns only the number of rows affected.

try
{
    SqlDataReader dr = cmd1.ExecuteReader();
}
catch (SqlException oError)
{

}
while(dr.Read())
{
    string treatment = dr[0].ToString();
}

Or better, use a using statement for it.

using(SqlDataReader dr = cmd1.ExecuteReader())
{
    while(dr.Read())
    {
        string treatment = dr[0].ToString();
    }
}

But if your SqlCommand returns only 1 column, you can use the ExecuteScalar() method. It returns first column of the first row as follows:-

cmd.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
string str = Convert.ToString(cmd.ExecuteScalar());

Also you can open your code to SQL Injection. Always use parameterized queries. Jeff has a cool blog article called Give me parameterized SQL, or give me death. Please read it carefully. Also read DotNetPerl SqlParameter article. SQL Injection very important when you are working queries.