How to INSERT data into TWO separate Tables at the same time.

[robuc]

Hello All;

I am trying to INSERT data from a form into a MS ACESS Database. I have tested parts of the CODE behind [just inserting data to one table only] and it worked. Now, i would like to improve on it... and this is where i need your help. I would like to insert data to TWO different TABLES with one action/event... IN FACT, i would like to do THREE things:

1. insert the new USER info to the "User table"... hence , creating a new user account.
2. then, RETRIEVE the new user ID
3. then, insert the new user's URL info into the "Url table" INCLUDING the user_ID [this is an Int.] i just retrieved.

4. i and also trying to pass a value that was NOT in the from into the "User table", this is a Integer.

Please REVIEW the code below AND let me know what i need to do to make it work.

Thanks.

protected void SubmitURLButton_Click(object sender, EventArgs e)
        {

            CountryDataSqlDataSource1.InsertCommandType = SqlDataSourceCommandType.Text;
            CountryDataSqlDataSource1.InsertCommand = "Insert into User (user_Approved,user_FirstName,user_LastName,user_Email,user_Country) VALUES ('1', @user_FirstName, @user_LastName, @user_Email, @user_Country, @user_City)";
           
            CountryDataSqlDataSource1.InsertParameters.Add("user_FirstName", YourFirstNameTextBox.Text);
            CountryDataSqlDataSource1.InsertParameters.Add("user_LastName", YourLastNameTextBox.Text);
            CountryDataSqlDataSource1.InsertParameters.Add("user_Email", YourEmailTextBox.Text);
            CountryDataSqlDataSource1.InsertParameters.Add("user_Country", YourCountryDropDownList1.Text);
            CountryDataSqlDataSource1.InsertParameters.Add("user_City", YourCityTextBox.Text);

            CountryDataSqlDataSource1.Insert();

            YourFirstNameTextBox.Text = "";
            YourLastNameTextBox.Text = "";
            YourEmailTextBox.Text = "";
            YourCountryDropDownList1.Text = "";
            YourCityTextBox.Text = "";

            {
                //Pull new user ID from the USER TABLE
                {
                    subCategoryDataSqlDataSource1.InsertCommandType = SqlDataSourceCommandType.Text;
                    subCategoryDataSqlDataSource1.InsertCommand = "Insert into url (url_user_ID,url_SiteName,url_Description,url_URL,url_Category) VALUES (@url_user_ID, @url_SiteName, @url_Description, @url_URL, @url_Category)";

                    subCategoryDataSqlDataSource1.InsertParameters.Add("url_SiteName", WebsiteNameTextBox.Text);
                    subCategoryDataSqlDataSource1.InsertParameters.Add("url_Description", WebsiteDescriptionTextBox.Text);
                    subCategoryDataSqlDataSource1.InsertParameters.Add("url_URL", WebsiteURLTextBox.Text);
                    subCategoryDataSqlDataSource1.InsertParameters.Add("url_Category", subCategoryDropDownList2.Text);

                    subCategoryDataSqlDataSource1.Insert();
                  
                    WebsiteNameTextBox.Text = "";
                    WebsiteDescriptionTextBox.Text = "";
                    WebsiteURLTextBox.Text = "";
                    subCategoryDropDownList2.Text = "";

                }
            }


        }
  
    }
}