mac007 mac007 - 4 months ago 35
C# Question

Return value of a select statement

I have this little problem. 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 the to place the selected treatment value into a string variable. How can i do this. Thanks

Answer

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.