[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to get an output variable out of sql stored procedure

Posted on 2004-11-12
6
Medium Priority
?
338 Views
Last Modified: 2010-04-24
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
0
Comment
Question by:OSLAdmin
3 Comments
 
LVL 5

Accepted Solution

by:
Ignacio Soler Garcia earned 672 total points
ID: 12563983
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
 
LVL 18

Assisted Solution

by:tusharashah
tusharashah earned 664 total points
ID: 12565260
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
 
LVL 7

Assisted Solution

by:natloz
natloz earned 664 total points
ID: 12568300
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

Featured Post

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!

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

873 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