I can't return result from my SQL user function to my vb.net program

Question

I'm pulling my hair out!!

I wrote a simple scalar function and now I'm tring to execute it from vb.net and get the result back but it always shows up as zero, even though when I call it in SSMS it returns the value of 1, which is what I expect.

The function looks like this:

Alter FUNCTION [dbo].[CheckParity]
(@test char(1)  )
RETURNS int
AS
BEGIN
declare @result int
IF (SELECT SUM(REVENUEAMOUNT) FROM CommissionDetail WHERE  RevenueType IN('P','S')) =
   (SELECT SUM(SERVICEREVENUE + PRODUCTREVENUE) FROM SalesmanAttainment)
    set @result = 1
else  
    set @result = 0
return @result
END

I grabbed the vb code from the web:

                sql = "dbo.CheckSalesAttainmentParity"
                Dim count As Integer

                cnn = New OleDbConnection(conn.ConnectionString)
                Try
                    cnn.Open()
                    cmd = New OleDbCommand(sql, cnn)
                    cmd.Parameters.Add("test", OleDbType.Integer)
                    cmd.Parameters("test").Direction = ParameterDirection.ReturnValue
                    cmd.ExecuteScalar()
                    count = cmd.Parameters("test").Value
                    cmd.Dispose()
                    cnn.Close()                   
                sql = "dbo.CheckSalesAttainmentParity"
                Dim count As Integer

                cnn = New OleDbConnection(conn.ConnectionString)
                Try
                    cnn.Open()
                    cmd = New OleDbCommand(sql, cnn)
                    cmd.Parameters.Add("test", OleDbType.Integer)
                    cmd.Parameters("test").Direction = ParameterDirection.ReturnValue
                    cmd.ExecuteScalar()
                    count = cmd.Parameters("test").Value
                    cmd.Dispose()
                    cnn.Close()

I'm totally stumped. I'd appreciate all ideas

John DMC · Accepted Answer · 2013-06-14 20:14:32Z

1

Duh!

I left out this line of code:

cmd.CommandType = CommandType.StoredProcedure

answered Jun 14, 2013 at 20:14

John DMC

957 bronze badges

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

3 Comments

khr055 Over a year ago

Please use the edit link on your question to add additional information. The Post Answer button should be used only for complete answers to the question.

Niall C. Over a year ago

If this is the answer to the question, please mark it as accepted so that people will know that it is the answer (click the checkmark on the sidebar to the left). It looks very like an update to the question as Mike said above.

Ry- Over a year ago

@Mike: It answers the question.

Craig W. · Accepted Answer · 2013-06-14 20:49:46Z

0

You're using a SQL Server database so I'm not sure if there was a particular reason you used OleDbConnection instead of SqlConnection, but try this.

    Dim Sql As String = "select Sample.dbo.CheckParity('a')"
    Dim count As Integer
    Dim cnn As SqlConnection
    Dim cmd As SqlCommand

    Try
        cnn = New SqlConnection(conn.ConnectionString)
        cnn.Open()

        cmd = New SqlCommand(Sql, cnn)

        count = CInt(cmd.ExecuteScalar())

        Console.WriteLine(count)
        Console.ReadKey()
    Finally
        cmd.Dispose()
        cnn.Close()
    End Try

answered Jun 14, 2013 at 20:49

Craig W.

18.2k6 gold badges52 silver badges83 bronze badges

Collectives™ on Stack Overflow

I can't return result from my SQL user function to my vb.net program

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related