Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 (?????)?

  • #2
    New to the CF scene
    Join Date
    Aug 2002
    Location
    Istanbul
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert into two tables

    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)
    %>


  • #3
    Regular Coder
    Join Date
    Jun 2002
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i am using access!?

  • #4
    New to the CF scene
    Join Date
    Aug 2002
    Location
    Istanbul
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 ...

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  • #7
    Regular Coder
    Join Date
    Jun 2002
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #8
    Regular Coder
    Join Date
    Jun 2002
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    is it Difficult ?
    please , i need it

  • #9
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)
    Whats the most efficient way in access

    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

  • #10
    Regular Coder
    Join Date
    Jun 2002
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

  • #11
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this link . According to this article if you have the proper setup with Access you can use @@identity.

  • #12
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts

    last record inserted

    With Access you could:
    Dim LastKey

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

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

  • #13
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #14
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quite true. However, I never suggested that Access was a good choice for multiple users either!


  •  

    Posting Permissions

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