Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Posts
    150

    Question Unanswered: Stored Procedure Parameter

    Hi,

    I have an Access front end/MSDE2000 backend system. There is a form that has a subform which is based on the results of a stored procedure. Both my subform and mainform contain intOrderID ( which is the field i want to link both of them with ).

    I would like to have the subform's data updated when changing records in the mainform. In Access, this was simple ( link child and master fields ), but not with MSDE2000 as a back end ( ADO connection thing...)

    I think i have to create something to filter the results of the stored procedure, but i dont like the fact the the entire dataset needs to be transmitted over the network ( most of the time, users will only be creating new records, or reviewing recently created ones )

    I'm really not sure as to how to accomplish this... Any ideas?

    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Can't you requery the database for your subform?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2003
    Posts
    150
    requery in the Afterupdate event of my fields?
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use the On Current even of the main form.

    My Access is a little rusty. Why don't you post this on the Access forum?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2003
    Posts
    150
    i have a few posts there already. I'm having more trouble writing my SP than dealing with the results on the client side.

    I'm having trouble declaring my input parameter and subsequently using it properly... havent written many SPs yet.

    this is what i have so far:

    ----------------------------------------------------------------------------------
    CREATE PROCEDURE dbo.sprocExtendedPrice

    AS SELECT intOrderID, intProductID, curUnitRetail, intQuantity, intDiscount, upsize_ts, CONVERT(money, (curUnitRetail * intQuantity) * (1 - intDiscount) / 100 * 100)

    AS curExtendedPrice

    FROM dbo.tblOrderDetails

    GO
    -----------------------------------------------------------------------------------

    thanks for any help!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK.....so what's the parameter?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    CREATE PROCEDURE dbo.sprocExtendedPrice (
    @OrderID int = null)
    as
    SELECT intOrderID, intProductID,
    curUnitRetail, intQuantity, intDiscount, upsize_ts,
    CONVERT(money, (curUnitRetail * intQuantity) *
    (1 - intDiscount) / 100 * 100) AS curExtendedPrice
    FROM dbo.tblOrderDetails
    where intOrderID = isnull(@OrderID, intOrderID)

    GO

  8. #8
    Join Date
    Nov 2003
    Posts
    150
    This is what i have:


    -----------------------------------------------------------------------------------
    (@OrderID int = NULL) AS SELECT intOrderID, intProductID, curUnitRetail, intQuantity, intDiscount, upsize_ts, CONVERT(money, (curUnitRetail * intQuantity)
    * (1 - intDiscount) / 100 * 100) AS curExtendedPrice
    FROM dbo.tblOrderDetails
    WHERE intOrderID = isnull(@OrderID, intOrderID)

    -----------------------------------------------------------------------------------

    The results of this sp will be displayed as a subform. I need the parameter to be equal to a control in my main form. So how do i reference a control in a form in the SP? Or if there is a better way to do things, let me know, i'd be more than happy to know!

    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If your control holds Order ID's, then you'd pass its value (ctrl.Text if it's a TextBox, cmb.List(cmb.ListIndex) if it's a ComboBox, etc.) to the stored procedure.

  10. #10
    Join Date
    Nov 2003
    Posts
    150
    and where do i enter this information? in VBA, or in the SP itself?

    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  11. #11
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88
    Why not use the subforms INPUT PARAMETER property?

    eg. @XFromDate datetime = GetFromDate(), @XCode nvarchar(2) = 'TX'

    This should pass your parameters along with the Recordsource SPROC.

Posting Permissions

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