Solved

Error calling MS SQL Server stored procedure in VB.NET. A bug in VB.NET ?

Posted on 2004-10-19
3
430 Views
Last Modified: 2010-04-23
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;User 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(sConnectionString)
        Dim command As SqlCommand = New SqlCommand("spDecimal", connection)

        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add("@in_arg", SqlDbType.Decimal).Value = in_arg
        command.Parameters.Add("@out_arg", SqlDbType.Decimal).Value = out_arg
        command.Parameters("@out_arg").Direction = 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



0
Comment
Question by:GregBo
  • 2
3 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12348254
Hi 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("spDecimal", SqlConnection1)
        Dim ss As New SqlClient.SqlParameter("@out_arg", SqlDbType.Decimal, 10, ParameterDirection.Output, True, 10, 5, "out_arg", DataRowVersion.Current, 0)

        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add("@in_arg", SqlDbType.Decimal).Value = in_arg
        command.Parameters.Add(ss).Value = out_arg
        command.Parameters("@out_arg").Direction = 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
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 200 total points
ID: 12348330
or this is easier

   Public Sub spDecimal(ByVal in_arg As Decimal, _
                         ByRef out_arg As Decimal)
        Dim connection As SqlConnection = New SqlConnection(sConnectionString)
        Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("spDecimal", Connection)

        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add("@in_arg", SqlDbType.Decimal).Value = in_arg
        command.Parameters.Add("@out_arg", SqlDbType.Decimal).Value = out_arg
        command.Parameters("@out_arg").Direction = ParameterDirection.Output
        command.Parameters("@out_arg").Precision = 10
        command.Parameters("@out_arg").Scale = 3
        Try
            command.Connection.Open()
            command.ExecuteNonQuery()
        Finally
            command.Connection.Close()
        End Try

        out_arg = CType(command.Parameters("@out_arg").Value, Decimal)
    End Sub
0
 

Author Comment

by:GregBo
ID: 12355341
Thank you very much, Ronald

Regards,


Gregory
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now