call sql function

Do I call sql function correctly? It suppose to return 0 or 1. When I ran my sql function, It return 1 . When I ran my VB function, always return 0.


Public Function test(ByVal myval As String)
        Dim result As Boolean = False
        Dim result_num As Integer
        Using sqlcon As New SqlConnection("mycon"))
            Using sqlcmnd As New SqlCommand("mysqlfunction", sqlcon)
                sqlcmnd.CommandType = CommandType.StoredProcedure
                sqlcmnd.Parameters.AddWithValue("@myval", myval)
                sqlcon.Open()
                result_num = sqlcmnd.ExecuteScalar
                sqlcon.Close()
            End Using
        End Using
        Return result
    End Function
VBdotnet2005Asked:
Who is Participating?
 
LIONKINGConnect With a Mentor Commented:
I called it by using select instead. It works for me.
select mydb.dbo.mysqlfuntion(a.mydatafield) as test from mytable.dbo.mytable a where mydatafield = ' myparam'

If that's the case you don't need the reader, you can just use ExecuteScalar() and return the first data that's retreived.
0
 
LIONKINGCommented:
First of all you should specify the data type the function will return.
Second of all, in your actual function you're returning the Boolean variable (result), which isn't altered throughout the function and is initialized with false. Therefore the output will always be 0 (or false)

Public Function test(ByVal myval As String)
        Dim result As Boolean = False
        Dim result_num As Integer
        Using sqlcon As New SqlConnection("mycon"))
            Using sqlcmnd As New SqlCommand("mysqlfunction", sqlcon)
                sqlcmnd.CommandType = CommandType.StoredProcedure
                sqlcmnd.Parameters.AddWithValue("@myval", myval)
                sqlcon.Open()
                result_num = sqlcmnd.ExecuteScalar
                sqlcon.Close()
            End Using
        End Using
        Return result
    End Function
0
 
VBdotnet2005Author Commented:
Public Function test(ByVal myval As String) as boolen
        Dim result As Boolean = False
        Dim result_num As Integer
        Using sqlcon As New SqlConnection("mycon"))
            Using sqlcmnd As New SqlCommand("mysqlfunction", sqlcon)
                sqlcmnd.CommandType = CommandType.StoredProcedure
                sqlcmnd.Parameters.AddWithValue("@myval", myval)
                sqlcon.Open()
                result_num = sqlcmnd.ExecuteScalar   <<<< when I check result_num, it alway return 0
            if result_num = 1 then
              result = True
                sqlcon.Close()
            End Using
        End Using
        Return result
    End Function
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
LIONKINGCommented:
What exactly does this sql function do?
The return value of ExecuteScalar is the first column of the first row of the resultset. (Source).

Should this return a particular number... Remember that when you do this in SQL you specify a SELECT statement, in vb.net you're only executing the function, not returning any data.
0
 
CodeCruiserCommented:
Can you show your SP code?
0
 
VBdotnet2005Author Commented:
it should return 0 or 1
", in vb.net you're only executing the function, not returning any data. "
I need it to return data
0
 
VBdotnet2005Author Commented:
In sql I ran like this

select mysqlfunction(my param)
0
 
VBdotnet2005Author Commented:
How can I call it in vb.net?
0
 
LIONKINGCommented:
Have you tried using a dataReader?
Something like this:

Public Function test(ByVal myval As String) as boolen
        Dim result As Boolean = False
        Dim result_num As Integer
        Using sqlcon As New SqlConnection("mycon"))
            Using sqlcmnd As New SqlCommand("mysqlfunction", sqlcon)
                sqlcmnd.CommandType = CommandType.StoredProcedure
                sqlcmnd.Parameters.AddWithValue("@myval", myval)
                sqlcon.Open()
Dim myReader as SqlDataReader
                myReader = sqlcmnd.ExecuteReader()
if myReader.HasRows then
myReader.Read()
result_num=Integer.Parse(myReader(0))
else
result_num=0
end if

            if result_num = 1 then
              result = True
                sqlcon.Close()
            End Using
        End Using
        Return result
    End Function


That's a global idea... Don't have VS right now and that's done on the fly... Sorry if there are any errors.
Let me know if this works...
0
 
LIONKINGCommented:
P.S. Remember that whatever you're putting in this line (specifically the bolded string):

Using sqlcmnd As New SqlCommand("mysqlfunction", sqlcon)

should be a valid SQL statement... In this case you're saying that "mysqlfunction" is a stored procedure, therefore, it should exist in the db.
0
 
VBdotnet2005Author Commented:
correct
0
 
VBdotnet2005Author Commented:
I tried datareader, same result. No error when calling that SQL function though.
0
 
LIONKINGCommented:
Does it return rows? What's the value of the myReader.HasRows property?
0
 
VBdotnet2005Author Commented:
No row is returned.
0
 
VBdotnet2005Author Commented:
When I ran "select mysqlfunction(myparam) by itself on SQL, it did return correct result, 0 or 1.
0
 
LIONKINGCommented:
Can you show us your code for the "mysqlfunction" stored procedure?
0
 
VBdotnet2005Author Commented:
I will post my solution tomorrow. I got it to work now.
0
 
VBdotnet2005Author Commented:
I called it by using select instead. It works for me.
select mydb.dbo.mysqlfuntion(a.mydatafield) as test from mytable.dbo.mytable a where mydatafield = ' myparam'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.