GregBo
asked on
Error calling MS SQL Server stored procedure in VB.NET. A bug in VB.NET ?
I often call SQL Server stored procedures from VB.NET programs.
The error occurs for the following stored procedure:
create procedure spDecimal(@in_arg decimal(10, 5),
@out_arg decimal(10, 5) output)
as
begin
set @out_arg = @in_arg
end
go
In fact, it does nothing more than giving back its first parameter.
The following VB.NET console application tries to call this stored procedure
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----
' Begin of code
Option Strict On
Option Explicit On
Imports System.Data.SqlClient
Module Module1
Private sConnectionString As String = "Data Source=(local);initial catalog=wrapper_test_db;Us er ID=sa;Password=sql;"
Sub Main()
Dim de1, de2 As Decimal
de1 = 9.234D
spDecimal(de1, de2)
Console.WriteLine(de2)
End Sub
Public Sub spDecimal(ByVal in_arg As Decimal, _
ByRef out_arg As Decimal)
Dim connection As SqlConnection = New SqlConnection(sConnectionS tring)
Dim command As SqlCommand = New SqlCommand("spDecimal", connection)
command.CommandType = CommandType.StoredProcedur e
command.Parameters.Add("@i n_arg", SqlDbType.Decimal).Value = in_arg
command.Parameters.Add("@o ut_arg", SqlDbType.Decimal).Value = out_arg
command.Parameters("@out_a rg").Direc tion = ParameterDirection.Output
Try
command.Connection.Open()
command.ExecuteNonQuery()
Finally
command.Connection.Close()
End Try
out_arg = CType(command.Parameters(" @out_arg") .Value, Decimal)
End Sub
End Module
' End of code
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Surprisingly, it prints 9 instead of expected value 9.234. The value of
output parameter is rounded.
This ONLY happens with the stored procedure with the parameter of type Decimal.
If we change the type of stored procedure to any other type (float, int, varchar etc.) and
change the type of corresponding parameters in VB.NET code, it works perfectly.
Does anybody have any idea about what happens with the decimal output parameter ?
Is there probably a bug in VB.NET ?
Gregory
The error occurs for the following stored procedure:
create procedure spDecimal(@in_arg decimal(10, 5),
@out_arg decimal(10, 5) output)
as
begin
set @out_arg = @in_arg
end
go
In fact, it does nothing more than giving back its first parameter.
The following VB.NET console application tries to call this stored procedure
'-------------------------
' Begin of code
Option Strict On
Option Explicit On
Imports System.Data.SqlClient
Module Module1
Private sConnectionString As String = "Data Source=(local);initial catalog=wrapper_test_db;Us
Sub Main()
Dim de1, de2 As Decimal
de1 = 9.234D
spDecimal(de1, de2)
Console.WriteLine(de2)
End Sub
Public Sub spDecimal(ByVal in_arg As Decimal, _
ByRef out_arg As Decimal)
Dim connection As SqlConnection = New SqlConnection(sConnectionS
Dim command As SqlCommand = New SqlCommand("spDecimal", connection)
command.CommandType = CommandType.StoredProcedur
command.Parameters.Add("@i
command.Parameters.Add("@o
command.Parameters("@out_a
Try
command.Connection.Open()
command.ExecuteNonQuery()
Finally
command.Connection.Close()
End Try
out_arg = CType(command.Parameters("
End Sub
End Module
' End of code
'-------------------------
Surprisingly, it prints 9 instead of expected value 9.234. The value of
output parameter is rounded.
This ONLY happens with the stored procedure with the parameter of type Decimal.
If we change the type of stored procedure to any other type (float, int, varchar etc.) and
change the type of corresponding parameters in VB.NET code, it works perfectly.
Does anybody have any idea about what happens with the decimal output parameter ?
Is there probably a bug in VB.NET ?
Gregory
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much, Ronald
Regards,
Gregory
Regards,
Gregory
Yeah, the decimal output parameter does that, to avoid that you need something like this
You have to explicitly tell the output parameter, the total digits and decimal places
Public Sub spDecimal(ByVal in_arg As Decimal, _
ByRef out_arg As Decimal)
Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("spDe
Dim ss As New SqlClient.SqlParameter("@o
command.CommandType = CommandType.StoredProcedur
command.Parameters.Add("@i
command.Parameters.Add(ss)
command.Parameters("@out_a
Try
command.Connection.Open()
command.ExecuteNonQuery()
Finally
command.Connection.Close()
End Try
out_arg = CType(command.Parameters("
End Sub