How to use output in stored procedure to return value to VB programme

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello friends, I want to use the OUTPUT key word in stored procedure to return value
to a front end control and is not working. Below are the codes.

STORED PROCEDURE Code

SQL

CREATE procedure prcSumSales
(
      @SubTotal Money OUTPUT
)
As
    Declare @salDate Datetime
    BEGIN
        Set @SubTotal = (Select Sum(SubTotal) From  dbo.DAILY_SALES
        Where SalesDate = @salDate)
        select @SubTotal
    END

VB Codes
Dim prmt(1) As OleDbParameter

Try
With cmd

con.ConnectionString = renConString
con.Open()
.Connection = net_55
.CommandText = "prcSumSales"
.CommandType = CommandType.StoredProcedure

prmt(0) = New OleDb.OleDbParameter("SalesDate", OleDb.OleDbType.VarChar, Integer.MaxValue)
prmt(1) = New OleDb.OleDbParameter("SubTotal", OleDb.OleDbType.Double, Integer.MaxValue)

prmt(0).Direction = ParameterDirection.Input
prmt(1).Direction = ParameterDirection.Output

.Parameters.AddWithValue("@salDate", txtEnterDate.Text.Trim)
.Parameters.AddWithValue("@SubTotal", 0)

Dim result As Double

.ExecuteNonQuery()
lblTotalSales.Visible = True

result = cmd.Parameters("@SubTotal").Value

lblTotalSales.Text = result
End With

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
End Try
Please this is the error message I get
Procedure or function prcSumSales has too many arguments specified.
Thanks.

Posted 10-Dec-13 21:36pm

NanaKwame

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BK 4 code · Answer 1 · 2013-12-10T22:02:00

Solution 1

Hi there is nothing wrong in your code but your storedprocedure has not specified two parameter.

Your procedure should be like following.

SQL

CREATE procedure prcSumSales
(
      @SalesDate datetime,
      @SubTotal Money OUTPUT
)
As
    Declare @salDate Datetime
    BEGIN
        Set @SubTotal = (Select Sum(SubTotal) From  dbo.DAILY_SALES
        Where SalesDate = @salDate)
        select @SubTotal
    END

Posted 10-Dec-13 22:02pm

BK 4 code

Comments

NanaKwame 11-Dec-13 7:20am

hi Bhavesh Kashikar, thanks for your help anyway. It return zero (0) to the front end control.

BK 4 code 12-Dec-13 2:31am

try this SP code

CREATE procedure prcSumSales
(
@SalesDate datetime,
@SubTotal Money OUTPUT
)
As
Declare @salDate Datetime
BEGIN
select @SubTotal= Sum(SubTotal) From dbo.DAILY_SALES
Where SalesDate = @salDate

END

NanaKwame 12-Dec-13 2:57am

thanks for your effort. It still return null. I even execute at the query analyzer like this
exec prcSumSales '05-Nov-2013',0 and it still return the null value.

NanaKwame 12-Dec-13 9:29am

After removing the this Declare @salDate Datetime line from the code every thing is okay now
with procedure.

CREATE procedure prcSum_DSales
(
@SalesDate datetime,
@SubTotal Money OUTPUT
)
As
BEGIN
Set @SubTotal = (Select Sum(SubTotal)
From dbo.DAILY_SALES
Where SalesDate = @SalesDate )
select @SubTotal

END