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
– entropic, Commented Feb 10, 2015 at 21:58
I actually deleted it a couple of mins ago, I am not using it anymore — codeFinatic
– codeFinatic, Commented Feb 10, 2015 at 22:01

Community · Accepted Answer · 2017-05-23 12:12:38Z

1

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

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Feb 10, 2015 at 22:02

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dour High Arch Over a year ago

This answer should be added to the FAQ.

Collectives™ on Stack Overflow

Getting Result From Select Command SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related