locked
Insert records to an access table and at the same time update the junction table RRS feed

  • Question

  • User-1885804455 posted

    How do I insert records to an access table and at the same time update the junction table?
    Or rather: How do I insert records to two access tables and at the same time update the corresponding junction table?

    JunctionTable

    public partial class WebForm1 : System.Web.UI.Page
        {
            DataTable data;
            DataTable courses;
            DataTable students;
    
            String conn_string = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Me\\Documents\\AccessDatabase\\AccessTable.accdb;Persist Security Info=False";
            protected void Page_Load(object sender, EventArgs e)
            {
                data = new DataTable();
                data.Columns.Add("CompanyName", typeof(string));
                data.Columns.Add("Phone", typeof(string));
                data.Rows.Add("Foo", "12345678");
                data.Rows.Add("Bar", "87654321");
    
                courses = new DataTable();
                courses.Columns.Add("Course", typeof(string));
                courses.Rows.Add("Chemistry");
                courses.Rows.Add("Mathematics");
    
                students = new DataTable();
                students.Columns.Add("Name", typeof(string));
                students.Rows.Add("Peter");
                students.Rows.Add("Sandra"); 
            }
    
            protected void ButtonBulkInsertion_Click(object sender, EventArgs e)
            {          
                using (var connection = new OleDbConnection(conn_string))
                {
                    connection.Open();
    
                    var adapter = new OleDbDataAdapter();
    
                    adapter.InsertCommand = new OleDbCommand("INSERT INTO Courses (Course) VALUES (@Course)", connection);
                    adapter.InsertCommand.Parameters.Add("@Course", OleDbType.VarChar, 40, "Course");
    
                    adapter.Update(courses);
    
                    adapter.InsertCommand = new OleDbCommand("INSERT INTO Students (sName) VALUES (@Name)", connection);
                    adapter.InsertCommand.Parameters.Add("@Name", OleDbType.VarChar, 40, "Name");
    
                    adapter.Update(students);
    
                    // NEED TO UPDATE JUNCTION TABLE AS WELL
                }          
            }               
        }

    It works as far as adding records to Courses and Students, but how do I add primary key from each of those tables into the junction table (Courses_Students_Junction)?
    I need to get Course_ID, and Student_ID into the junction table, to tie it together.

    Sunday, March 20, 2016 3:34 AM

Answers

  • User1559292362 posted

    Hi Ptolemy,

    It works as far as adding records to Courses and Students, but how do I add primary key from each of those tables into the junction table (Courses_Students_Junction)?

    According to your description, I suppose that tables (students, couses) have a primary key with a DataType AutoNumber, you need to retrieve the AutoNumber when you insert data to database. You could add code to the RowUpdated event handler that executes a separate SELECT @@IDENTITY statement to retrieve the new Autonumber value.

    You could refer to following link for more information.

    https://msdn.microsoft.com/library/ks9f57t0(v=vs.100).aspx#Anchor_2

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 21, 2016 9:47 AM