CODEWITHSUNDEEP
c#asp.netsql-servervisual-studio
user175084
user175084

Reputation: 4630

Store value in a variable after using select statement

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.

Upvotes: 3

Views: 23519

Answers (4)

oscaro
oscaro

Reputation: 21

You can also use SELECT IDENT_CURRENT('Policies') to get the value that was created for your record. To learn more about the differences between SCOPE_IDENTITY() AND IDENT_CURRENT('tablename'), check out this link.

Upvotes: 0

SLaks
SLaks

Reputation: 887225

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());
}

Upvotes: 6

Hans Kesting
Hans Kesting

Reputation: 39255

Did you just insert a new record into that policy table and now you want the ID given? Then instead of a max(), use 

SELECT SCOPY_IDENTITY()

to get the value that was assigned to your record, not to a record that happened to be inserted moments later.

Upvotes: 1

Gary McGill
Gary McGill

Reputation: 27516

DECLARE @id INTEGER
SELECT @id=PolicyID FROM ...

Upvotes: 2

Related Questions