Getting Result From Select Command SQL Server

Question

I am trying to get the result from a select command:

 string strName = dtTable.Rows[i][myName].ToString();
 string selectBrand = "SELECT [brand] FROM [myTable] WHERE [myName] = '" + strName + "'";

 SqlCommand sqlCmdSelectBrand = new SqlCommand(selectBrand , sqlConn);
 sqlCmdSelectBrand .Connection.Open();
 sqlCmdSelectBrand .ExecuteNonQuery();                         

 string newBrand = Convert.ToString(sqlCmdSelectBrand .ExecuteScalar());                          
 sqlCmdSelectBrand .Connection.Close();

The select works, I have executed it in SQL Studio, but it does not assign to my variable on the second to last line. Nothing gets assigned to that variable when I debug it...

Any advice?

Side note: Is there a reason you're calling `sqlCmdSelectBrand.ExecuteNonQuery()`? That doesn't need to be there based on the way the code is written. — entropic, Feb 10 '15 at 21:58
I actually deleted it a couple of mins ago, I am not using it anymore — codeFinatic, Feb 10 '15 at 22:01

score 1 · Answer 1 · edited May 23 '17 at 12:12

Your approach to read data returned from a SELECT query is (in this particular context) a bit wrong. Usually you call ExecuteReader of the SqlCommand instance to get back your data.

string strName = dtTable.Rows[i][myName].ToString();
string selectBrand = "SELECT [brand] FROM [myTable] WHERE [myName] = @name";

using(SqlCommand sqlCmdSelectBrand = new SqlCommand(selectBrand , sqlConn))
{
    sqlCmdSelectBrand.Parameters.Add(
             new SqlParameter("@name", SqlDbType.NVarChar)).Value = strName;
    sqlCmdSelectBrand .Connection.Open();
    using(SqlDataReader reader = sqlCmdSelectBrand.ExecuteReader())
    {
        if(reader.HasRows)
        {
           reader.Read();
           string newBrand = reader.GetString(reader.GetOrdinal("Brand"));
           ..... work with the string newBrand....
        }
        else
            // Message for data not found...

        sqlCmdSelectBrand .Connection.Close();
    }
}

In your context, the call to ExecuteNonQuery is not required because it doesn't return anything from a SELECT query. The call to ExecuteScalar should work if you have at least one record that match to the WHERE condition

Notice also that you should always use a parameterized query when building an sql command text. Also if you think to have full control of the inputs, concatenating string is the open door to Sql Injection

This answer should be added to the FAQ. – Dour High Arch Feb 10 '15 at 22:37 — Dour High Arch, Feb 10 '15 at 22:37

Getting Result From Select Command SQL Server

1 Answers1

Linked