Link to home
Start Free TrialLog in
Avatar of russomr
russomr

asked on

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;
}
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of russomr
russomr

ASKER

Now I'm getting:

System.InvalidCastException: Specified cast is not valid.

on the line of:

int newRecID = (int) cmd.ExecuteScalar();
can you debug what the ExecuteScalar() function returns as data type?
Avatar of russomr

ASKER

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?
Avatar of russomr

ASKER

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?
int is actually too small for the "int" data type of sql, you need to use long instead.
Avatar of russomr

ASKER

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.
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?
Avatar of russomr

ASKER

True, you did.  I tend to get off topic at times.  Thanks.
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