Retrieving Last inserted ID

[cjheath]

Hi

I am having some problems retrieving the last inserted id from the DB,
I really want to chuck this into a variable so I can use it anywhere.
The following is what I have got so far :-

string connStr =
ConfigurationManager.ConnectionStrings["dataConn"].ConnectionString;

SqlConnection objConn = new SqlConnection(connStr);
SqlCommand objCmd;

objConn.Open();
objCmd = new SqlCommand("rab_sp_err_addError", objConn);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.AddWithValue("@err_ErrorType", strErrorType);
objCmd.Parameters.AddWithValue("@err_BrowserType", strBrowserType);
objCmd.Parameters.AddWithValue("@err_Page", strPage);
objCmd.Parameters.AddWithValue("@sitename", strSiteName);
objCmd.Parameters.AddWithValue("@sitestatus", strSiteStatus);
objCmd.ExecuteNonQuery();
objConn.Close();

SqlCommand cmd = new SqlCommand("SELECT @@IDENTITY AS id", objConn);
objConn.Open();
object result = cmd.ExecuteScalar();
if (!(result == null))
{
Label1.Text += String.Format("{0:d}", result);
Label1.Text += "result!!<br />";
}
else
{
Label1.Text = "no match";
}

The first block of text runs fine and the record is put into the DB
but the second block where I try to get the indentity field brings
back nothing or if I play around with this it says something about an
incorrect cast.

Am I doing this completely the wrong way or is there something I have
missed since I am new to dot net.

Thanks
Chris

 

[Gregg Walker]

Chris,

Am I doing this completely the wrong way or is there something I have
missed since I am new to dot net.

Not completely...You just need to keep your connection open between your
insert proc and select @@identity calls. They need to share the same
connection.

Also you should use the scope_identity() function instead of @@identity.
@@Identity may not always return what you are expecting. Check the SQL
Server docs for an explanation.

Your code should work fine if you modify as such.

string connStr =
ConfigurationManager.ConnectionStrings["dataConn"].ConnectionString;

SqlConnection objConn = new SqlConnection(connStr);
SqlCommand objCmd;

objConn.Open();
objCmd = new SqlCommand("rab_sp_err_addError", objConn);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.AddWithValue("@err_ErrorType", strErrorType);
objCmd.Parameters.AddWithValue("@err_BrowserType", strBrowserType);
objCmd.Parameters.AddWithValue("@err_Page", strPage);
objCmd.Parameters.AddWithValue("@sitename", strSiteName);
objCmd.Parameters.AddWithValue("@sitestatus", strSiteStatus);
objCmd.ExecuteNonQuery();

SqlCommand cmd = new SqlCommand("SELECT scope_identity() AS id", objConn);
object result = cmd.ExecuteScalar();
if (!(result == null))
{
Label1.Text += String.Format("{0:d}", result);
Label1.Text += "result!!<br />";
}
else
{
Label1.Text = "no match";
}
objConn.Close();

 

[Gregg Walker]

Chris,

Another approach to consider is modifying your procedure to return the
identity value in an output parameter. Then you only need one command to
get the job done. That is how I code my stored procs that perform inserts
with identity values.

Your code could be something like this...

string connStr =
ConfigurationManager.ConnectionStrings["dataConn"].ConnectionString;

SqlConnection objConn = new SqlConnection(connStr);
SqlCommand objCmd;

objConn.Open();
objCmd = new SqlCommand("rab_sp_err_addError", objConn);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.AddWithValue("@err_ErrorType", strErrorType);
objCmd.Parameters.AddWithValue("@err_BrowserType", strBrowserType);
objCmd.Parameters.AddWithValue("@err_Page", strPage);
objCmd.Parameters.AddWithValue("@sitename", strSiteName);
objCmd.Parameters.AddWithValue("@sitestatus", strSiteStatus);

// Add Out Parameter to hold Identity Value
SqlParameter paramId = new SqlParameter("@id", DBNull.Value);
paramId.Direction = ParameterDirection.Output;
objCmd.Parameters.Add(paramId);

// Execute query with Id returned in paramId.Value
objCmd.ExecuteNonQuery();

objConn.Close();

object result = paramId.Value;
if(!(result == null || result is DBNull))
{
Label1.Text += String.Format("{0:d}", result);
Label1.Text += "result!!<br />";
}
else
{
Label1.Text = "no match";
}

Here's an example of a stored procedure that returns an identity value in an
output parameter...

CREATE PROCEDURE [dbo].[AddSite]
@OrganizationId int,
@Name varchar(50),
@MapLabel varchar(50) = NULL,
@SiteTypeID int,
@SiteCode varchar(4) = NULL,
@Address1 varchar(50) = NULL,
@Address2 varchar(50) = NULL,
@City varchar(40) = NULL,
@StateCode char(2) = NULL,
@ZipCode varchar(9) = NULL,
@LatitudeN decimal(10,6),
@LatitudeS decimal(10,6),
@LongitudeE decimal(11,6),
@LongitudeW decimal(11,6),
@EffectiveUtc datetime = NULL,
@ExpirationUtc datetime = NULL,
@SiteID bigint output
AS

declare @ErrorCode int;
declare @CurrentUtc datetime;

set @SiteID = NULL;

set @MapLabel = IsNull(@MapLabel, @Name);

set @CurrentUtc = GetUtcDate();

set @EffectiveUtc = IsNull(@EffectiveUtc, @CurrentUtc);

insert into Site (
OrganizationID,
Name,
MapLabel,
SiteTypeID,
SiteCode,
Address1,
Address2,
City,
StateCode,
ZipCode,
LatitudeN,
LatitudeS,
LongitudeE,
LongitudeW,
CreatedUtc,
EffectiveUtc,
ExpirationUtc
)
values (
@OrganizationID,
@Name,
@MapLabel,
@SiteTypeID,
@SiteCode,
@Address1,
@Address2,
@City,
@StateCode,
@ZipCode,
@LatitudeN,
@LatitudeS,
@LongitudeE,
@LongitudeW,
@CurrentUtc,
@EffectiveUtc,
@ExpirationUtc
);

set @ErrorCode = @@Error;

if @ErrorCode <> 0 begin

return @ErrorCode;

end

set @SiteID = scope_identity();

return 0;

hth