Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: Output Parameter's to ADO recordset

    Hi guys,
    I know this might be the wrong place to post this, but I thought I would try since all of you are very smart here .
    I have a huge problem with an app that I inherited. It is a VB6 app that used RDO.
    I am trying to convert it to use ADO, but the problem I am running into is we have allot of stored procedures with OUTPUT parameter's, so instead of the procedure having a SELECT to pass back the value, it is passed back via the OUTPUT parameter.
    With RDO this is done easily with just passing back the parameter to a variable.
    I am not sure how to do this with ADO. Please shine some light for me.

    Here is an example:
    RDO code:
    Private rqAddRecord As RDO.rdoQuery
    Private rs As RDO.rdoResultset
    Set rqAddRecord = SQLServerDB.CreateQuery("", MakeSP("sp_UpdCourier", 11))

    With rqAddRecord
    .rdoParameters(0) = "I"
    .rdoParameters(1) = m.nCourierDeliveryID
    .rdoParameters(2) = m.dDeliveryDate
    .rdoParameters(3) = m.nCourierServiceID
    .rdoParameters(4) = m.nCourierID
    .rdoParameters(5) = m.sCourierDepartment
    .rdoParameters(6) = m.dTimeStart
    .rdoParameters(7) = m.dTimeComplete
    .rdoParameters(8) = g.sDatabaseUserName
    .rdoParameters(9) = m.dInvoiceDate
    .rdoParameters(10) = m.sInvoiceNumber

    .Execute

    m.nCourierDeliveryID = .rdoParameters(1)
    End With

    Stored Procedure:


    CREATE PROCEDURE sp_UpdCourier
    (
    @ActionCode char(1), -- (I)nsert, (U)pdate, (D)elete
    @CourierDeliveryID int OUTPUT,
    @DeliveryDate datetime,
    @CourierServiceID tinyint,
    @CourierID tinyint,
    @CourierDepartment char(5),
    @TimeStart datetime,
    @TimeComplete datetime,
    @ChangedUserID char(8),
    @InvoiceDate smalldatetime,
    @InvoiceNumber char(15)
    )
    AS

    /************************************************** *********************
    * Name: sp_UpdCourier
    * Author: Markus Waite
    * Date: 03/02/04
    *-----------------------------------------------------------------------
    * Desc: tblCourier Maintenance
    *
    *-----------------------------------------------------------------------
    * $Revision: 4 $
    *
    ************************************************** *********************/

    SET NOCOUNT ON

    DECLARE @ExistingDeliveryDate datetime,
    @NbrDays int

    IF @ActionCode IN ('I','U')
    AND @CourierDepartment = ''
    BEGIN
    RAISERROR (50075, 16, 1)
    RETURN
    END

    -- Insert
    IF @ActionCode = 'I'
    BEGIN
    -- Validate Existance
    IF EXISTS (
    SELECT *
    FROM tblCourier
    WHERE DeliveryDate = @DeliveryDate
    AND CourierServiceID = @CourierServiceID
    AND CourierID = @CourierID
    AND CourierDepartment = @CourierDepartment
    AND TimeStart = @TimeStart
    AND TimeComplete = @TimeComplete )
    BEGIN
    RAISERROR (50002, 16, 1)
    RETURN
    END

    /*
    ** Verify if entering another shift that that Times do not overlap
    */
    IF EXISTS (
    SELECT *
    FROM tblCourier
    WHERE DeliveryDate = @DeliveryDate
    AND CourierServiceID = @CourierServiceID
    AND CourierID = @CourierID
    AND CourierDepartment = @CourierDepartment
    AND NOT ( (@TimeStart < TimeStart
    AND @TimeComplete < TimeStart)
    OR (@TimeStart > TimeComplete
    AND @TimeComplete > TimeComplete) ) )
    BEGIN
    RAISERROR (50044, 16, 1)
    RETURN
    END

    BEGIN TRANSACTION

    SELECT @CourierDeliveryID = ISNULL(MAX(CourierDeliveryID), 0) + 1
    FROM tblCourier (HOLDLOCK)

    INSERT tblCourier
    (
    CourierDeliveryID,
    DeliveryDate,
    CourierServiceID,
    CourierID,
    CourierDepartment,
    TimeStart,
    TimeComplete,
    ChangedUserID,
    ChangedDate,
    InvoiceDate,
    InvoiceNumber
    )
    VALUES
    (
    @CourierDeliveryID,
    @DeliveryDate,
    @CourierServiceID,
    @CourierID,
    @CourierDepartment,
    @TimeStart,
    @TimeComplete,
    @ChangedUserID,
    GETDATE(),
    @InvoiceDate,
    @InvoiceNumber
    )

    IF @@ERROR <> 0
    BEGIN
    ROLLBACK TRANSACTION
    RAISERROR ('Failed inserting Courier values', 16, 1)
    RETURN
    END

    COMMIT TRANSACTION

    -- SELECT @CourierDeliveryID

    RETURN
    END

    -- Update
    IF @ActionCode = 'U'
    BEGIN
    /*
    ** Verify if entering another shift that that Times do not overlap
    */
    IF EXISTS (
    SELECT *
    FROM tblCourier
    WHERE DeliveryDate = @DeliveryDate
    AND CourierServiceID = @CourierServiceID
    AND CourierID = @CourierID
    AND CourierDepartment = @CourierDepartment
    AND NOT ( (@TimeStart < TimeStart
    AND @TimeComplete < TimeStart)
    OR (@TimeStart > TimeComplete
    AND @TimeComplete > TimeComplete) )
    AND CourierDeliveryID != @CourierDeliveryID )
    BEGIN
    RAISERROR (50044, 16, 1)
    RETURN
    END

    IF EXISTS (
    SELECT *
    FROM tblCourierDetail d
    WHERE CourierDeliveryID = @CourierDeliveryID
    AND CONVERT(CHAR,DeliveryTime,14) NOT BETWEEN CONVERT(CHAR,@TimeStart,14) AND CONVERT(CHAR,@TimeComplete,14) )
    BEGIN
    RAISERROR (50059, 16, 1)
    RETURN
    END

    SELECT @ExistingDeliveryDate = DeliveryDate
    FROM tblCourier
    WHERE CourierDeliveryID = @CourierDeliveryID

    IF @@ROWCOUNT <> 1
    BEGIN
    RAISERROR ('Could not locate current delivery record', 16, 1)
    RETURN
    END

    BEGIN TRANSACTION

    UPDATE tblCourier
    SET DeliveryDate = @DeliveryDate,
    CourierServiceID = @CourierServiceID,
    CourierID = @CourierID,
    CourierDepartment = @CourierDepartment,
    TimeStart = @TimeStart,
    TimeComplete = @TimeComplete,
    ChangedUserID = @ChangedUserID,
    ChangedDate = GETDATE(),
    InvoiceDate = @InvoiceDate,
    InvoiceNumber = @InvoiceNumber
    WHERE CourierDeliveryID = @CourierDeliveryID

    IF @@ERROR <> 0
    BEGIN
    ROLLBACK TRANSACTION
    RAISERROR ('Failed updating Courier values', 16, 1)
    RETURN
    END

    -- If date changed, then update the Delivery Times
    IF CONVERT(CHAR, @DeliveryDate, 101) <> CONVERT(CHAR, @ExistingDeliveryDate, 101)
    BEGIN
    SELECT @NbrDays = DATEDIFF(dd, @ExistingDeliveryDate, @DeliveryDate)

    UPDATE tblCourierDetail
    SET DeliveryTime = DATEADD(dd, @NbrDays, DeliveryTime)
    WHERE CourierDeliveryID = @CourierDeliveryID

    IF @@ERROR <> 0
    BEGIN
    ROLLBACK TRANSACTION
    RAISERROR ('Failed updating Courier Detail values', 16, 1)
    RETURN
    END
    END

    COMMIT TRANSACTION

    -- SELECT @CourierDeliveryID

    RETURN
    END

    -- Delete
    IF @ActionCode = 'D'
    BEGIN
    BEGIN TRANSACTION

    DELETE tblCourierDetail
    WHERE CourierDeliveryID = @CourierDeliveryID

    IF @@ERROR <> 0
    BEGIN
    ROLLBACK TRANSACTION
    RAISERROR ('Failed deleting Courier Detail values', 16, 1)
    RETURN
    END

    DELETE tblCourier
    WHERE CourierDeliveryID = @CourierDeliveryID

    IF @@ERROR <> 0
    BEGIN
    ROLLBACK TRANSACTION
    RAISERROR ('Failed deleting Courier values', 16, 1)
    RETURN
    END

    COMMIT TRANSACTION

    -- SELECT @CourierDeliveryID

    RETURN
    END

    -- else

    RAISERROR ('Invalid Action Code passed to sp_UpdCourier', 16, 1)
    RETURN
    GO

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's a snippet that may help:

    Code:
        .Parameters.Append .CreateParameter("@RetResNum", adVarChar, adParamOutput, 15)
    
        .Execute
    
        strMsg = .Parameters("@RetResNum")
    Paul

  3. #3
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Where did you get this? What is the .Parameter part of? And what is .Execute? What is it executing?
    Thanks.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It's part of a procedure I wrote. The .Parameter line is the ADO equivalent of your

    .rdoParameters(10) = ...

    In particular that one is for the output parameter. The .Execute does the same thing yours does; executes the SP.
    Paul

  5. #5
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    I got it to work that way. Thanks so much.

Posting Permissions

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