CODEWITHSUNDEEP
.netms-accessms-access-2007
broke
broke

Reputation: 8302

Best way to insert data into multiple tables in access?

Okay so here is my database:

http://i488.photobucket.com/albums/rr245/lolwtf_album/database.jpg

I have a page on my .net form that lets users insert a workout and the related muscles for that workout into an access database.

Once I insert a record to the WorkOuts table, how do I insert the related muscles into the MuscleGroup table without having the select the max ID of the workout that was just inserted? I want to insert everything in together, at the same time. Hopefully my question makes sense, I think this is a common database issue, but I haven't found a solution yet.

Upvotes: 1

Views: 3121

Answers (2)

ron tornambe
ron tornambe

Reputation: 10780

Although I agree you should look into using alternate RDBM'S, you can use the following code (assumes you are using OLEDB and C#) to retrieve the correct AutoNumber generated by an MS-Access Insert statement:

public static object InsertRow_ReturnKey()
{
  try
  {
    using (OleDbConnection con = new OleDbConnection())
    {
      con.ConnectionString = Users.GetConnectionString();
      con.Open();
      OleDbCommand cmd = new OleDbCommand();
      cmd.Connection = con;
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = "INSERT INTO WorkOuts(Workout) VALUES('Abs')";
      cmd.ExecuteNonQuery();
      cmd.CommandText = "SELECT @@IDENTITY";
      return cmd.ExecuteScalar();
    }
  }
  catch (Exception ex)
  {
    System.Web.HttpContext.Current.Response.Write(ex.Message);
    System.Web.HttpContext.Current.Response.StatusCode = 500;
    return null;
  }
}

Call it by using:

int newID = (int)DB.InsertRow_ReturnKey();

Upvotes: 3

Jason Meckley
Jason Meckley

Reputation: 7591

yes, this is very common, but it will require a minimum of 3 queries, if not more since it's access.

  1. to insert the record
  2. to retrieve the id
  3. one for each muscle group (you might be able to combine these, but I doubt it)

Access is also a poor choice for a database as it lacks many of features enterprise level databases and ado.net support. Transactions being the biggest missing feature, and named parameters being the second.

if you need a "smaller" or free database solution check out SqlExpress, SqLite, or Posgress. Any of these will work since you are currently targeting Access.

Upvotes: 1

Related Questions