Retrieve the last id from the SQL Server database table

Question

How do I get the last id created in the policy table and store it into a variable so that I can use it for another table called backupspec table.

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;

            //tell the compiler and database that we're using parameters (thus the @first, @last, @nick)  
            dataCommand.CommandText = ("Insert Policies ( PolicyName, PolicyDesc, TimeAdded,OSFlag, CreateVSSSnapshot, CreateAuditLogForRecoveries, AllowUsersToOverwriteFiles, AutoHandleEnvErrors, NotifyOnEnvErrorCount, NotifyOnFileFailure, NotifyOnFileFailureCount, NotifyOnLackOfPCContact, NotifyOnLackOfPCContactDays, NotifyOnRecoveryFailures, NotifyOnRecoveryFailureReason) values (@pn,@pd,@TimeAdded,@os,@vss,@al,@uow,@hee,@oeec,@off,@offc,@oloc,@olocd,@orf,@orfr)");




        dataCommand.Parameters.AddWithValue("@pn",pn);
        dataCommand.Parameters.AddWithValue("@pd",pd);
        dataCommand.Parameters.AddWithValue("@TimeAdded",TimeAdded);
        dataCommand.Parameters.AddWithValue("@os",os);
        dataCommand.Parameters.AddWithValue("@vss",vss);
        dataCommand.Parameters.AddWithValue("@al",al);
        dataCommand.Parameters.AddWithValue("@uow",uow);
        dataCommand.Parameters.AddWithValue("@hee",hee);
        dataCommand.Parameters.AddWithValue("@oeec",oeec);
        dataCommand.Parameters.AddWithValue("@off",off);
        dataCommand.Parameters.AddWithValue("@offc",offc);
        dataCommand.Parameters.AddWithValue("@oloc",oloc);
        dataCommand.Parameters.AddWithValue("@olocd",olocd);
        dataCommand.Parameters.AddWithValue("@orf",orf);
        dataCommand.Parameters.AddWithValue("@orfr",orfr);

        dataConnection.Open();

        dataCommand.ExecuteNonquery();


        dataConnection.Close();


        ArrayList jaja = (ArrayList)Session["BackupSpecList"];


        for (int i = 0; i < jaja.Count; i++)
        {
            BackupSpecEntry bsp = (BackupSpecEntry)jaja[i];
            string path = bsp.path;
            string inclExcl = bsp.inclExcl;
            byte inclExclFlags = bsp.inclExclFlags;
            bool indexContents = bsp.indexContents;
            int serverBackupSpecId = bsp.serverBackupSpecId;
            int freq = bsp.freq;
            int retention = bsp.retention;
            int policyID =DONT KNOW HOW TO GET THIS VALUE;
            long specChangeTime = 0;
            long backupTime = 0;

            dataCommand.CommandText = ("Insert BackupSpec (PolicyID, Path, ServerBackupSpecID, Freq, Retention, InclExclFlags, InclExcl, IndexContents, SpecChangeTime, BackupTime) values (@policyID,@path,@serverBackupSpecId,@freq,@retention,@inclExclFlags,@inclExcl,@indexContents,@specChangeTime,@backupTime)");

            dataCommand.Parameters.AddWithValue("@policyID", policyID);
            dataCommand.Parameters.AddWithValue("@path", path);
            dataCommand.Parameters.AddWithValue("@serverBackupSpecId", serverBackupSpecId);
            dataCommand.Parameters.AddWithValue("@freq", freq);
            dataCommand.Parameters.AddWithValue("@retention", retention);
            dataCommand.Parameters.AddWithValue("@inclExclFlags", inclExclFlags);
            dataCommand.Parameters.AddWithValue("@inclExcl", inclExcl);
            dataCommand.Parameters.AddWithValue("@indexContents", indexContents);
            dataCommand.Parameters.AddWithValue("@specChangeTime", specChangeTime);
            dataCommand.Parameters.AddWithValue("@backupTime", backupTime);


            dataConnection.Open();
            dataCommand.ExecuteNonQuery();
            dataConnection.Close();

        }

I am getting error with the label id...

can some 1 help me with this..??

I am not getting the last policyID created after inserting please help... Please help

Answer 1

You can use either SCOPE_IDENTITY or @@IDENTITY

SCOPE_IDENTITY:

strSQL = "INSERT INTO Policies (...) VALUES (@vals....);SELECT SCOPE_IDENTITY()";
SQLCommand.CommandText = strSQL;
IdReturned = SQLCommand.ExecuteScalar();

@@IDENTITY:

strSQL = "INSERT INTO Policies (...) VALUES (@vals....);SELECT @@Identity";
SQLCommand.CommandText = strSQL;
IdReturned = SQLCommand.ExecuteScalar();

For the differences between the two i recommend reading this article

Answer 2

Use scope_identity:

strSQL = "INSERT INTO Policies (...) VALUES (@vals....);SELECT @result = scope_identity()"
SQLCommand.CommandText = strSQL;
SQLCommand.Parameters.Add("@result", SqlDbType.Int);
SQLCommand.ExecuteScalar();
int id = SQLCommand.Parameters["@result"].Value;

Answer 3

If you do a INSERT INTO Policies() call first, in order to get the lastid, you could do something like this:

int lastId = 0;
using(SqlConnection Connection = new SqlConnection("(connection string)"))
{
  string queryStatement = 
    "INSERT INTO dbo.Policies(fields) OUTPUT Inserted.LastID VALUES(....)";

  using(SqlCommand Command = new SqlCommand(queryStatement, Connection))
  {
     Connection.Open();
     lastId = Command.ExecuteScalar();
     Connection.Close();
  }
}

Use the OUTPUT ....... clause to return the newly inserted lastId.

Then go on and use that value in your main query.

Marc