CODEWITHSUNDEEP
sqlvb.netfunctioncalloledb
John DMC
John DMC

Reputation: 95

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

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

Upvotes: 0

Views: 416

Answers (2)

Craig W.
Craig W.

Reputation: 18155

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

Upvotes: 0

John DMC
John DMC

Reputation: 95

Duh!

I left out this line of code:

cmd.CommandType = CommandType.StoredProcedure

Upvotes: 1

Related Questions