View Full Version : How to INSERT data into TWO separate Tables at the same time.

02-21-2012, 07:15 AM
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. :D

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);


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);


WebsiteNameTextBox.Text = "";
WebsiteDescriptionTextBox.Text = "";
WebsiteURLTextBox.Text = "";
subCategoryDropDownList2.Text = "";