如何使用存储过程中的输出将值返回给VB程序 [英] How to use output in stored procedure to return value to VB programme
问题描述
朋友您好,我想使用存储过程中的OUTPUT关键字将值
返回给前端控件,但无效。以下是代码。
存储过程代码
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
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代码
Dim prmt(1)作为OleDbParameter
尝试
使用cmd
con.ConnectionString = renConString
con.Open()
.Connection = net_55
.CommandText =prcSumSales
.CommandType = CommandType.StoredProcedure
prmt(0)=新OleDb.OleDbParameter(SalesDate,OleDb.OleDbType.VarChar,Integer.MaxValue)
prmt(1)=新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)
昏暗的结果双倍
.ExecuteNonQuery()
lblTotalSales.Visible = True
result = cmd.Parameters(@ SubTotal)。Value
lblTotalSales.Text =结果
结束
Catch ex As Exception
MsgBox(ex.Message,MsgBoxStyle.Critical,错误)
结束尝试
请这是我收到的错误消息
过程或函数prcSumSales指定了太多参数。
谢谢。
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.
推荐答案
您的代码中没有任何错误但是你的storedprocedure没有指定两个参数。
你的程序应该如下。
Hi there is nothing wrong in your code but your storedprocedure has not specified two parameter.
Your procedure should be like following.
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
这篇关于如何使用存储过程中的输出将值返回给VB程序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!