locked
How to Insert/Update when dealing with Superclasses and Subclasses? RRS feed

  • Question

  • User-999963490 posted

    My Access database has a Payment entity, which has the subtype CheckPmt (check payments)

    How do I go about inserting into both tables?

    Payment has the PK "paymentID", which is an AutoNumber field in Access

    CheckPmt has the same PK, which is obviously a foreign key

    When inserting for Payment, the paymentID doens't have to be specified as an insert parameter... it is automatically generated through Access

    That being the case, how do I perform a separate insert where the paymentID in CheckPmt is going to be the same? 

     

    Sunday, July 31, 2011 7:58 PM

Answers

  • User3866881 posted

    Hey again:)

    Just a tip for you, plz use your own sql statement instead of mine

    using(OleDbCommand cmd = new OleDbCommand("insert into xxx values(……)",new OleDbConnection("Your conn str"))
    {
            cmd.Connection.Open();
     cmd.ExecuteNonQuery();
           
            cmd.CommandText="select top 1 Id from xxx order by Id desc";

            int id = (int)cmd.ExecuteScalar();

            cmd.CommandText="insert into xxx values(……, include the id as a foreign key)";

            cmd.ExecuteNonQuery();
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 2, 2011 9:46 PM
  • User3866881 posted

    Hello drocco:)

    Suppose your AccessDataSource is bound with Primary Table. Plz handle the DetailsView and do this:

    In the AccessDataSource_Updated—— Plz use my codes.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 2, 2011 10:51 PM

All replies

  • User3866881 posted

    Hello drocco:)

    You can use a OledbCommand to do insert a record into the Payment table, and then use select top 1 Id from xxx, and command.ExecuteScalar().ToString() to get the auto-number. And again use another insert or update statement to deal with the table CheckPmt.

    Thx again.

    Tuesday, August 2, 2011 8:58 PM
  • User-999963490 posted

    Any sample code? I've never done this before

    Thanks! 

    Tuesday, August 2, 2011 9:40 PM
  • User3866881 posted

    Hey again:)

    Just a tip for you, plz use your own sql statement instead of mine

    using(OleDbCommand cmd = new OleDbCommand("insert into xxx values(……)",new OleDbConnection("Your conn str"))
    {
            cmd.Connection.Open();
     cmd.ExecuteNonQuery();
           
            cmd.CommandText="select top 1 Id from xxx order by Id desc";

            int id = (int)cmd.ExecuteScalar();

            cmd.CommandText="insert into xxx values(……, include the id as a foreign key)";

            cmd.ExecuteNonQuery();
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 2, 2011 9:46 PM
  • User-999963490 posted

    To put it another way, I'm working with an AccessDataSource and a DetailsView at the ItemInserting event

    How do I insert into two different tables at once with the same ID value? 

    Tuesday, August 2, 2011 10:30 PM
  • User3866881 posted

    Hello drocco:)

    Suppose your AccessDataSource is bound with Primary Table. Plz handle the DetailsView and do this:

    In the AccessDataSource_Updated—— Plz use my codes.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 2, 2011 10:51 PM