Store value in a variable after using select statement

asp.net, c#, sql-server, visual-studio

Solution

Use the SqlCommand.ExecuteScalar method, like this:

command.CommandText = @"select max(PolicyID) from Policies";
int maxPolicyId = (int)command.ExecuteScalar();

Also, if you're doing this to insert a new Policy row with a unique ID, you must not do it like this, because it's entirely possible that a different Policies row will be inserted between the select and the insert.

Instead, use an `IDENTITY` column or a `UNIQUEIDENTIFIER` column.

EDIT: To use this in your code, do this:

int maxId;
using (SqlConnection dataConnection = new SqlConnection(@"Data Source=JAGMIT-PC\SQLEXPRESS;Initial Catalog=SumooHAgentDB;Integrated Security=True"))
using (SqlCommand dataCommand = 
        new SqlCommand("select max(PolicyID) from Policies", dataConnection)) {

    dataConnection.Open();
    maxId = Convert.ToInt32(dataCommand.ExecuteScalar());
}

Problem

How to store the value of the `PolicyID` returned from database in an integer variable in `C#`? I am using SQL server 2005. ``` System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection(); dataConnection.ConnectionString = @"Data Source=JAGMIT-PC\SQLEXPRESS;Initial Catalog=SumooHAgentDB;Integrated Security=True"; System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand(); dataCommand.Connection = dataConnection; dataCommand.CommandText = ("select PolicyID from Policies where PolicyID=(select max(PolicyID) from Policies)"); dataConnection.Open(); dataCommand.ExecuteNonQuery(); dataConnection.Close(); ``` Please suggest. Thanks.

Original source