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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
russomrAuthor Commented:
Now I'm getting:

System.InvalidCastException: Specified cast is not valid.

on the line of:

int newRecID = (int) cmd.ExecuteScalar();
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you debug what the ExecuteScalar() function returns as data type?
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

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?
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?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
int is actually too small for the "int" data type of sql, you need to use long instead.
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.
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?
russomrAuthor Commented:
True, you did.  I tend to get off topic at times.  Thanks.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.