SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict mikeistyke's Avatar
    Join Date
    Dec 2004
    Location
    staunton
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pass PK and FK to do INSERT statement

    Using Access and Asp:
    Three tables: Registration, Session, Payment: Joined with 1 to many rel. starting with registration, moving to session and ending with payment.
    Form 1: Registration= INSERT INTO statement works fine. Reg table now has fields populated with form data.
    Form 2: Session= <<<<<this is where the problem occurs>>>>>
    Form 3: <<<<<<problem as well>>>>>>

    When i want to do another INSERT INTO statement, I have to know the RegID from the earlier table join(registration). Of course, I can't run either Form 2 or Form 3 without knowing the RegID from the Registration table begin point.
    I don't know how to increment the RegID as a foreign key in a joined table over the internet. Using Access over the network it's real easy to pass the foreign keys and the design seems to be more flexible this way, but I need to update tables and present stored data to the user across the internet.
    Thank you for all the help in the past and hope this one isn't as hard for you as it is for me!

  2. #2
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont think i really do understand your requirements, but u can use the SCOPE_IDENTITY( ) function. To get the increment ID.

    other functions are IDENT_CURRENT and @@identity to get the current increment value.

    I prefer Scope_identity() because it works within the scope you are working with.

    many such examples in this forum, or read the books online. Press F1 key

  3. #3
    SitePoint Addict mikeistyke's Avatar
    Join Date
    Dec 2004
    Location
    staunton
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Africa:
    However, the @@Identity is unfamiliar to me. I have seen some examples of this but never really look at it.
    Her'es the problem at hand:
    I have three tables all joined in one to many relationships.
    When I use the SQL INSERT INTO statement to add data to the first table(registration) it works fine. However, the join on the second table(Session) contains the PK RegID Field from the Registration Table, so now the second table begins to look like this: SessionID, RegID, blah , blah, blah... .
    How does the second table recognize and update the FK Field RegID in the Session table? Right now, the error says something like...unable to use the insert statement do to the table joins.
    Does that help?

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes i do understand your code well.

    Its simple, after you do an insert in your SQL statement

    declare a variable and assign it with the scope_identity() function

    e.g.
    declare @mikeistyke int
    set @mikeistyke = scope_identity()
    then... do whatever

    Afrika

  5. #5
    SitePoint Addict mikeistyke's Avatar
    Join Date
    Dec 2004
    Location
    staunton
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's it?

  6. #6
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try it

  7. #7
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try it and see.

    Put that code below your insert script.

    It would generate the auto increment value into the variable. Then you could insert it into your session table.

    as
    insert into session (regid) values @mikeistyke
    am assuming that the sessionID column is an auto increment column?

    afrika

  8. #8
    SitePoint Addict mikeistyke's Avatar
    Join Date
    Dec 2004
    Location
    staunton
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes it is

  9. #9
    SitePoint Addict mikeistyke's Avatar
    Join Date
    Dec 2004
    Location
    staunton
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Afrika:
    I got lost along the way. I apologize. Let me be more clear.
    I have 3 tables from MS Access, 3 html form pages, and 3 scripts written on asp pages.
    table one, registration, script one, perfect, no problems to report.
    table two, session, script two, throws error on the @ and doesn't like the set or declare words.
    table three, payment, not yet tested due to earlier failure in table two.
    btw, it's a little easier for me to test this stuff with html forms first, before going to a request.servervariables(script_name) call, later executed in the script.
    the second script will run on sessionform.asp, gathering info from sessionform.html. I guess access doesn't allow multiple table updates.
    So, if you could, please guide me from the sessionform.asp page, that gets it's FK field from registrationform.asp
    Thank you for your help again.

  10. #10
    SitePoint Addict mikeistyke's Avatar
    Join Date
    Dec 2004
    Location
    staunton
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    anyone have any idea how to get beyond this? I'm stuck.

  11. #11
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you do the first insert, retrieve the ID of the record you just inserted using afrika's @@identity method, store that in a session variable, then go to the next form.

    When you insert the data from the second form, use the stored ID from before - and store another ID for the session in a session variable. You can then handle the last form with the stored session id value.

  12. #12
    SitePoint Addict mikeistyke's Avatar
    Join Date
    Dec 2004
    Location
    staunton
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Jim. I'll give it a whirl. Using Access over the internet is surely different than over a network without the internet. Lots of things about access make zero sense, and finding information to do some of these stupid things is even harder.
    Thanks you guys...

  13. #13
    SitePoint Addict mikeistyke's Avatar
    Join Date
    Dec 2004
    Location
    staunton
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let's try this one more time:
    The first insert statement (tblRegistration) does not require the primary key to be acknowledged in the insert. So when i insert into the registration table there is no mention of the RegistrationID. This is done automatically.
    I execute my sql insert statement (dataconn.execute strQ), close the connection, then do a redirect.
    Where does this code belong?
    declare @mikeistyke int
    set @mikeistyke = scope_identity()
    ...and is the @ symbol necessary?
    ...and should the proper syntax be: declare @registrationID int, set @registrationID = scope identity()?

  14. #14
    SitePoint Wizard gRoberts's Avatar
    Join Date
    Oct 2004
    Location
    Birtley, UK
    Posts
    2,439
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    best way is to use a session? take a few details that will link your customer directly, ie DOB, house address, etc then stick it in a session, then search your database for the customer and update or add etc.

    Gav


Bookmarks

Posting Permissions

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