ExecuteScalar problems

[PsychoCoder]

OK,

I have a stored procedure entering data, I am then selecting the SCOPE_IDENTITY to get the id of the record, in my code behind I have this code which, in theory, will execute the SP and return the ID, problem is Im getting a reference not set to an instance of an object error. The code is as follows:

Dim NewId As Integer
'##INSERT APP INFO##
sSQL = "EXECUTE Turbo_InsertAppChange app_name,@app_developer,@app_rq_num,@app_completition_date,@app_description"

Command = New SqlCommand(sSQL, Connection)
Command.Parameters.AddWithValue("@app_name", application_name.Text.ToString)
Command.Parameters.AddWithValue("@app_developer", developer_list.SelectedValue.ToString)
Command.Parameters.AddWithValue("@app_rq_num", rq_num.Text.ToString)
Command.Parameters.AddWithValue("@app_completition_date", CType(Api_calendar1.DDate, Date))
Command.Parameters.AddWithValue("@app_description", proj_desc.Text.ToString)
Try
  Connection.Open()
  [b]NewId = Command.ExecuteScalar[/b]

The bold line is where Im getting the error. By the way at the top of the page I have:

Private Shared Conn As String = Common.GetConnectionString("api_sap")
Private Shared Connection As New SqlConnection(Conn)
Private Shared Command As SqlCommand

So those variables are declared.

The stored procedure is as follows:

CREATE PROCEDURE *Stored_Procedure_Name*(@app_name varchar(50),@app_developer char(3),@app_rq_num char(10),@app_completition_date datetime,
						  @app_description varchar(1500))
AS
INSERT INTO
	*Table_Name*(app_name,app_developer,app_rq_num,app_completition_date,app_description,date_entered)
SELECT
	@app_name,
	@app_developer,
	@app_rq_num,
	CONVERT(DATETIME,@app_completition_date),
	@app_description,
	GETDATE()
SELECT 
	SCOPE_IDENTITY() as 'NewID'

Ive never used ExecuteScalar before so what am I doing wrong?

Senior Qik III,.Net,SQL Programmer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
** Do NOT feed Code Gremlins after midnight **
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[ca8msm]

You are missing an @ symbol from your first parameter so that may be it.

However, rather than pass in the EXECUTE command with the names of the parameters, try just passing in the name of the SP and setting the command type to a Stored Procedure.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[PsychoCoder]

ca8msm,

However, rather than pass in the EXECUTE command with the names of the parameters, try just passing in the name of the SP and setting the command type to a Stored Procedure.

I tried this and Im still getting the reference not set to an instance of an object error, but when I run the procedure alone in QueryAnalyzer it returns the ID of the new record.

Senior Qik III,.Net,SQL Programmer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
** Do NOT feed Code Gremlins after midnight **
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[DotNetGnat]

try putting...

SET NOCOUNT ON

inside your stored procedure

-DNG

 

[PsychoCoder]

DotNetGnat,

Tried that still getting the same error. Ive tried everything Ive found on the internet to no avail. Maybe if someone else looks at this code they'll see the problem:

Dim NewId As Integer
'##INSERT APP INFO##
sSQL = "Turbo_InsertAppChange"

Command = New SqlCommand(sSQL, Connection)
Command.CommandType = CommandType.StoredProcedure
Command.Parameters.Add("@app_name", SqlDbType.VarChar).Value = application_name.Text.ToString
Command.Parameters.Add("@app_developer", SqlDbType.Char).Value = developer_list.SelectedValue.ToString
Command.Parameters.Add("@app_rq_num", SqlDbType.VarChar).Value = rq_num.Text.ToString
Command.Parameters.Add("@app_completition_date", SqlDbType.DateTime).Value = CType(Api_calendar1.DDate, Date)
Command.Parameters.Add("@app_description", SqlDbType.VarChar).Value = proj_desc.Text.ToString
Try
   Connection.Open()
   NewId = Command.ExecuteScalar()
   ......

Senior Qik III,.Net,SQL Programmer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
** Do NOT feed Code Gremlins after midnight **
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[DotNetGnat]

also use IDENT_CURRENT('tablename') instead of scope_identity

-DNG

 

[PsychoCoder]

DotNetGnat,

Still no luck, this is driving me absolutely CRAZY as I know ExecuteScalar works.

Senior Qik III,.Net,SQL Programmer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
** Do NOT feed Code Gremlins after midnight **
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[ca8msm]

also use IDENT_CURRENT('tablename') instead of scope_identity

Why?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[DotNetGnat]

ca8msm, thanks for asking me the question...i get little confused with scope_identity() and ident_current()

may be scope_identity() is the best to use in this context as OP did...any ways here is some info:

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

-DNG

 

[ca8msm]

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table

Exactly, which is why I was confused you'd mentioned it. Surely using that method, there is the potential that it would return the incorrect value. This wouldn't happen with scope_identity().


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[PsychoCoder]

I fixed the problem. Thank you.

 

[jbenson001]

How did you fix the problem?

 

[PsychoCoder]

jbenson001,

Odly enough the only thing I changed was added SELECT SCOPE_IDENTITY AS 'ReturnValue' in the stored procedure. I know it sounds funky but it now works.

Senior Qik III, ASP.Net, VB.Net ,SQL Programmer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
** Do NOT feed Code Gremlins after midnight **
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[jbenson001]

That would do it..

Jim