使用 oledb 命令 vb.net 从存储过程中检索输出参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15714923/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieving output parameter from stored procedure with oledb command vb.net
提问by GangOne Style
My stored procedure:
我的存储过程：
PROCEDURE [dbo].[addMasterTransaksi]
@kodeSuplier varchar(10),
@Total money,
@kodeUser varchar(10),
@isLunas varchar (2),
@Dp money,
@kodeTrans varchar(10) output
AS
BEGIN
Declare @KdTrans as varchar(10);
Declare @Kode as int;
Declare @thisYear as varchar(10);
select @thisyear = RIGHT(YEAR(getDate()),2)
SELECT TOP(1) @KdTrans = SUBSTRING(kodeTransaksi,5,6) FROM TblMasterPembelian WHERE YEAR(Tanggal) = YEAR(getDate()) order by kodeTransaksi desc;
--print @KdTrans
IF @KdTrans IS Null
SET @KdTrans = 'TB'+ @thisYear +'000001'
else
begin
select @Kode = convert(int,@KdTrans);
select @Kode = @Kode + 1;
select @KdTrans = convert(int,@Kode);
select @KdTrans = '00000' + @KdTrans;
select @KdTrans = right(@KdTrans,6)
select @KdTrans ='TB' + @thisYear + @KdTrans
end
SET NOCOUNT ON;
--ke Master Pembelian
INSERT INTO TblMasterPembelian(kodeTransaksi,Tanggal,Total,kodeSuplier,kodeUser,isLunas,DP)
VALUES (@KdTrans,getDate(),@Total,@kodeSuplier,@kodeUser,@isLunas,@Dp)
set @kodeTrans =@KdTrans
--print @kodeTrans
return @kodetrans
END
VB.NET code:
VB.NET 代码：
Public Function addMasterPembelianny(ByVal kodesup As String, ByVal total As Long, ByVal kodeUser As String, ByVal isLunas As String, ByVal dp As Long)
Dim kodeTransaksi As String
modKoneksi.bukaKoneksi()
command.Connection = modKoneksi.koneksidb
command.CommandType = CommandType.StoredProcedure
command.CommandText = "addMasterTransaksi"
command.Parameters.Add("@kodeSuplier", OleDbType.VarChar, 10, ParameterDirection.Input).Value = kodesup
command.Parameters.Add("@Total", OleDbType.BigInt, 10, ParameterDirection.Input).Value = total
command.Parameters.Add("@kodeUser", OleDbType.VarChar, 10, ParameterDirection.Input).Value = kodeUser
command.Parameters.Add("@isLunas", OleDbType.VarChar, 2, ParameterDirection.Input).Value = isLunas
command.Parameters.Add("@Dp", OleDbType.BigInt, 10, ParameterDirection.Input).Value = dp
command.Parameters.Add("@kodeTrans", OleDbType.Char, 10)
command.Parameters("@kodeTrans").Direction = ParameterDirection.Output
command.ExecuteReader()
kodeTransaksi = command.Parameters("@kodeTrans").Value
modKoneksi.tutupKoneksi()
Return kodeTransaksi
End Function
I have problem when I want to retrieve parameter from a stored procedure..
当我想从存储过程中检索参数时遇到问题..
When I run that code, there appear an error like this message ..
当我运行该代码时，会出现类似此消息的错误..
Conversion failed when converting the varchar value 'TB13000005' to data type int.
将 varchar 值“TB13000005”转换为数据类型 int 时转换失败。
Why they said that failed converting to data type int??
为什么他们说转换为数据类型 int 失败？
What's wrong with my code..?
我的代码有什么问题..？
master help me please..
请高手帮帮我..
回答by nkvu
I believe it is this line in your stored procedure which is causing the error:
我相信是您的存储过程中的这一行导致了错误：
return @kodetrans
Stored procedures can only return integers as part of the RETURNstatement so the line fails (as @kodetransis a VARCHAR). You can just remove that line completely...For output parameters, what you have done here:
存储过程只能返回整数作为RETURN语句的一部分，因此该行失败（就像@kodetransa 一样VARCHAR）。您可以完全删除该行...对于输出参数，您在此处完成的操作：
set @kodeTrans =@KdTrans
In the stored proc is fine and should be sufficient/OK.
在存储过程中很好，应该足够/确定。
