Retrieving output parameter from stored procedure with oledb command vb.net

Question

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:

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.

Why they said that failed converting to data type int??

What's wrong with my code..?

master help me please..

nkvu · Accepted Answer · 2013-03-30 04:46:43Z

3

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 RETURN statement so the line fails (as @kodetrans is a VARCHAR). You can just remove that line completely...For output parameters, what you have done here:

set @kodeTrans =@KdTrans

In the stored proc is fine and should be sufficient/OK.

answered Mar 30, 2013 at 4:46

nkvu

5,9012 gold badges18 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GangOne Style Over a year ago

thanks a lot nkvu.. my problem solved.. i don't know before "Return" only return integers.. thanks.

Collectives™ on Stack Overflow

Retrieving output parameter from stored procedure with oledb command vb.net

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related