Can't retrieve Scope_Identity()

I'm trying to retrieve the primary key that was just generated in an insert statement.  My sql stored proc is the following:

CREATE PROCEDURE usp_BRS_MainData_ins
      @AgencyName varchar(50),
      @Code varchar(50),
      ...
      @LossHistory varchar(1000)
AS
INSERT INTO tblBRSMainData (
      AgencyName,
      Code,
      ...
      LossHistory
)
VALUES (
      @AgencyName,
      @Code,
      ...
      @LossHistory
)

RETURN SCOPE_IDENTITY()
GO

My code to retrieve it in asp.net 1.1 c# is:

string sSQL = "usp_BRS_MainData_ins";
SqlConnection cn = CommonFunctions.OpenDatabase("DepartmentCLD");
SqlCommand cmd = new SqlCommand(sSQL, cn);
cmd.CommandType = CommandType.StoredProcedure;

SQLScripts.AddParameter("@AgencyName", SqlDbType.VarChar, txtAgencyName.Text, cmd);
...
cn.Open();
int newRecID = (int) cmd.ExecuteScalar();
...

There's a lot of junk code I'm leaving out, but do I need to somehow specify in my .net code that I'll need to retrieve a value?  The error I'm getting is:

System.NullReferenceException: Object reference not set to an instance of an object.

I get it on the "int newRecID=..." line.

The SQLScripts.AddParameter is a custom method that I wrote:

public static void AddParameter(string p_ParameterName, SqlDbType p_DBType, string p_FieldName, SqlCommand p_CommandType)
{
      p_CommandType.Parameters.Add(new SqlParameter(p_ParameterName, p_DBType));
      p_CommandType.Parameters[p_ParameterName].Value = p_FieldName;
}
russomrAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this version of the procedure:

CREATE PROCEDURE usp_BRS_MainData_ins
     @AgencyName varchar(50),
     @Code varchar(50),
     ...
     @LossHistory varchar(1000)
AS
SET NOCOUNT ON
INSERT INTO tblBRSMainData (
     AgencyName,
     Code,
     ...
     LossHistory
)
VALUES (
     @AgencyName,
     @Code,
     ...
     @LossHistory
)

SELECT SCOPE_IDENTITY()
GO
0
 
russomrAuthor Commented:
Now I'm getting:

System.InvalidCastException: Specified cast is not valid.

on the line of:

int newRecID = (int) cmd.ExecuteScalar();
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you debug what the ExecuteScalar() function returns as data type?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
russomrAuthor Commented:
Not sure what you mean.  Stored Proc is returning a number, at least when I run it in query analyzer it does.

How can I find out officially what ExecuteScalar() is returning as a data type?
0
 
russomrAuthor Commented:
Ok, it looks like it is returning a decimal.  When I use the following code:

...
cn.Open();

decimal returnRecID;
returnRecID = (decimal) cmd.ExecuteScalar();

Response.Write("returnRecID: " + returnRecID);
Response.End();

cmd = null;
cn.Close();
cn = null;
...

It writes out the number.  But when I switch it to type int, it errors out like above.  What's going on?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
int is actually too small for the "int" data type of sql, you need to use long instead.
0
 
russomrAuthor Commented:
Figured it out.  It was the way I was converting it.  I used this:

int returnRecID = Convert.ToInt32(cmd.ExecuteScalar());

And it worked.  Thanks anyway for the attepted help.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Hmmm, I actually solved (with the SET NOCOUNT ON ) the first (Initial) problem of the ".. object not set ..", and then I gave the hint to check out the return data type of the ExecuteScalar() function...
What do you think?
0
 
russomrAuthor Commented:
True, you did.  I tend to get off topic at times.  Thanks.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Glad I could help, I hope no offense taken!

it's often happening that one problem hides another (like on rail crossings one train can hide another), you want obviously the entire solution (all problems solved).
Also, the initial problem was not a ASP.NET problem actually, while the second one was a "generic" problem, with however a ASP.Net-specific code solution.

CHeers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.