Hi,

I have a stored procedure and a class in vb.net 2.0. How can I return more than one value to the function?

 

 

Hi, friend:

  To return more than one value in vb.net, you should use a parameter to carry the extra value returned. Just set byref instead of byval before parameters.

Regards

Or you could return a List object or an Array with all the values in that you want to return.  Or concatenate the different values into a single string  and then split the string once returned.  There are many many ways to implement this, it all depends on what it is you are trying to achieve and how it fits with the rest of the code (in terms of scalability and maintainability).

Thanks for the replies.

Here is my function in my class and i want to return both the values of Active and Role to my code .

Public Function GetUsers(ByVal UserName As String) As Integer

myConnection.Close()

Dim myCommand As New SqlCommand("GetUsersByUsername", myConnection)

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.AddWithValue(

"@USERNAME", UserName)

myConnection.Open()

Dim reader As SqlDataReader = myCommand.ExecuteReader()

Do While reader.Read

Role = reader.Item(

"roleid")

Active = reader.Item(

"Active")

Loop

Return Role

reader.Close()

myConnection.Close()

End Function

Thanks for the replies.

Here is my function in my class and i want to return both the values of Active and Role to my code .

Public Function GetUsers(ByVal UserName As String) As Integer

myConnection.Close()

Dim myCommand As New SqlCommand("GetUsersByUsername", myConnection)

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.AddWithValue(

"@USERNAME", UserName)

myConnection.Open()

Dim reader As SqlDataReader = myCommand.ExecuteReader()

Do While reader.Read

Role = reader.Item(

"roleid")

Active = reader.Item(

"Active")

Loop

Return Role

reader.Close()

myConnection.Close()

End Function

Hi:

  Where is the definition of Role and Active? Are they both global variables?

Regards

Public

Class UsersClass

 

Public Shared Role As Integer

Public Shared Active As Integer

Public Function GetUsers(ByVal UserName As String) As Integer

myConnection.Close()

Dim myCommand As New SqlCommand("GetUsersByUsername", myConnection)

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.AddWithValue(

"@USERNAME", UserName)

myConnection.Open()

Dim reader As SqlDataReader = myCommand.ExecuteReader()

Do While reader.Read

Role = reader.Item(

"roleid")

Active = reader.Item(

"Active")

Loop

 

Return Role

reader.Close()

myConnection.Close()

End Function

Private ReadOnly Property ConnectionString() As String

Get

Return ConfigurationManager.ConnectionStrings("ChildbirthClassesConnectionString").ConnectionString

End Get

End Property

End

Class

Hi:

  Then, just as I mentioned,

Public Function GetUsers(ByVal UserName As String, ByRef result_active As String) As Integer

and in the end of the function:

result_active=Active

 

Regards

 

Public Function GetUsers(ByVal UserName As String, ByRef result_active As Integer, ByRef result_role As Integer) As Integer

myConnection.Close()

Dim myCommand As New SqlCommand("GetUsersByUsername", myConnection)

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.AddWithValue(

"@USERNAME", UserName)

'myCommand.Parameters.AddWithValue("@password", Password)

myConnection.Open()

Dim reader As SqlDataReader = myCommand.ExecuteReader()

Do While reader.Read

Role = reader.Item(

"roleid")

Active = reader.Item(

"Active")

Loop

'Return Role

reader.Close()

myConnection.Close()

result_role = Role

result_active = Active

 

I have to get the results of both result_role  and result_active in my code (web form). How do i get the value of both variables?

Hi:

Your Function:

Public Function GetUsers(ByVal UserName As String, ByRef result_active As Integer, ByRef result_role As Integer) As Integer

.....

Role = reader.Item(

"roleid")

result_role =Role

Active = reader.Item(

"Active")

result_active =Active

.....

End Function

Get results:

Dim  rRole As Integer

 

Dim rActive As Integer 
Dim UserName As String

GetUsers(UserName,rActive, rRole)

Regards

Hi Allen

i cannot get this to work.  i am using wcf and a SqlDataReader, the function returning a list(of and i added a byref to return another listof, but it returns nothing.  when i step thru the function on the server side of the service, the data is being populated.

any ideas would be appreciated

This is kinda on the same vein i am using a web service that I created that recieves a zip Code as String, ErrorCheck as Boolean and ErrorMessage as String i set it up like this

    Public Function ZipValidate(ByVal Zip As String, ByVal ErrorCheck As Boolean, ByVal ErrorMessage As String) As String

        Dim City As String
        Dim State As String

        Select Case Zip
            Case "20193"
                City = "Reston"
                State = "VA"
            Case "22219"
                City = "Arlington"
                State = "VA"
            Case "21201"
                City = "Baltimore"
                State = "MD"
            Case "21401"
                City = "Annapolis"
                State = "MD"
            Case "20810"
                City = "Bethesda"
                State = "MD"
            Case "20001"
                City = " Washington"
                State = "DC"
            Case "15235"
                City = "Pittsburg"
                State = "PA"
            Case "191192"
                City = "Philadelphia"
                State = "PA"
            Case "10165"
                City = "New York"
                State = "NY"
            Case "14201"
                City = "Buffalo"
                State = "NY"
            Case Else
                City = ""
                State = ""
                ErrorCheck = False
                ErrorMessage = "Zip Code has not been found"
        End Select

        Return City
        Return State

    End Function

I need to return City and State, ErrorCheck and ErrorMessage to the wcf service that calls it but I am unsure how to do this please help me figure out how to fix my problem