?
Solved

call sql function

Posted on 2012-08-15
18
Medium Priority
?
482 Views
Last Modified: 2012-08-31
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
0
Comment
Question by:VBdotnet2005
  • 10
  • 7
18 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 38297776
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
 

Author Comment

by:VBdotnet2005
ID: 38297806
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
 
LVL 13

Expert Comment

by:LIONKING
ID: 38297834
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38297857
Can you show your SP code?
0
 

Author Comment

by:VBdotnet2005
ID: 38297860
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
 

Author Comment

by:VBdotnet2005
ID: 38297874
In sql I ran like this

select mysqlfunction(my param)
0
 

Author Comment

by:VBdotnet2005
ID: 38297879
How can I call it in vb.net?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38297912
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
 
LVL 13

Expert Comment

by:LIONKING
ID: 38297923
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
 

Author Comment

by:VBdotnet2005
ID: 38297991
correct
0
 

Author Comment

by:VBdotnet2005
ID: 38298087
I tried datareader, same result. No error when calling that SQL function though.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38298104
Does it return rows? What's the value of the myReader.HasRows property?
0
 

Author Comment

by:VBdotnet2005
ID: 38298116
No row is returned.
0
 

Author Comment

by:VBdotnet2005
ID: 38298127
When I ran "select mysqlfunction(myparam) by itself on SQL, it did return correct result, 0 or 1.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38298146
Can you show us your code for the "mysqlfunction" stored procedure?
0
 

Author Comment

by:VBdotnet2005
ID: 38298892
I will post my solution tomorrow. I got it to work now.
0
 

Author Comment

by:VBdotnet2005
ID: 38302137
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
 
LVL 13

Accepted Solution

by:
LIONKING earned 2000 total points
ID: 38302338
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month15 days, 20 hours left to enroll

850 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