?
Solved

Can't retrieve Scope_Identity()

Posted on 2006-04-26
11
Medium Priority
?
677 Views
Last Modified: 2009-03-15
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;
}
0
Comment
Question by:russomr
  • 5
  • 5
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 16544651
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
 

Author Comment

by:russomr
ID: 16545710
Now I'm getting:

System.InvalidCastException: Specified cast is not valid.

on the line of:

int newRecID = (int) cmd.ExecuteScalar();
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16546754
can you debug what the ExecuteScalar() function returns as data type?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:russomr
ID: 16547329
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
 

Author Comment

by:russomr
ID: 16552087
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16552115
int is actually too small for the "int" data type of sql, you need to use long instead.
0
 

Author Comment

by:russomr
ID: 16552293
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16554670
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
 

Author Comment

by:russomr
ID: 16554720
True, you did.  I tend to get off topic at times.  Thanks.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16554805
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Loops Section Overview
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question