Creating Parameterized queries with ASP.NET and SQL Server 2000
I cannot stress enough the importance of using Parameteried queries in ASP.NET. Apart from not having to worry about escaping quotes and other xtra characters your app is saved from SQL Injection attack.
So knowing how important it is to use Parameterized queries I havent seen any sample article using SQL 2000. So I decided to write up one.
Dim
myCommand As SqlCommand
Dim
myParam As SqlParameter
myCommand
= New SqlCommand()
myCommand.Connection
= objcon
myCommand.CommandText
= "UPDATE Products SET ProductName=@ProductName AND Quantity=@Quantity WHERE Productid=@Productid"
myCommand.Parameters.Add(New SqlParameter("@ProductId",SqlDbType.Int))
myCommand.Parameters(
"@ProductId").Value = 25
myCommand.Parameters.Add(
New SqlParameter("@ProductName",SqlDbType.VarChar,50))
myCommand.Parameters(
"@ProductName").Value = "New Product"
myCommand.Parameters.Add(
New SqlParameter("@Quantity",SqlDbType.int))
myCommand.Parameters(
"@Quantity").Value = 100
Try
If objCon.State = 0 Then objCon.Open()
mycommand.ExecuteNonQuery()
Catch
exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
End
Try
(2) If you are using a stored proc and have any return values that you are expecting back from the stored proc in the form of OUTPUT parameters, you would also need to add the OUTPUT parameter in as :
Dim myCommand As SqlCommand
Dim
myParam As SqlParameter
myCommand
= New SqlCommand()
myCommand.Connection
= objcon
myCommand.CommandText
= "usp_testproc"
myCommand.CommandType
= CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@ProductName",SqlDbType.VarChar,50))
myCommand.Parameters(
"@ProductName").Value = "New Product"
myCommand.Parameters.Add(New SqlParameter("@Quantity",SqlDbType.int))
myCommand.Parameters("@Quantity").Value = 100
myCommand.Parameters.Add(New SqlParameter("@ProductId",SqlDbType.Int))
myCommand.Parameters.Direction = ParameterDirection.Output
Try
If objCon.State = 0 Then objCon.Open()
mycommand.ExecuteNonQuery()
Response.Write(Convert.ToInt16(mycommand.Parameters("@ProductId").Value))
Catch
exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
End
Try
Thats it.
Happy programming !!!