Using SqlParameter Class

Represents a parameter to a SqlCommand and optionally its mapping to DataSet columns. This class cannot be inherited.

You should use parameters to filter queries in a secure manner. But I recommend to use parameters when you try to pass the datetime value in your query.

The process of using parameter contains two steps:

• create SqlParameter object and insert there value with applicable properties
• define the parameter in the SqlCommand command string, and assign the SqlParameter object to the SqlCommand object. When the SqlCommand executes, parameters will be replaced with values specified by the SqlParameter object.

Sample Code

Imports Namespace: System.Data.SqlClient

' Insert string
Dim sql As String = " INSERT INTO tblZipCode([ZIPCODE], [STATE], [CITY], [TestDate]) VALUES(@ZIPCODE, @STATE, @CITY), @TestDate"

' Create sql parameter
Dim param(3) As SqlParameter

param(0) = New SqlParameter("@ZIPCODE", SqlDbType.VarChar)
param(0).Value = "60000"

param(1) = New SqlParameter("@STATE", SqlDbType.VarChar)
param(1).Value = "Statename"

param(2) = New SqlParameter("@CITY", SqlDbType.VarChar)
param(2).Value = "Cityname"

' Recommend to use sql param when you try to send datetime value
param(3) = New SqlParameter("@TestDate", SqlDbType.DateTime)
param(3).Value = DateTime.Now

' Create Connection string
Dim sConnection As New SqlConnection("server=(local);uid=sa;pwd=pass;database=db")
sConnection.Open()

' Create Sql Command
Dim command As SqlCommand = sConnection.CreateCommand()
command.CommandText = sql

' Add Parameter to command
command.Parameters.AddRange(param)

' Execute command
Dim nResult As Integer = command.ExecuteNonQuery()

If nResult > 0 Then
Console.WriteLine("Insert completed")
End If

sConnection.Close()
command.Dispose()