Getting the value that was just inserted passed back

[gtjr921]

ASP.NET 2.0 and SQL 2005

I have a stored procedure that is the datasource for a form.
This form allows new employees to be entered etc.
Once this data is inserted I want to redirect the user to that employees page based on the ID. I know how to do something like response.redirect(webpage.aspx?ID=1234)
What i don't know how to do is to get the scope identity or the newest Employee id (I don't think i can get the scopeID into ASP.net)
I guess I could do something like select top 1 ID from employee order by desc but that is probably not the best way.

 

[ca8msm]

The scope identity will work fine. Just pass it back from your stored procedure and then redirect the user.


____________________________________________________________

Need help finding an answer?

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

 

[gtjr921]

Ok but that is what i am unsure of How do i pass it back?
I have a variable in my Stored Proc that captures the scope but how do i send it back to the asp.net page?

 

[jbenson001]

In your sp, create an output parameter and send the value back in that. Then in your page, you also need an output parameter which will hold the value once the sp is finished executing.

 

[ca8msm]

There are a couple of methods. One would be to create an OUT parameter in your SP and in your ASP.NET code set the ParameterDirection of the relevant parameter.


____________________________________________________________

Need help finding an answer?

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

 

[ca8msm]

Also, check out the help files




____________________________________________________________

Need help finding an answer?

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

 

[gtjr921]

I tried this
but it only get me the return value of 0 not of my sql variable for the employee id

Relevent Sql 2005

Declare @NEWEMPID int
--after an insert happens in sql
Set @NEWEMPID= SCOPE_IDENTITY()
--last thing in my sql
Return	@NEWEMPID

Dim retValParam As New SqlParameter("@NEWEMPID", SqlDbType.Int)
        retValParam.Direction = ParameterDirection.ReturnValue
               sqlEmpNewEmp.Insert()
        Dim EmpIDVar As Integer = Convert.ToInt32(retValParam.Value)    
               Response.Redirect("[URL unfurl="true"]http://www.mysite.com?EMPID="[/URL] + Server.HtmlEncode(EmpIDVar))
        sqlEmpNewEmp.InsertParameters.Clear()

 

[jbenson001]

in the sp create a paramater as an output parameter
@myID integer OUTPUT

In the asp.net code
retValParam.Direction = ParameterDirection.OutPut

 

[gtjr921]

If I do that I get
Procedure or Function 'uspInsertEmployee' expects parameter '@NEWEMPID', which was not supplied
It is treating my output parameter as if it were an input parameter

 

[tperri]

why don't you just return scope_identity() without putting it into a variable?

 

[gtjr921]

tperri
why don't you just return scope_identity() without putting it into a variable?

because the stored procedure inserts into multiple tables, once it uses scope once it is lost so i have to put it into a variable

 

[tperri]

show us your code for declaring the output variable for your stored proc

 

[gtjr921]

I just put it in the list of variables
like so
@EmpID int OUTPUT

 

[tperri]

Show your full .net code, or else I dont think anyone can help with the error you're getting.

 

[gtjr921]

tperri Show your full .net code, or else I dont think anyone can help with the error you're getting.

That is all my code except unless you want to see my other 50 variables that are not relevant.

IT all happends on the wizard finish button click

Sub InsertNew(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) _
    Handles Wizard1.FinishButtonClick
'i have tried addind the output variable this way 
Dim retValParam As New SqlParameter("@NEWEMPID", SqlDbType.Int)
'and this way (both give same result
'sqlEmpNewEmp.InsertParameters.Add("NEWEMPID", 'retValParam.SqlValue)
        retValParam.Direction = ParameterDirection.ReturnValue
               sqlEmpNewEmp.Insert()
        Dim EmpIDVar As Integer = Convert.ToInt32(retValParam.Value)    
               Response.Redirect("[URL unfurl="true"]http://www.mysite.com?EMPID="[/URL] + Server.HtmlEncod
e(EmpIDVar))
        sqlEmpNewEmp.InsertParameters.Clear()

 

[gtjr921]

also tried retValParam.Direction = ParameterDirection.output

 

[gtjr921]

anyone else have any ideas on this?
Thanks!

 

[jbenson001]

The parameter direction is OUTPUT not RETURN VALUE.. they are 2 different things.
Also, you are clearing your parameters

sqlEmpNewEmp.InsertParameters.Clear()

Make sure you get the ouput paramater value before you clear them.

 

[gtjr921]

jbenson001
The parameter direction is OUTPUT not RETURN VALUE.. they are 2 different things.
Also, you are clearing your parameters

I've tried all that even commented out the clear, changed from return value to output same result.