Get Value to Return to VB.Net

I have the following stored procedure:
ALTER Procedure spInsert
@.UserName char(50),
@.Password char(15),
@.EmailAddress char(60),
@.TCoName char(50),
@.TCoAddress char(50),
etc.
@.UserID int OUTPUT,
@.TCoID int OUTPUT
AS
INSERT INTO tblLogin
VALUES
(
@.UserName,
@.Password,
@.EmailAddress
)
Declare @.Ident int
Select @.UserID = @.@.IDENTITY
Select @.Ident = @.UserID
INSERT INTO tblTCompany
VALUES
(
@.Ident,
@.TCoName,
@.TCoAddress,
etc.....
)
Declare @.Ident2 int
Select @.TCoID = @.@.IDENTITY
Select @.Ident2 = @.TCoID
I need to grab the @.Ident2 value into VB.Net (for a Web App). How do I get
the two applications to "talk" to each other?
Any suggestions will be greatly appreciated!
Sandy> I need to grab the @.Ident2 value into VB.Net (for a Web App).
You have a couple of options. One method is to return the value as a result
set:
SELECT @.Ident2
Another technique is to return the value as an OUTPUT parameter:
ALTER Procedure spInsert
@.UserName char(50),
@.Password char(15),
@.EmailAddress char(60),
etc.,
@.Ident2 OUT
AS
...
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:F826D886-345C-48DF-BE65-3725BA80E920@.microsoft.com...
>I have the following stored procedure:
> ALTER Procedure spInsert
> @.UserName char(50),
> @.Password char(15),
> @.EmailAddress char(60),
> @.TCoName char(50),
> @.TCoAddress char(50),
> etc.
> @.UserID int OUTPUT,
> @.TCoID int OUTPUT
> AS
> INSERT INTO tblLogin
> VALUES
> (
> @.UserName,
> @.Password,
> @.EmailAddress
> )
> Declare @.Ident int
> Select @.UserID = @.@.IDENTITY
> Select @.Ident = @.UserID
> INSERT INTO tblTCompany
> VALUES
> (
> @.Ident,
> @.TCoName,
> @.TCoAddress,
> etc.....
> )
> Declare @.Ident2 int
> Select @.TCoID = @.@.IDENTITY
> Select @.Ident2 = @.TCoID
> I need to grab the @.Ident2 value into VB.Net (for a Web App). How do I
> get
> the two applications to "talk" to each other?
> Any suggestions will be greatly appreciated!
> Sandy|||Hi Dan -
Thanks for your reply. How do you refer to the value in VB.Net, though?
What's the VB code you would write?
Sandy
"Dan Guzman" wrote:

> You have a couple of options. One method is to return the value as a resu
lt
> set:
> SELECT @.Ident2
> Another technique is to return the value as an OUTPUT parameter:
> ALTER Procedure spInsert
> @.UserName char(50),
> @.Password char(15),
> @.EmailAddress char(60),
> etc.,
> @.Ident2 OUT
> AS
> ...
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sandy" <Sandy@.discussions.microsoft.com> wrote in message
> news:F826D886-345C-48DF-BE65-3725BA80E920@.microsoft.com...
>
>|||Sandy,
Here's my table definition
CREATE TABLE dbo.Report (
ReportID int IDENTITY (1, 1) NOT NULL ,
Descr varchar (50) NOT NULL ,
ReportName varchar (50) NOT NULL
)
And the associated SP. Notice the @.ReportID as OUTPUT
and the SET @.ReportID as the last statement. That
returns the identity value in the OUTPUT parameter.
---
CREATE PROCEDURE dbo.usp_Report_Ins
@.Descr varchar(50),
@.ReportName varchar(50),
@.ReportID int OUTPUT
AS
INSERT INTO dbo.Report (
Descr,
ReportName
)
VALUES (
@.Descr,
@.ReportName
)
SET @.ReportID = SCOPE_IDENTITY()
This is a snippet of the Insert code. I use the the
Microsoft Data Access Application Block to do the
SQL stuff (SqlHelper statements) to update the DB.
Use whatever code works there. The last statement
retrieves the value of the identity field.
---
Dim params() As SqlParameter = New SqlParameter(2) {}
params(0) = New SqlParameter("@.Descr", Reports.Descr)
params(1) = New SqlParameter("@.ReportName", Reports.ReportName)
params(2) = New SqlParameter("@.ReportID", Reports.ReportID)
params(2).Direction = ParameterDirection.Output
SqlHelperParameterCache.CacheParameterSet(ConnectionSettings.cnString,
_
"usp_Report_Ins", params)
Dim result As Integer =
SqlHelper.ExecuteNonQuery(ConnectionSettings.cnString, _
CommandType.StoredProcedure, "usp_Report_Ins",
params)
dim PrimaryKey as Integer = CInt(params(2).Value)
On Thu, 10 Feb 2005 07:09:02 -0800, "Sandy"
<Sandy@.discussions.microsoft.com> wrote:
>Hi Dan -
>Thanks for your reply. How do you refer to the value in VB.Net, though?
>What's the VB code you would write?
>Sandy
>
>"Dan Guzman" wrote:
>|||larzeb's example shows how you can get a parameter output value. To
retrieve a value returned as a single-row single-column result, you can use
a number of methods, such as SqlCommand.ExecuteScalar,
SqlCommandExecuteReader or SqlDataAdapter.Fill.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:EBDC9678-1001-4809-A0C5-A524115190B0@.microsoft.com...
> Hi Dan -
> Thanks for your reply. How do you refer to the value in VB.Net, though?
> What's the VB code you would write?
> Sandy
>
> "Dan Guzman" wrote:
>