insert into two tables

I have two tables in my database (products, color)

-products table has three fields (id, name, type).

-color table has three fields too (color_id, prod_id, color).

-there are relation between two tables (id is a primary key in "products" and foreign key in "color" as prod_id).

- I want to insert in the two tables using this code:

<%sql1="insert into products (name,type)values('"&var1&"','"&var2&"')"
conn.execute(sql)
<!--here are some conditions if exist will insert in the other table-->
sql2="insert into color(prod_id,color)values("&??????&",'"&var3&"')"
conn.execute(sql2)
%>


-the question is how to get latest record's id inserted to put it in (?????)?

Hi , if you are using MS-SQL server, you can use Select @@IDENTITY method and Set Nocount On together


<%

sql1="insert into products (name,type)values('"&var1&"','"&var2&"')";SET NOCOUNT ON ;select @@identity as IdentityNo"

Set MyRs = Conn.Execute(sql1)

ReturnedID = MyRs("IdentityNo")


sql2="insert into color(prod_id,color)values("& ReturnedID &",'"&var3&"')"
conn.execute(sql2)
%>

:thumbsup:

i am using access!?

maybe you can use an additional query and get last identity,

sql11 = " Select top 1 ProductID from products Order by ProductID desc"

set Rs1 = Conn.execute(sql11)

ReturnedID = Rs1("ProductID")

this can bu usefull , for single user input . if some other users try to insert a data returnedID will be different from requested . to try to avoid this use more complicated additional query like

sql11 = " Select top 1 ProductID from products where
name = '"&var1&"' and type='"&var2&"'"

set Rs1 = Conn.execute(sql11)

ReturnedID = Rs1("ProductID")

still you cannot be sure of the returnedID with this method ...

I just don't understand why you'd want to insert anything into two tables - if you're using relational databases correctly, you should only have to update one table, in my experience.

It all depends on your database layout, and good planning (although I must admit sometimes good planning is not ALLOWED (because of time constraints) when you do this for a living, lol)

whammy is right (about the first thing) : why the hell insert into two tables ? Why do you need that product-ID in the colors table ? (I don't know your application, buth I think you're creating problems here)

If you wan't to link the tables, you should insert the colorID into the products table (since color is just one of the features of your product) And it would be very simple to generate a form with a dropdown containing all the colors (with the colorID's as values)

at the first I have a table of (products) and each product has many colors and many sizes and each color and size has different price, so many redundancy will be in product table, so I created another table called (features) contains (color,size,colorprice,sizeprice) not only which I inserted in my question.
Then the relation between the two tables is one to many.

First I will insert in products table the common data
Second I will insert in the feature table the other data as I describe before.
What the best way to perform this using the facility of the relation between the two tables?
Explain depend on the simple code I wrote in my question
I am using access

is it Difficult ?
please , i need it:confused:

sorry, i responded so slow buth i took a long weekend.

now, it's your app so you call the shots, buth their are some thing you should consider. you might start by reading this thread whitch deals with the problem of data-design (what to put in whitch table)
http://www.codingforums.com/showthread.php?s=&threadid=3818

i never register data more then once in my database. if you ever need to update it, you will know why !!

i made simmilar app's where i had a table with products (for instace one with all sorts of granit tablets. each tablet has about 20 features --> my productstable had 25 variables (20 features + ID + date +user that inserted + ...) --> and every record looked like :

1|20|3|5|6|...|2

You know, just ID's from other tables. For instance the table "sort_granit" had a record with ID 20 and a description, a name, a price etc etc

In this second table (similar to your colorstable), their isn't anything registered/changed when i inserte a new granit tablet.
I just pull an ID out of it, and via an inner join, i can link all the info from the sort_granit table to the productstable.

these "feature-tables" should not containe data about a specific product/order/piece etc

when you insert a new product, you must descibe it, using the ID's from the tables thac contain the info about the features.
For exampe : a table 'color' with 'ColorID', 'colorname', 'colorprice'
another table 'size' with 'SizeID','sizename', 'sizeprice'
etc

a new product will then be described by
productID, sizeID, colorID etc or
productID,sizeAsizeID, sizeAcolorID, sizeBsizeID, sizeBcolorID etc

that depends on what sort of products

i don't know if this cleares up thing. if not, let us know

Thank you raf, your answer is very useful, but the design isn't the main problem, my database design isn't bad, at least satisfy my site requirements.
To conclude my problem, please type 4 sql statements(select,insert,ubdate,delete) dealing with relational tables. Just!

Try this link (http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=79) . According to this article if you have the proper setup with Access you can use @@identity.

With Access you could:
Dim LastKey

.....etc.
set the values
rs.Update

rs.MoveLast
LastKey = rs("ID")
rs.Close
Set rs = Nothing

what fractalvibes suggest isn't good for apps with multiple users.

their are a lot of other threads that deal with this problem (how to get data from a the record you just inserted)for instance this one : Order number duplicating and I don't now why??

i think you find some useful info in allida77's sufggested link.

Quite true. However, I never suggested that Access was a good choice for multiple users either!