vb.net - 使用 oledb 命令 vb.net 从存储过程中检索输出参数

Question

我的存储过程:

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 代码:

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

当我想从存储过程中检索参数时遇到问题..

当我运行该代码时，会出现类似此消息的错误..

Conversion failed when converting the varchar value 'TB13000005' to data type int.

为什么他们说转换为数据类型int失败？

我的代码有什么问题..？

请高手帮帮我..

Answer 1

我相信是您的存储过程中的这一行导致了错误:

return  @kodetrans

存储过程只能返回整数作为 RETURN 的一部分语句，因此该行失败(因为 @kodetrans 是 VARCHAR )。您可以完全删除该行...对于输出参数，您在此处执行的操作:

set @kodeTrans =@KdTrans

在存储过程中很好，应该足够/确定。