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,
ferDepending on your database tool, you probably just need a UNIQUE CONSTRAINT or a UNIQUE INDEX and things should be lovely.

-PatP|||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?|||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)|||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|||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|||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.dev-archive.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.|||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.

Really confused in SQL statement

Hi there,

I'd like to execute an SQL query but i can not find a suitable way.Well i have one table contains :

Table 1 :
Id, CustNo, CustName, CustSurName, CustId1, CustId2,CustId3

Second Table contains :

Table 2 :

Id, WhNo, WhName, WhSurName, CustId1, CustId2,CustId3

As you see CustId1, CustId2,CustId3 is common.I would like to combine and show them in one table. It is simple with :
select a.* , b.* from Table1 a Table2 b ......(blah blah blah)

I select CustName rows from Table1 and can choose CustId1, CustId2, CustId3.But if i choose CustName (Customer Name) and it returns more than one row i can not match CustId's from Table2 and display in merged Query object.

Let me give an example :

select * from Table1 (where CustName = 'Joe') ;

gives these results :

Id, CustNo, CustName, CustSurName, CustId1, CustId2,CustId3

1,1,Joe,Black,1,1,1
2,2,Joe,Green,1,1,2
3,3,Joe,Blue,1,1,3

select * from Table2

gives these results :

Id, WhNo, WhName, WhSurName, CustId1, CustId2,CustId3

1,1,1,Apple,Peach,1,1,1
1,1,1,Apple2,Peach2,1,1,1
1,1,1,Apple3,Peach3,1,1,2
1,1,1,Apple4,Peach4,1,1,2
1,1,1,Apple5,Peach5,1,1,2
1,1,1,Apple6,Peach6,1,1,3
1,1,1,Apple7,Peach7,1,1,3
1,1,1,Apple8,Peach8,1,1,3
1,1,1,Apple9,Peach9,1,1,3
1,1,1,Apple10,Peach10,1,1,3

As you see 1,1,1,Joe,Black has 2 records in Table2 1,1,2,Joe,Green has 3 records in Table2 finally 1,1,3,Joe,Blue has 5 records in Table2.
Now i would like to select only name in Table1 and display merged table like this :

Table1 and Table 2 : (for example i choose "Joe" from Table 1 and the result is

1,1,Joe,Black,1,1,1,Apple,Peach
1,1,Joe,Black,1,1,1,Apple2,Peach2
2,2,Joe,Green,1,1,2,Apple3,Peach3
2,2,Joe,Green,1,1,2,Apple4,Peach4
2,2,Joe,Green,1,1,2,Apple5,Peach5
.
.
.
(goes like this)

As you see i want to select name from Table 1 and it should give merged table.I only have CustId1,CustId2,CustId3 as common field.

Thanks in advance.

AnalyzerTry this:

Select a.Id, a.CustNo, a.CustName, a.CustSurName
, a.CustId1, a.CustId2, a.CustId3
, b.WhName, b.WhSurName
From Table1 a, Table2 b
Where b.CustId3 = a.CustId3;

;)|||Hi,

Thank you for your reply but i can not select my list with this code because first of all i would like to select by name then Table 1 will return CustId numbers.Finally i'd like to make a merged list which contains Table1+Table2 values :(
Pat adviced me to use inner join for this.What is it?

Analyzer

P.S. By the way i didnt see an SQL section in the forum and i cross posted.I apologize for this.|||Which RDBMS software are you using?|||Well i am using MySQL 4.0+Borland C++ Builder 6.0 Ent+Zeos DBO 6.1.5|||Try something like:SELECT *
FROM table1
INNER JOIN table2
ON (table2.custId1 = table1.custId1
AND table2.custId2 = table1.custId2
AND table2.custId3 = table1.custId3)-PatP|||Hi Pat,

Thank you for your reply.But first i want to search on Customer Name then i would like to select columns from table2.I wrote this code :

SELECT * FROM table1 WHERE (CustName="John" and CustSurName="Blue") INNER JOIN table2 ON (table1.CustId1 = table2.CustId1 AND table1.CustId2 = table2.CustId2 AND table1.CustId3 = table2.CustId3)

But it does not work.Where is my fault?
I would like to explain more.For example John Blue purchased a printer, 2 DSL Modem and 1 monitor.I would like to search who named "John" and what did he purchased.
First of all i need to search name then i will search on CustId1,CustId2,CustId3.

I found this page : http://www.w3schools.com/sql/sql_join.asp but it does not elaborate.

Analyzer.|||Ok, i did it :)

SELECT * FROM table1 INNER JOIN table2 ON (table1.CustId1 = table2.CustId1 AND table1.CustId2 = table2.CustId2 AND table1.CustId3 = table2.CustId3) WHERE (CustName="John" and CustSurName="Blue")

Thank you for your all help.I appreciated it much.

Analyzer