22 Replies Latest reply on May 9, 2006 12:06 PM by Newsgroup_User

    A Database question

    ferrarikid123 Level 1
      I've created an "insert record" using the wizard. How would I display this information from the database on another page, once a user enters information into the first page? I keep getting information on the second page from the first record. I need the second page to show the latest record, ie what was inserted into the first page. I'm sure its very simple, but I've spent hours reading books and its not a common tast to perform. A master detail page won't acheive this as it only displays information from "already created" records. Hope that make sense? Thanks very much
        • 1. Re: A Database question
          bregent Most Valuable Participant
          >I need the second page to show the latest record

          There really is no concept of a 'latest record" inserted in a dbms. You need to uniquely identify each row, and pass the identity value to the next page. What are you using to identify each row?
          • 2. Re: A Database question
            ferrarikid123 Level 1
            By "each row" do you mean each record? I have a unique number for each one, they are indexed with no duplicates, so it auto-increments the next number. How would I pass this unique identity to the next page? Thanks Bregent
            • 3. Re: A Database question
              Julesmg Level 1
              A master detail page will build on a uniqueID being passed from the first page.

              masterPage.asp?uniqueID=1

              Example: Request.queryString("uniqueID")

              If you want to simply display the last record entered into a table use the ORDER BY DESC in your SQL and remove any repeat region behaviours on the second page.

              • 4. Re: A Database question
                Level 7
                Hi

                Are you using a Microsoft Access or SQL Server database?

                Brendan

                --
                ______________________________________________
                Rate your experiences with your UK and Ireland builders at
                http://www.ratethebuilder.co.uk
                =========================================


                "ferrarikid123" <webforumsuser@macromedia.com> wrote in message
                news:e3or62$bka$1@forums.macromedia.com...
                > I've created an "insert record" using the wizard. How would I display this
                > information from the database on another page, once a user enters
                information
                > into the first page? I keep getting information on the second page from
                the
                > first record. I need the second page to show the latest record, ie what
                was
                > inserted into the first page. I'm sure its very simple, but I've spent
                hours
                > reading books and its not a common tast to perform. A master detail page
                won't
                > acheive this as it only displays information from "already created"
                records.
                > Hope that make sense? Thanks very much
                >


                • 5. Re: A Database question
                  Level 7
                  Try the following links to see if they might help:

                  http://www.webxel-dw.co.uk/extensions/DreamweaverCtrls_details.html
                  http://www.drdev.net/article08.htm
                  http://www.tom-muck.com/extensions/help/insertretrieve/
                  http://www.dmxzone.com/ShowDetail.asp?NewsId=195



                  --
                  ______________________________________________
                  Rate your experiences with your UK and Ireland builders at
                  http://www.ratethebuilder.co.uk
                  =========================================


                  "Brendan" <Brendan.Collins@_RemoveThis_Singularity.co.uk> wrote in message
                  news:e3poq4$gu3$1@forums.macromedia.com...
                  > Hi
                  >
                  > Are you using a Microsoft Access or SQL Server database?
                  >
                  > Brendan
                  >
                  > --
                  > ______________________________________________
                  > Rate your experiences with your UK and Ireland builders at
                  > http://www.ratethebuilder.co.uk
                  > =========================================
                  >
                  >
                  > "ferrarikid123" <webforumsuser@macromedia.com> wrote in message
                  > news:e3or62$bka$1@forums.macromedia.com...
                  > > I've created an "insert record" using the wizard. How would I display
                  this
                  > > information from the database on another page, once a user enters
                  > information
                  > > into the first page? I keep getting information on the second page from
                  > the
                  > > first record. I need the second page to show the latest record, ie what
                  > was
                  > > inserted into the first page. I'm sure its very simple, but I've spent
                  > hours
                  > > reading books and its not a common tast to perform. A master detail page
                  > won't
                  > > acheive this as it only displays information from "already created"
                  > records.
                  > > Hope that make sense? Thanks very much
                  > >
                  >
                  >


                  • 6. Re: A Database question
                    ferrarikid123 Level 1
                    Hi thanks for the help, I'm using Microsoft Access, is that a problem? I am just trying to show the last record in that field.
                    To elaborate, I've got a table called cost1 and cost2 and total. The total is worked out through a query designed in Microsoft Access, which simply adds the two costs together. I want to be able to allow the user to type in the two costs on the first page using a "insert record", and then take me to the next page with the total displayed from the database. So the second page has a recordset query from the query designed in the database. Thanks a lot and I hope that clarifies my problem.
                    • 7. Re: A Database question
                      ferrarikid123 Level 1
                      I forgot to mention, I'm using ASP Javascript for this. Thanks
                      • 8. Re: A Database question
                        ferrarikid123 Level 1
                        I also forgot to add that in the table in Access I also have a field called "CostID" which is set to Autonumber and has no index.
                        • 9. Re: A Database question
                          Level 7
                          Build your recordset on the page and sort the records in DESC order. Pick
                          the first record. That's the last one entered.

                          --
                          Murray --- ICQ 71997575
                          Adobe Community Expert
                          (If you *MUST* email me, don't LAUGH when you do so!)
                          ==================
                          http://www.dreamweavermx-templates.com - Template Triage!
                          http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
                          http://www.dwfaq.com - DW FAQs, Tutorials & Resources
                          http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
                          ==================


                          "ferrarikid123" <webforumsuser@macromedia.com> wrote in message
                          news:e3pqbm$ir5$1@forums.macromedia.com...
                          >I forgot to mention, I'm using ASP Javascript for this. Thanks


                          • 10. Re: A Database question
                            ferrarikid123 Level 1
                            Managed to get it to work! Thanks for your help.
                            • 11. Re: A Database question
                              Level 7
                              .oO(Murray *ACE*)

                              >Build your recordset on the page and sort the records in DESC order. Pick
                              >the first record. That's the last one entered.

                              This doesn't necessarily have to be the one which was entered on the
                              previous page ...

                              Micha
                              • 12. Re: A Database question
                                Level 7
                                It does if the index key is automatically generated (as stated).

                                --
                                Murray --- ICQ 71997575
                                Adobe Community Expert
                                (If you *MUST* email me, don't LAUGH when you do so!)
                                ==================
                                http://www.dreamweavermx-templates.com - Template Triage!
                                http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
                                http://www.dwfaq.com - DW FAQs, Tutorials & Resources
                                http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
                                ==================


                                "Michael Fesser" <netizen@gmx.de> wrote in message
                                news:7jv062lve4h63e3i29ntfgah9q2l18a0nq@4ax.com...
                                > .oO(Murray *ACE*)
                                >
                                >>Build your recordset on the page and sort the records in DESC order. Pick
                                >>the first record. That's the last one entered.
                                >
                                > This doesn't necessarily have to be the one which was entered on the
                                > previous page ...
                                >
                                > Micha


                                • 13. Re: A Database question
                                  Level 7
                                  Michael

                                  Was thinking this myself, but am afraid of Murray :-)

                                  If two users were performing the same action at the same time, then would
                                  not one of them have the slim chance of seeing the others entered
                                  information instead of their own?

                                  Brendan
                                  --
                                  ______________________________________________
                                  Rate your experiences with your UK and Ireland builders at
                                  http://www.ratethebuilder.co.uk
                                  =========================================


                                  "Michael Fesser" <netizen@gmx.de> wrote in message
                                  news:7jv062lve4h63e3i29ntfgah9q2l18a0nq@4ax.com...
                                  > .oO(Murray *ACE*)
                                  >
                                  > >Build your recordset on the page and sort the records in DESC order.
                                  Pick
                                  > >the first record. That's the last one entered.
                                  >
                                  > This doesn't necessarily have to be the one which was entered on the
                                  > previous page ...
                                  >
                                  > Micha


                                  • 14. Re: A Database question
                                    Level 7
                                    .oO(Murray *ACE*)

                                    >It does if the index key is automatically generated (as stated).

                                    Yes, in this case:

                                    User A inserts a new record with auto-ID, e.g. 42
                                    User A fetches record with highest ID -> 42
                                    User B inserts a new record with auto-ID, e.g. 43
                                    User B fetches record with highest ID -> 43

                                    But there's no guarantee that both statements sent from user A will be
                                    executed consecutively without interference from user B:

                                    User A inserts a new record with auto-ID, e.g. 42
                                    User B inserts a new record with auto-ID, e.g. 43
                                    User A fetches record with highest ID -> 43
                                    User B fetches record with highest ID -> 43

                                    That's called a race condition, which can happen quite easily in a
                                    multi-user environment like a web server.

                                    The solution is to let the insert-script grab the generated ID and
                                    forward it to the second script, so that it can fetch the correct
                                    record. How to grab the ID depends on the used DB and its capabilities.
                                    Usually there are two possible ways:

                                    1) The use of sequences to generate the ID _before_ inserting the new
                                    record.

                                    2) The use of a particular DB-function to retrieve the auto-generated ID
                                    after inserting the record, based on the current connection. In MySQL
                                    for example this can be done with a call to LAST_INSERT_ID() (in other
                                    servers there's @@identity or something like that). The result would
                                    then be like this:

                                    User A inserts a new record with auto-ID, e.g. 42
                                    User B inserts a new record with auto-ID, e.g. 43
                                    User A calls LAST_INSERT_ID() -> 42
                                    User B calls LAST_INSERT_ID() -> 43

                                    Micha
                                    • 15. Re: A Database question
                                      Level 7
                                      Ahh - yes, I see. I was not thinking about simultaneous hits.

                                      --
                                      Murray --- ICQ 71997575
                                      Adobe Community Expert
                                      (If you *MUST* email me, don't LAUGH when you do so!)
                                      ==================
                                      http://www.dreamweavermx-templates.com - Template Triage!
                                      http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
                                      http://www.dwfaq.com - DW FAQs, Tutorials & Resources
                                      http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
                                      ==================


                                      "Michael Fesser" <netizen@gmx.de> wrote in message
                                      news:sf01621vh3embd967tmfvpiqu1npmsachr@4ax.com...
                                      > .oO(Murray *ACE*)
                                      >
                                      >>It does if the index key is automatically generated (as stated).
                                      >
                                      > Yes, in this case:
                                      >
                                      > User A inserts a new record with auto-ID, e.g. 42
                                      > User A fetches record with highest ID -> 42
                                      > User B inserts a new record with auto-ID, e.g. 43
                                      > User B fetches record with highest ID -> 43
                                      >
                                      > But there's no guarantee that both statements sent from user A will be
                                      > executed consecutively without interference from user B:
                                      >
                                      > User A inserts a new record with auto-ID, e.g. 42
                                      > User B inserts a new record with auto-ID, e.g. 43
                                      > User A fetches record with highest ID -> 43
                                      > User B fetches record with highest ID -> 43
                                      >
                                      > That's called a race condition, which can happen quite easily in a
                                      > multi-user environment like a web server.
                                      >
                                      > The solution is to let the insert-script grab the generated ID and
                                      > forward it to the second script, so that it can fetch the correct
                                      > record. How to grab the ID depends on the used DB and its capabilities.
                                      > Usually there are two possible ways:
                                      >
                                      > 1) The use of sequences to generate the ID _before_ inserting the new
                                      > record.
                                      >
                                      > 2) The use of a particular DB-function to retrieve the auto-generated ID
                                      > after inserting the record, based on the current connection. In MySQL
                                      > for example this can be done with a call to LAST_INSERT_ID() (in other
                                      > servers there's @@identity or something like that). The result would
                                      > then be like this:
                                      >
                                      > User A inserts a new record with auto-ID, e.g. 42
                                      > User B inserts a new record with auto-ID, e.g. 43
                                      > User A calls LAST_INSERT_ID() -> 42
                                      > User B calls LAST_INSERT_ID() -> 43
                                      >
                                      > Micha


                                      • 16. Re: A Database question
                                        ferrarikid123 Level 1
                                        Thanks Fresser, I was thinking the exact same thing last night. What if two users are on the system simultaneously. I'm using Microsoft Access 2002 with ASP Javascript. Where would I be able to find out more information on the two types of methods you suggested?
                                        • 17. Re: A Database question
                                          ferrarikid123 Level 1
                                          Ideally, a unique number should be created before a user enters 2 numbers into the insert record form. When the user submits this, the next page should display the total of the 2 numbers, corresponding to that record and that record only. How would you make sure its the correct record being displayed. Presummably, the unique ID will responsible for this, but how can I set this up?
                                          • 18. Re: A Database question
                                            Level 7
                                            .oO(ferrarikid123)

                                            >Thanks Fresser, I was thinking the exact same thing last night. What if two
                                            >users are on the system simultaneously. I'm using Microsoft Access 2002 with
                                            >ASP Javascript. Where would I be able to find out more information on the two
                                            >types of methods you suggested?

                                            I can't help you directly, since I don't use Access. But a quick search
                                            on Google or Google Groups might give you a start.

                                            Micha
                                            • 19. Re: A Database question
                                              bregent Most Valuable Participant
                                              >Ahh - yes, I see. I was not thinking about simultaneous hits.

                                              It's probably also not a good idea from a performance perspective. You never want to select every row in a table if you are going to just use one. What if you have millions of rows? That would require a rather large amount of resources to build the cursor for that operation.
                                              • 20. Re: A Database question
                                                bregent Most Valuable Participant
                                                >Presummably, the unique ID will responsible for this, but how can I set this up?

                                                This is MS suggested method for retrieving an identity column.

                                                http://support.microsoft.com/default.aspx?scid=kb;EN-US;221931
                                                • 21. Re: A Database question
                                                  bregent Most Valuable Participant
                                                  >By "each row" do you mean each record?

                                                  Yeah, sure. The term 'record' is a older term carried over from the file processing days. The correct term for a relational DBMS, at least at the physical level is 'row'. The more formal relational algabraic term is tuple. But anyone will know what you mean if you use row or record.
                                                  • 22. Re: A Database question
                                                    Level 7
                                                    I should live so long to have a database with millions of rows! 8)

                                                    --
                                                    Murray --- ICQ 71997575
                                                    Adobe Community Expert
                                                    (If you *MUST* email me, don't LAUGH when you do so!)
                                                    ==================
                                                    http://www.dreamweavermx-templates.com - Template Triage!
                                                    http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
                                                    http://www.dwfaq.com - DW FAQs, Tutorials & Resources
                                                    http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
                                                    ==================


                                                    "bregent" <webforumsuser@macromedia.com> wrote in message
                                                    news:e3qhms$jog$1@forums.macromedia.com...
                                                    > >Ahh - yes, I see. I was not thinking about simultaneous hits.
                                                    >
                                                    > It's probably also not a good idea from a performance perspective. You
                                                    > never
                                                    > want to select every row in a table if you are going to just use one. What
                                                    > if
                                                    > you have millions of rows? That would require a rather large amount of
                                                    > resources to build the cursor for that operation.
                                                    >