VB.net calls a simple T-SQL execute scalar but doesn't get result

Question

I have a simple TSQL scalar function called TestScalar2 see below..

CREATE FUNCTION TestScalar2
(
@GVStaffID INT = 1
)
RETURNS INT
AS
BEGIN
    DECLARE @Val Int
    set @Val = 10

    IF @GVStaffID = 1 
        BEGIN
            set @Val = 20
        END
    ELSE
        BEGIN
             SET @Val = 30
        END
    RETURN @VAL

END
GO

But my vb.net code doesnt get a result, it calls it, it passes in the parameter, but I get 0 back!.

vb code as follows...

cmd1.CommandType = System.Data.CommandType.StoredProcedure
    cmd1.CommandText = "TestScalar2"
    cmd1.Parameters.AddWithValue("@GVStaffID", GVStaffID)
    cmd1.Connection = conn
    conn.Open()
    returnValue = Convert.ToInt32(cmd1.ExecuteScalar())
    MsgBox(GVStaffID)

    conn.Close()
    MsgBox(returnValue)

Any help greatly appreciated as I am at my wits end!

Possible duplicate of [How can I call a sqlserver function from VB.net(or C#) ? Is there some syntax like stored procedure?](https://stackoverflow.com/questions/1300052/how-can-i-call-a-sqlserver-function-from-vb-netor-c-is-there-some-syntax-li) — Andrew Morton, Jul 14 '18 at 10:52
If you have read the documentation for `ExecuteScalar` then you know that it returns the value from the first column of the first row of the result set of a query. A return value is NOT part of the result set. You MUST have a `SELECT` statement to produce a result set. A return value is something completely separate and is retrieved via a dedicated parameter with a `Direction` of `ReturnValue`. The question is why have you NOT read the documentation? VS has a Help menu for a reason. — jmcilhinney, Jul 14 '18 at 11:04

Ctznkane525 · Answer 1 · 2018-07-14T10:51:53.030

0

You aren't calling a stored procedure. You are calling a function. Make this change:

cmd1.CommandType = System.Data.CommandType.Text
cmd1.CommandText = "Select TestScalar2(@GVStaffID)"

edited Jul 14 '18 at 10:51

answered Jul 14 '18 at 10:44

Ctznkane525

7,297
3
16
40

Tried it, but get an error at ExecuteScalar 'TestScalar2' is not a recognized built-in function name. Thanks anyways – Gerry Mac Jul 14 '18 at 10:50
i forgot the parameter – Ctznkane525 Jul 14 '18 at 10:52
I used execute scalar before where I added a SELECT @@Identity, when I was trying to get the next primary key, but it was with a long INSERT INTO statement, not a scalar Function! – Gerry Mac Jul 14 '18 at 11:06

score 0 · Answer 2 · answered Jul 14 '18 at 11:41

So I finally got it to work... I created a Stored procedure added an output parameter and used the execute non query cmd!

cmd1.CommandType = System.Data.CommandType.StoredProcedure
cmd1.CommandText = "TestScalar"
cmd1.Parameters.AddWithValue("@GVStaffID", GVStaffID)
cmd1.Parameters.Add("@Val", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
cmd1.Connection = conn
conn.Open()
cmd1.ExecuteNonQuery()
MsgBox(GVStaffID)
returnValue = cmd1.Parameters("@Val").Value
conn.Close()
MsgBox(returnValue)

And voila it worked, but thats 2 hours I'll never get back!

score 0 · Answer 3 · answered Jul 14 '18 at 12:59

0

use

select ResultValue=dbo.TestScalar2(@p1)

It is necessary to use the schema name (dbo.)

It is preferable to assign the value to a variable

answered Jul 14 '18 at 12:59

Rami Bancosly

434
2
7

VB.net calls a simple T-SQL execute scalar but doesn't get result

3 Answers3