Parameters (place-holder values)

SQL statements may contain ‘hard-coded’ values such as the following:

SELECT FieldName1, FieldName2 FROM TableName WHERE ColumnID = ‘1’ AND FieldName1 = ‘X’

..or they may contain a parameter(s) that is used as a place-holder value such as in the following:

SELECT FieldName1, FieldName2 FROM TableName WHERE ColumnID = @ColumnID AND FieldName1 = @FieldName

Using a parameter allows the SQL statement to work dynamically - that is, to take a current value and use it in the query. A parameter is preceded by the ‘@’ symbol.

When we define a parameter, we must provide further explanation as to where the real value will come from, at run time.

Note the following code sample:

Dim strConnectionString As String = "Provider=Microsoft.Ace.OLEDB.12.0;" & _

"Data Source=C:\FolderName\DatabaseName.accdb"

Dim MyConnection As New OleDb.OleDbConnection(strConnectionString)

MyConnection.Open()

Dim MyCommand As New OleDb.OleDbCommand("SELECT * FROM Customers WHERE CustomerID = @CustomerID AND LastName = @LastName", MyConnection)

MyCommand.Parameters.AddWithValue(“@CustomerID”, lblCustomerID.Text)

MyCommand.Parameters.AddWithValue(“@LastName”, lblLastName.Text)

Dim MyDataset As New Data.DataSet

Dim MyDataAdapter As New OleDb.OleDbDataAdapter(MyCommand)

MyDataAdapter.Fill(MyDataset, "MyQueryResults")

MyConnection.Close()

This select command includes two parameters - @CustomerID and @LastName. The next two statements inform the command object of where to find the actual values to use. The @CustomerID parameter’s value can be found in the text property of the label called lblCustomerID and the @LastName parameter value can be found in the text property of the label called lblLastName.

Notes on using parameters:
    •    Parameter names are preceded by the ‘@’ symbol
    •    You may name a parameter anything you like, however convention is to name the parameter strictly after the field name it represents.
    •    Each parameter must be passed to the command object’s Parameters.AddWithValue method
    •    Parameters must be passed to the command object’s Parameters.AddWithValue method in the same order as they are defined in the SQL statement.
    •    Spelling and case must match