Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    81

    Red face Unanswered: Stored Procedure - Returning Data using OUTPUT Parameters

    OK, have read the BOL about stored procedures till my eyes started to shrivel up and look like little prunes!

    It's just not making any freakin sense!!!

    I have some code...

    Code:
    Public Function ConvertToJob()
    
        Dim Lot As Integer, Street As String, _
        Suburb As String, MapBook As String, MapNumber As Integer, _
        MapReference As String, Price As Currency, BuilderID As Integer, _
        QuoteID As Integer
    
        Dim Conn1 As ADODB.Connection
        Dim Cmd1 As ADODB.Command
        Dim Cmd2 As ADODB.Command
        Dim Cmd3 As ADODB.Command
        Dim Cmd4 As ADODB.Command
        Dim Cmd5 As ADODB.Command
        Dim strConn As String
        Dim intNewJobNum As Integer
            
        Set Cmd1 = New ADODB.Command ' Update tblJobDetails
        Set Cmd2 = New ADODB.Command ' Update tblMYOBInvoiceNumber
        Set Cmd3 = New ADODB.Command ' Update tblFixDateChanges
        Set Cmd4 = New ADODB.Command ' Update tblkOptions -- Last Job Number
        Set Cmd5 = New ADODB.Command ' Get Last Job Number
        
        ' Establish connection.
        Set Conn1 = New ADODB.Connection
        strConn = "Driver={SQL Server};" & _
                  "Server=Blah;" & _
                  "Database=BlahSQL;" & _
                  "UID=Blah;PWD=password"
        Conn1.Open ConnectionString:=strConn
        
        ' Get the values from the form
        Lot = Me.txtLot
        Street = Me.cboStreet
        Suburb = Me.cboSuburb
        MapBook = Me.txtMapBook
        MapNumber = Me.txtMapNumber
        MapReference = Me.txtMapReference
        Price = Me.txtPrice
        BuilderID = Me.cboBuilderID
        QuoteID = Me.txtQuoteID
        
        '------------------------------------------------------------------------------------------------------------
        
        ' Open command object -- used for getting last job number
        Set Cmd5 = New ADODB.Command
        Cmd5.ActiveConnection = Conn1
        Cmd5.CommandText = "spLastJobNum"
        Cmd5.CommandType = adCmdStoredProc
        
        ' Open command object -- used for updating the Job Details table
        Set Cmd1 = New ADODB.Command
        Cmd1.ActiveConnection = Conn1
        Cmd1.CommandText = "spConvertToJobJobDetails"
        Cmd1.CommandType = adCmdStoredProc
        
        ' Open second command object -- used for updating the MYOB Invoice Number table
        Set Cmd2 = New ADODB.Command
        Cmd2.ActiveConnection = Conn1
        Cmd2.CommandText = "spConvertToJobMYOBInvoiceNumber"
        Cmd2.CommandType = adCmdStoredProc
        
        ' Open third command object -- used for updating the Fix Date Changes table
        Set Cmd3 = New ADODB.Command
        Cmd3.ActiveConnection = Conn1
        Cmd3.CommandText = "spConvertToJobFixDateChanges"
        Cmd3.CommandType = adCmdStoredProc
        
        ' Open fourth command object -- used for updating the options table
        Set Cmd4 = New ADODB.Command
        Cmd4.ActiveConnection = Conn1
        Cmd4.CommandText = "spUpdateOptions"
        Cmd4.CommandType = adCmdStoredProc
                
        '------------------------------------------------------------------------------------------------------------
                
        ' Run Stored Procedure to get last job number
        Cmd5.Parameters("@LastJobNumber") = "@LastJobNum OUTPUT"
        Cmd5.Execute
        intNewJobNum = Cmd5.Parameters("@LastJobNumber = @LastJobNum OUTPUT")
        intNewJobNum = intNewJobNum + 1
        
        ' Run Stored Procedure to update job values
        Cmd1.Parameters("@Lot") = Lot
        Cmd1.Parameters("@Street") = Street
        Cmd1.Parameters("@Suburb") = Suburb
        Cmd1.Parameters("@MapBook") = MapBook
        Cmd1.Parameters("@MapNumber") = MapNumber
        Cmd1.Parameters("@MapReference") = MapReference
        Cmd1.Parameters("@Price") = Price
        Cmd1.Parameters("@BuilderID") = BuilderID
        Cmd1.Parameters("@QuoteID") = QuoteID
        Cmd1.Execute
        
        ' Run Stored Procedure to update MYOB Invoice Number
        Cmd2.Parameters("@MYOBInvoiceNumber_2") = 0
        Cmd2.Parameters("@JobID_3") = intNewJobNum
        Cmd2.Execute
        
        ' Run Stored Procedure to update Fix Date Changes
        Cmd3.Parameters("@JobID_5") = intNewJobNum
        Cmd3.Execute
        
        ' Run Stored Procedure to update last job number in options table
        Cmd4.Parameters("@OptionID") = 1
        Cmd4.Parameters("@LastJobNumber") = intNewJobNum
        Cmd4.Execute
        
        ' Display message box signalling successful entry of job
        MsgBox "Job conversion was successful.", vbInformation + vbOKOnly, "Convert Job"
            
        ' Terminate the connection
        Conn1.Close
        Set Conn1 = Nothing
        
    End Function
    OK, on the line: Cmd5.Parameters("@LastJobNumber") = "@LastJobNum OUTPUT"

    It causes an error:

    "Run-time error '3421':
    Application uses a value of the wrong type for the current operation."

    The Stored Procedure looks like this:

    Code:
    CREATE PROCEDURE [spLastJobNum]
    (
    @LastJobNumber int OUTPUT
    )
    AS SELECT [LastJobNumber] = @LastJobNumber
    FROM [BlahSQL].[dbo].[tblkOptions]
    GO
    So As you can probably see from my code, I'm trying to get the last job number out of a table and then use that in the rest of my code. Incidentally, as I'm fairly new at this, feel free to pick on my code and offer any better ways I can do this. (It's using an access ADP for a frontend).

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I don't think I've used output in ADO... can't remember. Anyhoo - does this sproc work in Query Analyser? It doesn't look to me like it does what you think.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    I'm pretty sure that that's not the way to create output parameters.
    I can't get my ADODB help to work, but I think it's like this:

    Cmd5.Parameters("@LastJobNumber").Direction = adParamOutput

    After the Cmd5.Execute,
    intNewJobNum = Cmd5.Parameters("@LastJobNumber")
    should give you the value of the parameter.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ivon
    I'm pretty sure that that's not the way to create output parameters.
    I can't get my ADODB help to work, but I think it's like this:

    Cmd5.Parameters("@LastJobNumber").Direction = adParamOutput

    After the Cmd5.Execute,
    intNewJobNum = Cmd5.Parameters("@LastJobNumber")
    should give you the value of the parameter.
    Yeah, that sounds about right.

    You do need to reread about output params though - your sproc is currently no different from:

    Code:
     
    CREATE PROCEDURE [spLastJobNum]
    (
    @LastJobNumber int
    )
    AS SELECT @LastJobNumber AS  [LastJobNumber]
    FROM [BlahSQL].[dbo].[tblkOptions]
    GO
    I.e. returns a result set of the @LastJobNumber value that you sent as in input arg, aliased as LastJobNumber, * as many rows as there are records in tblkOptions.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2003
    Posts
    81
    Code:
    ' Run Stored Procedure to get last job number
    Cmd5.Parameters("@LastJobNumber").Direction = adParamOutput
    Cmd5.Execute
    intNewJobNum = Cmd5.Parameters("@LastJobNumber")
    intNewJobNum = intNewJobNum + 1
    Doesn't seem to be working. It executes the first two lines, but when it gets to the intNewJobNum = Cmd5.Parameters("@LastJobNumber") line, it chucks up an error which says "Invalid use of null". Doing the debug thing, moving over the Cmd5.Parameters("@LastJobNumber") is equal to null.

    So it doesn't seem to be getting the value...

    Any clues...

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    The reason is that your sproc is wrong. The code may or may not be correct however you cannot know until you fix your sproc. The error is exactly what I would expect - it is not outputting the @LastJobNumber variable with a value. Try testing with Query Analyser until you get it right.

    From BOL:

    Code:
      
    CREATE PROCEDURE get_sales_for_title
    @title varchar(80),   -- This is the input parameter.
    @ytd_sales int OUTPUT -- This is the output parameter.
    AS  
    
    -- Get the sales for the specified title and 
    -- assign it to the output parameter.
    SELECT @ytd_sales = ytd_sales
    FROM titles
    WHERE title = @title
    
    RETURN
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •