My knowledge of databases and so on are weak, and I am quite frustrated with it, so I ask help.
If I want to add a value from one datatable to another while executing on click scenario that adds the new values all in the same time, what should I do?
So here is my scenario, I have a dataset with 3 datatables (for this issue we need only two of them, Business and Users)
Datatable 1 (Business)
- B_ID
- B_Name
- B_Pass
Datatable 2 (Users)
- User_ID
- User_Business
- User_Name
- User_Password
- User_Email
- User_Shortcode
- User_AdminRights
- User_UserRights
, I am executing the following code (my registration form registers business data and user data, implements the data seperatly to the two datatables, it works fine but I have no idea how to insert the "B_ID" to "User_Business") :
cmd = new SqlCommand("select * from Business where B_Name ='" + textBox1.Text + "'", cn);
dr = cmd.ExecuteReader();
if (dr.Read())
{
dr.Close();
MessageBox.Show("Business with this name already exists!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
dr.Close();
cmd = new SqlCommand("insert into Business values(@B_Name, @B_Password)", cn);
cmd.Parameters.AddWithValue("B_Name", textBox1.Text);
cmd.Parameters.AddWithValue("B_Password", textBox2.Text);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("insert into Users values(@User_Business, @User_Name, @User_Surname, @User_Email, @User_Password, @User_Shortcode, @User_AdminRights, @User_UserRights)", cn);
cmd.Parameters.AddWithValue("User_Business", ???); // <- the question mark indicates where I want to add the value of B_ID which is created after inserting business data
cmd.Parameters.AddWithValue("User_Name", txt_name.Text);
cmd.Parameters.AddWithValue("User_Surname", txt_surname.Text);
cmd.Parameters.AddWithValue("User_Email", txt_email.Text);
cmd.Parameters.AddWithValue("User_Password", txt_pass.Text);
cmd.Parameters.AddWithValue("User_Shortcode", txt_code.Text);
cmd.Parameters.AddWithValue("User_UserRights", 0);
cmd.Parameters.AddWithValue("User_AdminRights", 1);
cmd.ExecuteNonQuery();
MessageBox.Show("Registration succesfull!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
So at the end, I want to add the specific "B_ID" value that generates after data is inserted to Business datatable, to "User_Business" value in the Users datatable.
The code works without "User_Business" fine tho, but I am open to new suggestions, thank you.
