469,277 Members | 2,085 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,277 developers. It's quick & easy.

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 ?
Nov 23 '05 #1
4 2106
Rob wrote:
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
Nov 23 '05 #2
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" <no@spam.com> wrote in message
news:uJ**************@TK2MSFTNGP09.phx.gbl...
Rob wrote:
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

Nov 23 '05 #3
Rob wrote:
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" <no@spam.com> wrote in message
news:uJ**************@TK2MSFTNGP09.phx.gbl...
Rob wrote:
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



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
Nov 23 '05 #4
Rob
Thanks Chris,
Yep, that works fine...
"Chris" <no@spam.com> wrote in message
news:O2**************@TK2MSFTNGP12.phx.gbl...
Rob wrote:
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" <no@spam.com> wrote in message
news:uJ**************@TK2MSFTNGP09.phx.gbl...
Rob wrote:

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



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

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by mitchell | last post: by
12 posts views Thread by Sanjay | last post: by
2 posts views Thread by jmensch | last post: by
1 post views Thread by VB Programmer | last post: by
1 post views Thread by VB Programmer | last post: by
3 posts views Thread by kevenj | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.