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

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
--
Gregg Walker