Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    5

    Unanswered: Really need urgent help in Insert statement

    Hi,

    I am creating a event registration system. Right now my program is able to insert user's registered event into the database. This is the code i wrote:

    INSERT INTO EventRegistration(eventId,userId,status) VALUES('" + eid + "','" + id + "','" + status + "')

    However I notice that same user are able to register the same event when i use this code. How should i improve my code in order to prevent same user from registering the same event. Which means my sql statement will not insert registered event into the database if same user register the same event. I will really appreciate the help all of you offer.

    Thank you.

    Regards,
    fer

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Depending on your database tool, you probably just need a UNIQUE CONSTRAINT or a UNIQUE INDEX and things should be lovely.

    -PatP

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As Pat.P infers good table design should preclude you users entering duplicate data.

    What makes an event / entry unique in the real world and how do you represent that in your database?

  4. #4
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119
    if you are providing the user a list of options to register, I assume you are pulling the list of things to register from the database too ?

    only show them options to register for, that they have not registered for already.

    for instance, if you have 10 time slots available for something and someone picks time slot #3, then the next user will only see 9 time slots to pick from. (#3 is no longer displayed)
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

  5. #5
    Join Date
    Dec 2005
    Posts
    5

    Really need urgent help in Insert statement

    Hi Kropes2001,

    Ya you actually get wat i mean. I am providing a list of options for user to register, and options(events) are retrieve from the database. I get wat u mean but i dun realli noe how to implement it as i am new to sql. Can you provide me a sample coding of wat u mean?

    Thank you.

    ferlina

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you define the primary key, or alternatively a unique key correctly there can't be any duplicates.

    If you handle any eror thrown by the db engine then you can gracefully handle the situation where a single user has tried to make more than one booking for the same event.
    HTH

  7. #7
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119
    Sorry... but
    why "handle an error" when you can prevent it from happening to begin with ?
    i see too many programmers that do that. let the user enter whatever they want and then try to deal with all the errors it generates.
    the more logical design is to simply provide the necessary information in a way that prevents the user form making any errors like that to begin with. and its usually a lot easier too.

    an ounce of prevention vs. a pound of cure.

    ferlina,
    i would really need to see your table designs to give you an absolute answer.

    i am assuming that you have 1 table with all of the events that are available. plossible fields :
    EventID
    Description
    StartDate
    StartTime
    Active
    etc.....
    etc.....

    i assume that you then use a SELECT statement to build a recordset of all of the availalbe events. something like :
    SELECT EventID, Description, StartDate, StartTime from EventList WHERE Active=true

    i also assume that by the time you hit this page, you already know who your user is, or at least what their ID is ? (they already are defined in the database somelace, yes ?)

    if so, then combine the SELECT of available events with an outer join. you want to retrieve a list of all events that are active in teh database, except for the ones that are already scheduled by this UserID.

    take a look at this article

    http://www.devx.com/dbzone/Article/17403/0/page/4

    instead of selecting all of the registered events that match the user's ID, you are selecting all of the ones that they did not register for.
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In a multi user environment you have to trap for errors and handle them gracefully. After a user has booked an event then you can exclude them from appearing in future combo select boxes, but untll they have booked you are running the risk of an error. Granted you could set a flag on the user to say they are in process of making a booking - but that doesn;t stp clients that have already loaded available users from attempting to make a duplicate booking.

    It is always theoretically possible for a user to be booking an event from more than one session at the same time (either through user error or deliberate attempt to subvert the system.

Posting Permissions

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