Parametized queries in C#-Hard one.
-
Hi there, I am using this stored procedure in sql. I have 6 tables. One is called employees. This is what I need to be able to do. A user enters a new employee into a winform, picks a role, division, manager, technicalskill set and applications from the drop down lists and hits save. The employee table should be the only one updated and has these columns only.( firstname, lastname, dvisionid, managerid, roleid,techskillsid, and appID). At the moment what is happening is its saving the firstname, lastname correctly, but the rest of the ID columns are null. It is updating the other tables with the string entered but what I need is the emplyee table to update with the corresponding ids. Is this alot more complicated then i thought? If I try to replace the role with roleid etc, it will just tell me I can't convert string to int which is understandable. How do I do this? 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 (MANAGER) VALUES (@MANAGER) INSERT INTO DIVISION:(((DIVISION) VALUES (@DIVISION) END GO:(( My C# code is like this: SqlCommand sqlC = new SqlCommand("sp_InsertEmployee", myConnection); sqlC.CommandType = CommandType.StoredProcedure; sqlC.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.VarChar, 50, "Firstname")); sqlC.Parameters.Add(new SqlParameter("@Lastname", SqlDbType.VarChar, 50, "Lastname")); sqlC.Parameters.Add(new SqlParameter("@RoleID", SqlDbType.Int, 50, "RoleID")); sqlC.Parameters.Add(new SqlParameter("@ManagerID", SqlDbType.Int, 50, "ManagerID")); sqlC.Parameters.Add(new SqlParameter("@DivisionID", SqlDbType.Int, 50, "DivisionID")); //sqlC.Parameters[0].Value = 4; sqlC.Parameters[0].Value = FirstnameText.Text; sqlC.Parameters[1].Value = Lastnametext.Text; sqlC.Parameters[2].Value = RolecomboBoxTest.Text; sqlC.Parameters[3].Value = ManagercomboBox1.Text; sqlC.Parameters[4].Value = DivisioncomboBox2.Text; int i = sqlC.ExecuteNonQuery(); //sqlC.ExecuteNonQuery(); Sorry for pasting so much.
-
Hi there, I am using this stored procedure in sql. I have 6 tables. One is called employees. This is what I need to be able to do. A user enters a new employee into a winform, picks a role, division, manager, technicalskill set and applications from the drop down lists and hits save. The employee table should be the only one updated and has these columns only.( firstname, lastname, dvisionid, managerid, roleid,techskillsid, and appID). At the moment what is happening is its saving the firstname, lastname correctly, but the rest of the ID columns are null. It is updating the other tables with the string entered but what I need is the emplyee table to update with the corresponding ids. Is this alot more complicated then i thought? If I try to replace the role with roleid etc, it will just tell me I can't convert string to int which is understandable. How do I do this? 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 (MANAGER) VALUES (@MANAGER) INSERT INTO DIVISION:(((DIVISION) VALUES (@DIVISION) END GO:(( My C# code is like this: SqlCommand sqlC = new SqlCommand("sp_InsertEmployee", myConnection); sqlC.CommandType = CommandType.StoredProcedure; sqlC.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.VarChar, 50, "Firstname")); sqlC.Parameters.Add(new SqlParameter("@Lastname", SqlDbType.VarChar, 50, "Lastname")); sqlC.Parameters.Add(new SqlParameter("@RoleID", SqlDbType.Int, 50, "RoleID")); sqlC.Parameters.Add(new SqlParameter("@ManagerID", SqlDbType.Int, 50, "ManagerID")); sqlC.Parameters.Add(new SqlParameter("@DivisionID", SqlDbType.Int, 50, "DivisionID")); //sqlC.Parameters[0].Value = 4; sqlC.Parameters[0].Value = FirstnameText.Text; sqlC.Parameters[1].Value = Lastnametext.Text; sqlC.Parameters[2].Value = RolecomboBoxTest.Text; sqlC.Parameters[3].Value = ManagercomboBox1.Text; sqlC.Parameters[4].Value = DivisioncomboBox2.Text; int i = sqlC.ExecuteNonQuery(); //sqlC.ExecuteNonQuery(); Sorry for pasting so much.
Actually, this is an easy question, about converting formats. int.TryParse is how you get an int out of a string.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
Hi there, I am using this stored procedure in sql. I have 6 tables. One is called employees. This is what I need to be able to do. A user enters a new employee into a winform, picks a role, division, manager, technicalskill set and applications from the drop down lists and hits save. The employee table should be the only one updated and has these columns only.( firstname, lastname, dvisionid, managerid, roleid,techskillsid, and appID). At the moment what is happening is its saving the firstname, lastname correctly, but the rest of the ID columns are null. It is updating the other tables with the string entered but what I need is the emplyee table to update with the corresponding ids. Is this alot more complicated then i thought? If I try to replace the role with roleid etc, it will just tell me I can't convert string to int which is understandable. How do I do this? 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 (MANAGER) VALUES (@MANAGER) INSERT INTO DIVISION:(((DIVISION) VALUES (@DIVISION) END GO:(( My C# code is like this: SqlCommand sqlC = new SqlCommand("sp_InsertEmployee", myConnection); sqlC.CommandType = CommandType.StoredProcedure; sqlC.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.VarChar, 50, "Firstname")); sqlC.Parameters.Add(new SqlParameter("@Lastname", SqlDbType.VarChar, 50, "Lastname")); sqlC.Parameters.Add(new SqlParameter("@RoleID", SqlDbType.Int, 50, "RoleID")); sqlC.Parameters.Add(new SqlParameter("@ManagerID", SqlDbType.Int, 50, "ManagerID")); sqlC.Parameters.Add(new SqlParameter("@DivisionID", SqlDbType.Int, 50, "DivisionID")); //sqlC.Parameters[0].Value = 4; sqlC.Parameters[0].Value = FirstnameText.Text; sqlC.Parameters[1].Value = Lastnametext.Text; sqlC.Parameters[2].Value = RolecomboBoxTest.Text; sqlC.Parameters[3].Value = ManagercomboBox1.Text; sqlC.Parameters[4].Value = DivisioncomboBox2.Text; int i = sqlC.ExecuteNonQuery(); //sqlC.ExecuteNonQuery(); Sorry for pasting so much.
falles01 wrote:
At the moment what is happening is its saving the firstname, lastname correctly, but the rest of the ID columns are null.
Of course they are. You don't specify any values for them.
falles01 wrote:
It is updating the other tables with the string entered but what I need is the emplyee table to update with the corresponding ids. Is this alot more complicated then i thought?
As you obviously thought that it would happen automatically, yes, it's a lot more complicated than that. You have to add the other values first, and get the id that is created for each record:
declare @RoleId int insert into [ROLE] ([ROLE]) values (@ROLE) set @RoleId = scope_identity()Then you can use these values when you insert into EMPLOYEES.--- single minded; short sighted; long gone;
-
falles01 wrote:
At the moment what is happening is its saving the firstname, lastname correctly, but the rest of the ID columns are null.
Of course they are. You don't specify any values for them.
falles01 wrote:
It is updating the other tables with the string entered but what I need is the emplyee table to update with the corresponding ids. Is this alot more complicated then i thought?
As you obviously thought that it would happen automatically, yes, it's a lot more complicated than that. You have to add the other values first, and get the id that is created for each record:
declare @RoleId int insert into [ROLE] ([ROLE]) values (@ROLE) set @RoleId = scope_identity()Then you can use these values when you insert into EMPLOYEES.--- single minded; short sighted; long gone;
Thank you, but I decided to just use my insert statement for now as that works. My question now is how exactly do I get selected values of a checkedlist box and store it as ID numbers in the database. I had something like this which is obviously wrong. It works for the comboboxes but then I heard you need for loops to check multiple selections in a checkedlistbox. Feel free to just change my code. if (this.techSkillsCheckListBox2.CheckedItems.Count > 0) { label1.Text = "worked"; :confused: foreach (string item in this.techSkillsCheckListBox2.CheckedItems) { string sql = "INSERT INTO employees(Firstname,Lastname,RoleID,DivisionID,ManagerID,TechnicalSkillsID) Values ('" + FirstnameText.Text.ToString() + "' , '" + Lastnametext.Text.ToString() + "' , " + RolecomboBoxTest.SelectedValue.ToString() + "," + DivisioncomboBox2.SelectedValue.ToString() + " , " + ManagercomboBox1.SelectedValue.ToString() + " , " + techSkillsCheckListBox2.SelectedValue.ToString() + ")"; Don't worry this won't be used commercially so I was told not to worry about injection attacks until I can get these correct. Thank you for helping out.
-
Thank you, but I decided to just use my insert statement for now as that works. My question now is how exactly do I get selected values of a checkedlist box and store it as ID numbers in the database. I had something like this which is obviously wrong. It works for the comboboxes but then I heard you need for loops to check multiple selections in a checkedlistbox. Feel free to just change my code. if (this.techSkillsCheckListBox2.CheckedItems.Count > 0) { label1.Text = "worked"; :confused: foreach (string item in this.techSkillsCheckListBox2.CheckedItems) { string sql = "INSERT INTO employees(Firstname,Lastname,RoleID,DivisionID,ManagerID,TechnicalSkillsID) Values ('" + FirstnameText.Text.ToString() + "' , '" + Lastnametext.Text.ToString() + "' , " + RolecomboBoxTest.SelectedValue.ToString() + "," + DivisioncomboBox2.SelectedValue.ToString() + " , " + ManagercomboBox1.SelectedValue.ToString() + " , " + techSkillsCheckListBox2.SelectedValue.ToString() + ")"; Don't worry this won't be used commercially so I was told not to worry about injection attacks until I can get these correct. Thank you for helping out.
falles01 wrote:
It works for the comboboxes but then I heard you need for loops to check multiple selections in a checkedlistbox.
If you have a control that allows mutiple selections, then you have to use the SelectedItems property instead. You also have to change your database design so that it allows for multiple values.
--- single minded; short sighted; long gone;
-
falles01 wrote:
It works for the comboboxes but then I heard you need for loops to check multiple selections in a checkedlistbox.
If you have a control that allows mutiple selections, then you have to use the SelectedItems property instead. You also have to change your database design so that it allows for multiple values.
--- single minded; short sighted; long gone;
It doesn't allow multiple selections though. Okay well apart from the database side of things I believe I have to use a 'for' loop. why does the following return an error?: The name 'Checked' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. foreach (int indexChecked in techSkillsCheckListBox2.CheckedIndices) { string sql = "INSERT INTO employees(Firstname,Lastname,RoleID,DivisionID,ManagerID,TechnicalSkillsID) Values ('" + FirstnameText.Text.ToString() + "' , '" + Lastnametext.Text.ToString() + "' , " + RolecomboBoxTest.SelectedValue.ToString() + "," + DivisioncomboBox2.SelectedValue.ToString() + " , " + ManagercomboBox1.SelectedValue.ToString() + " , " + techSkillsCheckListBox2.GetItemCheckState(indexChecked).ToString() + ")"; SqlCommand sqlC = new SqlCommand(sql, myConnection); } } Anyone. I have been baffled by this for 8 hours. Its especially annoying when it was so easy to work out the combobox selections. Thanks again.