How to get an output variable out of sql stored procedure

hi i have a stored procedure, and this is how i use it currently

    Function GetContact(ByVal tblName) As String
        Dim db As New DataAccess
        Dim cn As New SqlConnection(db.ConnectStr)
        Dim dr As SqlDataReader
        Dim da As New SqlDataAdapter
        Dim result As String
        Try

            Dim cmd As New SqlCommand("obtainNextID", cn)
            cmd.CommandType = CommandType.StoredProcedure
            da = New SqlDataAdapter(cmd)

            'Set The Parameters
            Dim parm As New SqlParameter("@Region", SqlDbType.VarChar)
            parm.Value = _Region
            cmd.Parameters.Add(parm)

            parm = New SqlParameter("@Table", SqlDbType.VarChar)
            parm.Value = tblName
            cmd.Parameters.Add(parm)

            parm = New SqlParameter("@NextID", SqlDbType.VarChar, 50)
            parm.Direction = ParameterDirection.InputOutput
            cmd.Parameters.Add(parm)

            cn.Open()
            dr = cmd.ExecuteReader

            While dr.Read()
                result = dr.GetValue(0)
            End While

        Catch sql As SqlException
            Return sql.ToString
        Catch ex As Exception
            Return ex.ToString
        Finally
            If cn.State = ConnectionState.Open Then cn.Close()
        End Try

        Return result

    End Function


problem is, when i do a trace on the sql, its returning everything fine, but it returns an error  saying error line 1 near obtainNextID, but i know the sql is 100% right
OSLAdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ignacio Soler GarciaSolution Architech & Technical LeadCommented:
I always work in a different way:

First, I don't know if matters but I always open the connection befor assigning it to any object. You don't know what is going to do that object with the connection (as I said, this can be not related with the problem)

Second, I use a different way to take the return parameters:

returnValueParameter = command.Parameters.Add("ReturnValue", SqlDbType.Int)
returnValueParameter.Direction = ParameterDirection.ReturnValue

And then I don't use an execute reader, instead an ExecuteNonQuery

This way the method would be:

    Function GetContact(ByVal tblName) As String
        Dim db As New DataAccess
        Dim cn As New SqlConnection(db.ConnectStr)
        Dim dr As SqlDataReader
        Dim da As New SqlDataAdapter
        Dim returnValueParameter As OleDbParameter
        Dim result As String
        Try

            cn.Open()
            Dim cmd As New SqlCommand("obtainNextID", cn)
            cmd.CommandType = CommandType.StoredProcedure
            da = New SqlDataAdapter(cmd)

            'Set the return
            returnValueParameter = command.Parameters.Add("ReturnValue", SqlDbType.VarChar)
            returnValueParameter.Direction = ParameterDirection.ReturnValue

            'Set The Parameters
            Dim parm As New SqlParameter("@Region", SqlDbType.VarChar)
            parm.Value = _Region
            cmd.Parameters.Add(parm)

            parm = New SqlParameter("@Table", SqlDbType.VarChar)
            parm.Value = tblName
            cmd.Parameters.Add(parm)

            parm = New SqlParameter("@NextID", SqlDbType.VarChar, 50)
            parm.Direction = ParameterDirection.InputOutput
            cmd.Parameters.Add(parm)

            cmd.ExecuteNonQuery()
            result = CType(returnValueParameter.Value, String)

        Catch sql As SqlException
            Return sql.ToString
        Catch ex As Exception
            Return ex.ToString
        Finally
            If cn.State = ConnectionState.Open Then cn.Close()
        End Try

        Return result

    End Function

Ask me more if you need it.

SoMoS
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tusharashahCommented:
Check out this tutorial to see if you're missing something: (your code seems allright!)

http://support.microsoft.com/kb/310070/EN-US/

-tushar
0
natlozCommented:
Please post your procedure.

Are you using a Return value in your procedure? The method you are using would be if you are returning a RESULT SET based on a SELECT statement. If you have a Return Value in the Stored procedure you need to use another method, since you result will not be in a Recordset.

Sample with a Return Value...

'************************
    'ADD Project - frmProject
    '************************
    Public Function AddProject(ByVal fkBranchID As Integer, ByVal varProjectCode As String, ByVal varProjectName As String, ByVal varAddress As String, ByVal varCity As String, ByVal varProvince As String, ByVal varPostalCode As String, ByVal varPhone As String, ByVal varFax As String, ByVal varContact As String, ByVal bolClosed As Boolean) As Integer
        'Variables and objects
        Dim cDB As clsDBConn = New clsDBConn 'Connection Class
        Dim strConn As String = cDB.getStrConn 'Get connection string for database
        Dim oConn As New SqlConnection 'SQL connection object
        Dim intReturnID As Integer 'Return ID from Stored Procedures
        Dim oComm As SqlCommand 'SQL Command object

        Try
            'Pass connection string to SqlConnection
            oConn.ConnectionString = strConn

            'SQL Database statements
            oComm = New SqlCommand
            oComm.CommandType = CommandType.StoredProcedure
            oComm.CommandText = "spAddProject"
            oComm.Connection = oConn

            'Send in parameters
            oComm.Parameters.Add(New SqlParameter("@fkBranchID", SqlDbType.Int)).Value = fkBranchID
            oComm.Parameters.Add(New SqlParameter("@varProjectCode", SqlDbType.VarChar)).Value = varProjectCode
            oComm.Parameters.Add(New SqlParameter("@varProjectName", SqlDbType.VarChar)).Value = varProjectName
            oComm.Parameters.Add(New SqlParameter("@varAddress", SqlDbType.VarChar)).Value = varAddress
            oComm.Parameters.Add(New SqlParameter("@varCity", SqlDbType.VarChar)).Value = varCity
            oComm.Parameters.Add(New SqlParameter("@varProvince", SqlDbType.VarChar)).Value = varProvince
            oComm.Parameters.Add(New SqlParameter("@varPostalCode", SqlDbType.VarChar)).Value = varPostalCode
            oComm.Parameters.Add(New SqlParameter("@varPhone", SqlDbType.VarChar)).Value = varPhone
            oComm.Parameters.Add(New SqlParameter("@varFax", SqlDbType.VarChar)).Value = varFax
            oComm.Parameters.Add(New SqlParameter("@varContact", SqlDbType.VarChar)).Value = varContact
            oComm.Parameters.Add(New SqlParameter("@bolClosed", SqlDbType.Bit)).Value = bolClosed

            'Return value from stored procedure
            Dim sParam As SqlParameter
            sParam = New SqlParameter
            sParam.ParameterName = "@ReturnID"
            sParam.SqlDbType = SqlDbType.Int
            sParam.Direction = ParameterDirection.Output
            oComm.Parameters.Add(sParam)

            'Open connection and execute oComm
            oConn.Open() 'Open connection
            oComm.ExecuteNonQuery() 'Execute stored procedure
            intReturnID = oComm.Parameters("@ReturnID").Value 'Get Return value <----------------------- HERE
            oConn.Close() 'Close connection

        Catch ex As Exception
            MsgBox(ex.ToString)
            MsgBox("Error connecting to SQL Server")
        End Try

        'Return the ID added or ERROR Code
        Return intReturnID
    End Function
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

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.