haysam haysam - 3 months ago 13
SQL Question

Retrieving value from sql ExecuteScalar()

I have the following:

String sql = "SELECT * FROM Temp WHERE Temp.collection = '" + Program.collection + "'";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
Program.defaultCollection = (String)cmd.ExecuteScalar();


And I want to get the second column after executing the statement. I know it will return only one row with two columns

I have read online that I will have to read each row of the result, is there any other way?

Answer

ExecuteScalar gets the first column from the first row of the result set. If you need access to more than that you'll need to take a different approach. Like this:

DataTable dt = new DataTable();
SqlDataAdapater sda = new SqlDataAdapter(sql, conn);
sda.Fill(dt);

Program.defaultCollection = dt.Rows[0]["defaultCollection"];

Now, I realize that the field name may not be defaultCollection, but you can fill that in.

From the MSDN documentation for ExecuteScalar:

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Now, as a final bit of advice, please wrap all ADO.NET objects in a using statement. Like this:

using (SqlConnection conn = new SqlConnection(connString))
using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn))
{
   DataTable dt = new DataTable();
   sda.Fill(dt);

   // do something with `dt`
}

this will ensure they are properly disposed.