Store sp results to variable ?

[Rob]

Using VB.net and SQL server...

I have a stored procedure that is simply returning a row count....

I know how to execute a stored procedure and add the output parameter.

But how do I store that value to a variable ?

 

[Chris]

Using VB.net and SQL server...

I have a stored procedure that is simply returning a row count....

I know how to execute a stored procedure and add the output parameter.

But how do I store that value to a variable ?

Just return the value from the proc like:
Select @ValueFromProc

Then in VB do:
dim RowCount as Integer = SQLCommandObject.ExecuteScaler()

Chris

 

[Rob]

I tried, but it does not return the correct answer.... I must be doing
something wrong.
Here is my exact code..


CREATE PROCEDURE Testsp
@OutCntNewSalesperson int output
AS

SET NOCOUNT ON

Declare @ErrorCode int
Select @ErrorCode = @@Error

if @ErrorCode =0
BEGIN
SELECT @OutCntNewSalesperson = COUNT(*)
FROM dbo.SalespersonMaster
WHERE (NewSalesperson = 'Y')
Select @ErrorCode = @@Error
END
GO

The VB.net code below

Dim cmd As New OleDbCommand
Dim intNewSalesperson As Integer

cmd = cn.CreateCommand()

cmd.CommandText = "Testsp"
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@OutCntNewSalesperson",
OleDbType.Integer).Direction = ParameterDirection.Output


Dim RowCount As Integer = cmd.ExecuteScalar()

I simply want to store the value of the Output @OutCntNewSalesperson from
the sp to a variable.

Thanks !

 

[Chris]

I tried, but it does not return the correct answer.... I must be doing
something wrong.
Here is my exact code..


CREATE PROCEDURE Testsp
@OutCntNewSalesperson int output
AS

SET NOCOUNT ON

Declare @ErrorCode int
Select @ErrorCode = @@Error

if @ErrorCode =0
BEGIN
SELECT @OutCntNewSalesperson = COUNT(*)
FROM dbo.SalespersonMaster
WHERE (NewSalesperson = 'Y')
Select @ErrorCode = @@Error
END
GO

The VB.net code below

Dim cmd As New OleDbCommand
Dim intNewSalesperson As Integer

cmd = cn.CreateCommand()

cmd.CommandText = "Testsp"
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@OutCntNewSalesperson",
OleDbType.Integer).Direction = ParameterDirection.Output


Dim RowCount As Integer = cmd.ExecuteScalar()

I simply want to store the value of the Output @OutCntNewSalesperson from
the sp to a variable.

Thanks !

Well executescaler is not going to get you the output variable. The way
I showed you was as follows.

CREATE PROCEDURE Testsp
AS

SET NOCOUNT ON

BEGIN
SELECT COUNT(*)
FROM dbo.SalespersonMaster
WHERE (NewSalesperson = 'Y')
END

GO

 

[Rob]

Thanks Chris,
Yep, that works fine...

Well executescaler is not going to get you the output variable. The way I
showed you was as follows.

CREATE PROCEDURE Testsp
AS

SET NOCOUNT ON

BEGIN
SELECT COUNT(*)
FROM dbo.SalespersonMaster
WHERE (NewSalesperson = 'Y')
END

GO