Constraint problem

I having problems figuring out how to use contraints.

I have three tables tbl_photos, tbl_customers, tbl_register.

tbl_register has a foreign key contraint from both tbl_photos and tbl_customers which are photo_id and cust_id.

I have the following code on a page where I am adding a customer to the tbl_customers but I also want to add a entry to the tbl_register showing that this customer has registered a particular photo_id. So I have to insert into the tbl_register the cust_id and the photo_id.

SQL_1 = "INSERT INTO tbl_customers(firstname, lastname, email, zipcode, optin) VALUES('"&firstname&"', '"&lastname&"', '"&email&"', '"&zipcode&"', '"&optin&"')"
SQL_2 = "SELECT cust_id, email FROM tbl_customers WHERE email = '"&email&"'"
SQL_3 = "INSERT INTO tbl_register(cust_id, photo_id) Values('"&new_cust_id&"', '"&photo_id&"')"
SQL_4 = "UPDATE tbl_photos SET registered = 'Yes'"

Set RS = conn.Execute(SQL_1)
Set RS = conn.Execute(SQL_2)
new_cust_id = RS.Fields("cust_id").Value
Set RS = conn.Execute(SQL_3)
Set RS = conn.Execute(SQL_4)

I am getting an error:

[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tbl_register_tbl_customers'. The conflict occurred in database 'photoctm_sql', table 'tbl_customers', column 'cust_id'.

I am sure there is a much better way of doing this!If it were me, I'd be using stored procedures. Create a procedure for your customer insert that uses the @.@.identity property to capture the cust_id that was used and return that value to your application as an output parameter.

Since my using the email address to look up the customer just added, you can't be certain that you're getting the right ID back unless you have a unique constraint on the email attribute. You're problem may be that there's already a customer with the given email address, although that should just register the photo with the wrong customer and not return an error.

However, the problem is most likely that you are building the SQL_3 string before you've assigned the value to new_cust_id. If you move your SQL3 = ... statement to just before it's executed, it should work...

If you're dead-set against using stored procedures, though, put a unique constraint on the email attribute at the very least.