Insert statment for multiple tables
-
betetr you use trigers (insert) to insert data in all table based on the employee table
<>
Thanks. I'm after any example you can give me. Could you possibly give me an example. I tried the following but the whole injection attack thing. I think the ands could be wrog. Please feel free to string sql = "INSERT INTO employees(Firstname,Lastname) Values ('" + FirsttextBox.Text.ToString() + "' , '" + LasttextBox.Text.ToString() + "' and Insert into Role (Role) Values ('" + RolecomboBox1.Text.ToString() + "')Insert into Manager (MFirstname) Values ('" + ManagercomboBox1.Text.ToString() + "') Insert into Division (DivisionName) Values ('" + DivisioncomboBox1.Text.ToString() + "')"; :confused:
-
Your best solution would be to create a stored procedure and call it within your code "ExecuteNonQuery." The stored procedure will do all the work of inserting the records into the proper tables. As far as I know, there is not a way, with SQL Server, to insert into multiple tables with one INSERT statement. This also gives you the ability to do error checking on the back-end.
-
Your best solution would be to create a stored procedure and call it within your code "ExecuteNonQuery." The stored procedure will do all the work of inserting the records into the proper tables. As far as I know, there is not a way, with SQL Server, to insert into multiple tables with one INSERT statement. This also gives you the ability to do error checking on the back-end.
I've also been advised that I can do this by creating multiple insert statements in the same block. Is this possible? If so how? string sql = sql = sql= etc etc?????? Is this how you would do it? To me it would insert the data but it won't be related to each other. EG. I need to update the division, manager, role table when I insert a new employee. :~
-
Thanks, I do have the statement in the nonexecutequery block already. Is it possible to give me a little exampypoos???;)
CREATE PROCEDURE sp_InsertEmployee @Firstname nvarchar(50), @Lastname nvarchar(50), @Role nvarchar(50), @Manager nvarchar(50), @Division nvarchar(50) AS BEGIN SET NOCOUNT ON; INSERT INTO EMPLOYEES (FIRSTNAME, LASTNAME) VALUES (@FIRSTNAME, @LASTNAME) INSERT INTO [ROLE] ([ROLE]) VALUES (@ROLE) INSERT INTO MANAGER (MFIRSTNAME) VALUES (@MANAGER) INSERT INTO DIVISION (DIVISIONNAME) VALUES (@DIVISION) END GO
-
CREATE PROCEDURE sp_InsertEmployee @Firstname nvarchar(50), @Lastname nvarchar(50), @Role nvarchar(50), @Manager nvarchar(50), @Division nvarchar(50) AS BEGIN SET NOCOUNT ON; INSERT INTO EMPLOYEES (FIRSTNAME, LASTNAME) VALUES (@FIRSTNAME, @LASTNAME) INSERT INTO [ROLE] ([ROLE]) VALUES (@ROLE) INSERT INTO MANAGER (MFIRSTNAME) VALUES (@MANAGER) INSERT INTO DIVISION (DIVISIONNAME) VALUES (@DIVISION) END GO
Thanks so much. Is this case sensitive. You have all the names correct so I copied and pasted this before my executenonquery statment and there are red lines all over it. Okay I feel really dumb about now. It came up with ; errors so I'm guessing that I don't just copy exactly what you have done? Oh god. I'm waiting to be told off. ;)
-
Thanks so much. Is this case sensitive. You have all the names correct so I copied and pasted this before my executenonquery statment and there are red lines all over it. Okay I feel really dumb about now. It came up with ; errors so I'm guessing that I don't just copy exactly what you have done? Oh god. I'm waiting to be told off. ;)
-
That was a SQL statement for creating a stored procedure in SQL Server. Once you create it in SQL Server, you can then call it using a class like SqlDataReader and use the method ExecuteNonQuery. Don't forget to add your parameters!
Don't forget to use "Transaction" when you are trying to manipulate data from more than one table...
Thanks and Regards, Michael Sync ( Blog: http://michaelsync.net) If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message". Why vote? Plz Read it here. Thank you. :)
-
betetr you use trigers (insert) to insert data in all table based on the employee table
<>
A trigger may not always be the best solution. You have to look at performance and other aspects also.
only two letters away from being an asset
-
Don't forget to use "Transaction" when you are trying to manipulate data from more than one table...
Thanks and Regards, Michael Sync ( Blog: http://michaelsync.net) If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message". Why vote? Plz Read it here. Thank you. :)
As I read the thread, I was wondering about exactly this.
Cheers, Vıkram.
Be yourself, no matter what they say. - Sting, Englishman in New York.
-
That was a SQL statement for creating a stored procedure in SQL Server. Once you create it in SQL Server, you can then call it using a class like SqlDataReader and use the method ExecuteNonQuery. Don't forget to add your parameters!
-
Don't forget to use "Transaction" when you are trying to manipulate data from more than one table...
Thanks and Regards, Michael Sync ( Blog: http://michaelsync.net) If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message". Why vote? Plz Read it here. Thank you. :)
-
That was a SQL statement for creating a stored procedure in SQL Server. Once you create it in SQL Server, you can then call it using a class like SqlDataReader and use the method ExecuteNonQuery. Don't forget to add your parameters!
Okay I apologise for pasting so much info but I've done as you said. My code looks like this, but I get the null error which I've shown you at the bottom. If you can help, I'll vote you high for every message you've sent to me today. :rose: I notice that I got this same error when I was using my normal insert statement. what am I doing wrong you think? SqlCommand sqlC = new SqlCommand("sp_InsertEmployee", myConnection); sqlC.CommandType = CommandType.StoredProcedure; sqlC.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.VarChar, 0, "Firstname")); sqlC.Parameters.Add(new SqlParameter("@Lastname", SqlDbType.VarChar, 50, "Lastname")); sqlC.Parameters.Add(new SqlParameter("@Role", SqlDbType.VarChar, 0, "Role")); sqlC.Parameters.Add(new SqlParameter("@Manager", SqlDbType.VarChar, 50, "Manager")); sqlC.Parameters.Add(new SqlParameter("@Division", SqlDbType.VarChar, 0, "Division")); //sqlC.Parameters[0].Value = 4; sqlC.Parameters[0].Value = FirsttextBox.Text.ToString(); sqlC.Parameters[1].Value = LasttextBox.Text.ToString(); sqlC.Parameters[2].Value = RolecomboBox1.Text.ToString(); sqlC.Parameters[3].Value = ManagercomboBox1.Text.ToString(); sqlC.Parameters[4].Value = DivisioncomboBox1.Text.ToString(); int i = sqlC.ExecuteNonQuery(); Error::::Cannot insert the value NULL into column 'RoleID', table 'Dev_RST.dbo.employees'; column does not allow nulls. INSERT fails. The statement has been terminated.
-
That was a SQL statement for creating a stored procedure in SQL Server. Once you create it in SQL Server, you can then call it using a class like SqlDataReader and use the method ExecuteNonQuery. Don't forget to add your parameters!
Okay it works only slightly but what I notice from this is that the firstname and lastname are update in employees but I've noticed also that I just want the ID's corresponding to that name to be updated also in the employee table istead of saving new entries into the other tables. For example there are now multiple roles called developer, or graduate just with different ID's when what I actually need to do is just update the employee table with the new person and the ID that corresponds to their selected role. Thanks and sorry for being a pain.
-
Okay it works only slightly but what I notice from this is that the firstname and lastname are update in employees but I've noticed also that I just want the ID's corresponding to that name to be updated also in the employee table istead of saving new entries into the other tables. For example there are now multiple roles called developer, or graduate just with different ID's when what I actually need to do is just update the employee table with the new person and the ID that corresponds to their selected role. Thanks and sorry for being a pain.
In reponse to Michael & Vikram, yes, you should be using transactions on multiple statements. The example I provided to you was based on your original post. If your intentions are not alwasy to insert records, then you will need to modify the stored procedure to check for the Roles, Managers, etc. If found then update your employee table. You will need to know SQL. If you are using a Listbox or Combobox, you can also save the PK in the control collection and just pass that to your stored procedure. There are many ways to kill this bird, it just depends on your requirements.
-
In reponse to Michael & Vikram, yes, you should be using transactions on multiple statements. The example I provided to you was based on your original post. If your intentions are not alwasy to insert records, then you will need to modify the stored procedure to check for the Roles, Managers, etc. If found then update your employee table. You will need to know SQL. If you are using a Listbox or Combobox, you can also save the PK in the control collection and just pass that to your stored procedure. There are many ways to kill this bird, it just depends on your requirements.
Okay but what about my last question. how do I update the employee table wiht the ID corresponding the combobox selection. EG. Employee table has empID, RoleID, DivisioID etc. But then I have a Role table with roleid description and role, and division table with divisionid, division etc. When a user enters a new employee name, and selects role for example, the employee table should update with a new name and put the correct roleID against the role. Obviously I'm not good with sql at all. :~