Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 27th, 2006, 10:43 AM
Authorized User
Join Date: Jan 2006
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Scope_Identity with @Return_Value

I am currently writing an application that inserts data into a MS SQL Server 2000 DB. I am developing the application in Microsoft Visual Web Developer 2005 EE. So far everything has come together nicely. However, I have run into a situation that I can't seem to figure out.

I wrote a stored procedure to insert data into the DB from a Web Form. The insert works nicely; however, one of the requirements I have is that I would like the DB to return the last row inserted into to my table. I am using the Scope_Identity() function at the end of my stored procedure to accomplish this task:

...Last few lines of Stored Procedure...




I have conducted several tests on the SP, and received the following output from the DB when it is run:

(1 row(s) affected)
(0 row(s) returned)
Finished running [dbo].[usp_InsertProject].

I would like to take the result of the @RETURN_VALUE (116), and create a variable that I can use to embed into an e-mail. The code for the e-mail generation is working however, the value that comes through for the @RETURN_VALUE is always 0. I have tried several different things to get this to work, but so far no luck.

Here is the application code I am using to create the e-mail:

Sub SendEmail()

        Dim mySqlDataSource1 = SqlDataSource1.ConnectionString.ToString

        Dim myConnection As New Data.SqlClient.SqlConnection(mySqlDataSource1)
        Dim myCommand As New Data.SqlClient.SqlCommand("usp_InsertProject", myConnection)

        myCommand.CommandType = Data.CommandType.StoredProcedure

        Dim parameterGID As New Data.SqlClient.SqlParameter("@RETURN_VALUE", Data.SqlDbType.Int)
        parameterGID.Direction = Data.ParameterDirection.ReturnValue


        Dim reader As Data.SqlClient.SqlDataReader = myCommand.ExecuteReader()

        Dim GID As Integer = CInt(parameterGID.Value)

       ...E-mail code is below this, but is working, so not included ...

    End Sub

I would like to insert the GID variable into the e-mail, but for some reason it won't work. The following error occurs when the InsertCommand is invoked:

ExecuteReader requires an open and available Connection. The connection's current state is closed.:(

Old April 28th, 2006, 08:46 AM
Authorized User
Join Date: Jan 2006
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts

I got it to work by using the following code:

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEvent Args) Handles SqlDataSource1.Inserted

        Dim myID As Integer

        myID = e.Command.Parameters("@GID").Value


        Dim Email As New Net.Mail.MailMessage()

   E-mail code below this...

The key to getting it to work was putting it in the SqlDataSource1_Inserted event. This allowed me to get the output parameter.

Similar Threads
Thread Thread Starter Forum Replies Last Post
CreateUserWizard secondary table Return_Value jonnyO ADO.NET 0 November 16th, 2006 01:41 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.