Inserting and Deleting Data with Parameters in ASP.NET - Fixing the @EmployeeID SqlParameter
(Page 4 of 4 )
We need to modify the DeleteEmployee() method to be able to send the following code, which declares the @EmployeeID as an integer instead of nvarchar parameter.
exec sp_executesql N'DELETE FROM Employees WHERE EmployeeID =
@EmployeeID',N'@EmployeeID int',@EmployeeID=11
Instead of the code:
exec sp_executesql N'DELETE FROM Employees WHERE EmployeeID =
@EmployeeID',N'@EmployeeID nvarchar(2)',@EmployeeID=N'11'
We can do that by converting the value that we assign to the @EmployeeID SqlParameter to int value, using the .NET Convert class and its Int32 method which converts the value given to it as a parameter to int data type, and that's it.
SqlParameter parameter = new SqlParameter("@EmployeeID",
Convert.ToInt32(EmployeeIDTextBox.Text));
The complete code for the method is:
private void DeleteEmployee(){
try{
using (SqlConnection connection = new SqlConnection
(connectionString)){
string commandText = "DELETE FROM Employees WHERE
EmployeeID = @EmployeeID";
SqlCommand command = new SqlCommand(commandText,
connection);
SqlParameter parameter = new SqlParameter("@EmployeeID",
Convert.ToInt32(EmployeeIDTextBox.Text));
command.Parameters.Add(parameter);
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 1){
ListBox1.Items.Clear();
PopulateList(true);
}
}
}
catch (Exception ex){
MessageLabel.Text = ex.Message;
}
}
If you want to learn more about SqlParameter objects and how they can be used in your ASP.NET web pages, please read my three article series Creating the StudentDB Class for ASP.NET 2.0.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
