ADO or ODBC?

This is a discussion on ADO or ODBC? within the Windows Programming forums, part of the Platform Specific Boards category; I don't want to make a multiple record insert, but to insert one row in the table. With the nested ...

  1. #16
    Registered User
    Join Date
    Sep 2003
    Posts
    87
    I don't want to make a multiple record insert, but to insert one row in the table.
    With the nested SELECT query I want to get an Id from another table.

  2. #17
    Yes, my avatar is stolen anonytmouse's Avatar
    Join Date
    Dec 2002
    Posts
    2,544
    Did you read the link I posted? The multiple record syntax can insert one or more records.

    If you have to insert values from another table as well as literal values you will have to get creative:
    Code:
    INSERT INTO SomeTable (PersonID, FavouriteFood, Location)
    SELECT ID, 'Fried Carrots', 'Jail' FROM People WHERE Name='some name';
    or maybe:
    Code:
    INSERT INTO SomeTable (PersonID, FavouriteFood, Location)
    SELECT ID, "Fried Carrots" AS FavouriteFood, "Jail" AS Location FROM People WHERE Name='some name';

  3. #18
    Registered User
    Join Date
    Sep 2003
    Posts
    87
    Sorry, I took a look, but I obviously missed this. Now I understand. Thank you.

    This question could be a little bit sily, but in the documentation
    doesn't say anything about:
    What about if I want to run SELECT query on two or more
    tables to get Id's?
    Last edited by Gravedigga; 08-01-2004 at 11:18 AM.

  4. #19
    Yes, my avatar is stolen anonytmouse's Avatar
    Join Date
    Dec 2002
    Posts
    2,544
    Well, you should be able to use the normal table joining syntax (INNER JOIN, simple WHERE join, etc). How do you need to combine the two tables?

  5. #20
    Registered User
    Join Date
    Sep 2003
    Posts
    87
    OK. I have three tables:

    1)Reasons with Id AUTOINCREMENT and Reason TEXT
    2)People with Id AUTOINCREMENT, names TEXT, address TEXT
    3)Orders with Id AUTOINCREMENT,PersonId INT, ReasonId INT, Value FLOAT and OrderDate Date

    I want to INSERT a record in Orders like this:
    Code:
    INSERT INTO Orders(PersonID,ReasonID,Value,OrderDate)
    VALUES((SELECT Id FROM People WHERE Names='some name'),
    (SELECT Id FROM Reasons WHERE Reason='some reason'),1.50,
    '1/2/2004');
    This query works with Postgres SQL, but not with Access throught
    ODBC. I guess that nested queries are not allowed and do it with
    separate queries, but is there any way with one SQL statement?

    And I need LEFT/RIGHT JOIN to query the Orders table after that.
    (To get the Names throught PersonId, for example)
    Last edited by Gravedigga; 08-01-2004 at 03:04 PM.

  6. #21
    Yes, my avatar is stolen anonytmouse's Avatar
    Join Date
    Dec 2002
    Posts
    2,544
    You can probably just use a table combine. When you don't provide a join condition jet combines all possible combinations. You can then add the where clause to only get the row you are interested in.

    For example if you have table 1 with data items '1a', '1b', '1c' and table 2 with data items '2a' '2b' jet creates from:
    Code:
    Select table1.col1, table2.col1 FROM table1, table2
    something like this:
    Code:
    1a 2a
    1a 2b
    1b 2a
    1b 2b
    1c 2a
    1c 2b
    All the possible combinations are created! If you don't use a WHERE clause you could, in theory, get a massive result set. A two column result set created from two tables with 10000 records each would give you a hundred million records.

    Anyway, with this behaviour in mind, you would use something like this:
    Code:
    INSERT INTO Orders(PersonID, ReasonID, Value, OrderDate)
    SELECT People.Id, Reasons.Id, 1.5, '1/2/2004'
    FROM People, Reasons WHERE People.Names='some name' AND Reasons.Reason='some reason';

Page 2 of 2 FirstFirst 12
Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Help With ODBC Code
    By zzman in forum C Programming
    Replies: 9
    Last Post: 08-22-2008, 03:18 PM
  2. Oracle cursor in ODBC
    By Mortissus in forum C Programming
    Replies: 4
    Last Post: 09-10-2007, 01:19 PM
  3. Multi-Threading and ADO
    By chandhru in forum Windows Programming
    Replies: 19
    Last Post: 05-15-2004, 11:53 AM
  4. odbc with vc++
    By doublin in forum C++ Programming
    Replies: 1
    Last Post: 03-21-2003, 09:09 AM
  5. Linking error using ODBC with C and BCC32
    By D@nnus in forum C++ Programming
    Replies: 0
    Last Post: 11-17-2001, 04:11 AM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21