Retrieving value from sql ExecuteScalar()
c#, database, sql, sql-server
Solution
`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.
Problem
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?