Parametized queries in C#-Hard one.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parametized queries in C#-Hard one.

Author

Topic

NewGirl
Starting Member

1 Post

Posted - 2007-09-02 : 21:15:36

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.

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-09-02 : 21:40:10

From what I understand, is that your Employee table has more than 2 columns other than just Firstname and Lastname.

I think in your stored procedure, you need to pass extra blank arguments for the column indexes.

Say, if your employee table has 5 columns and you only need to save first name and last name, your stored procedure should have something like


INSERT INTO EMPLOYEES (FIRSTNAME, LASTNAME, , , ) VALUES (@FIRSTNAME, @LASTNAME, , , )

If you were to add some Rold IDs or Dept. IDs in your employee table, then before you do this, you have to get those ids from relavent tables by doing select and then put it in the above insert statement.

Also, if you are inserting data in more than one table, try using transactions to make sure that all data gets saved successfully. In your stored procedure, transactions can be implemented by writing something like


BEGIN TRANSACTION

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)

COMMIT TRANSACTION

Hope this helps.

rcurrey
Starting Member

30 Posts

Posted - 2007-09-04 : 10:03:44

If I understand you correctly, you are trying to assign the role chosen, manager chosen, etc to the employee. If this is the case, I would suggest that you populate the drop-downs in your application with the list of available roles, managers, tech skills, etc AND THEIR ID's from their tables. Then, when the user selects the values from the drop-downs, you would pass the ID's for each into your stored procedute. It would then look like:

CREATE PROCEDURE sp_InsertEmployee
@Firstname nvarchar(50),
@Lastname nvarchar(50),
@Role int,
@Manager int,
@Division int
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO EMPLOYEES (FIRSTNAME, LASTNAME, roleid, managerid, DivisionID) VALUES (@FIRSTNAME, @LASTNAME, @Role, @manager, @division)

Hope this helps

Thanks,
Rich

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources