Parameters Collection Editor

  • Article

Allows you to create and configure parameters (OleDbParameter, SqlParameter, OdbcParameter, and OracleParameter objects) associated with a SQL statement or stored procedure in TableAdapters and data adapters.

Editing the Parameters Collection

The Parameters Collection Editor is displayed when you are using the Properties window to configure a TableAdapter in the Dataset Designer, or a data adapter on a form or component.

Select a TableAdapter on the Dataset Designer surface and select the Parameters property in the Properties window to open the Parameters Collection Editor.

  1. Open the node for the SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties.

  2. Edit the Parameters property to open the Parameters Collection Editor.

Note

If you use data design tools such as the TableAdapter Configuration Wizard, or the Data Adapter Configuration Wizard to configure adapters, the parameters for the adapter are configured automatically.

Parameter Naming

The statements or stored procedures in these commands can be defined with parameter placeholders.

If you are using an OleDbDataAdapter object, a statement uses a question mark as a placeholder and might look like this:

Select * From Customers Where City = ?

If you are using a SqlDataAdapter object, a statement that uses named parameters might look like this:

Select * From Customers Where City = @City

If you are using an OracleDataAdapter object, a statement that uses Oracle named parameters might look like this:

SELECT * FROM Customers WHERE CustomerID = :pCustomerID

Note

For Oracle, when using named parameters in an SQL statement or stored procedure, you must precede the parameter name with a colon (:). However, when referring to a named parameter elsewhere in your code (for example, when calling the Add method), do not precede the named parameter with a colon (:). The data provider supplies the colon automatically.

Adding Parameters

The Parameters collection for a command (for example, Parameters) contains a list of parameter objects that are used at run time to pass values to the statements or stored procedures. Thus, there is a parameter object for each parameter value that needs to be passed to a command.

The editor is divided into two panes. In the left pane you add and remove parameters, and in the right pane you set properties for an individual parameters, among them:

  • ParameterName property, by which you can refer to the parameter in code.

    Note

    The SqlDataAdapter class requires named parameters, so in SqlParameter objects, the name must always be preceded with "@".

  • SourceColumn property, which identifies where in a data record the value of the parameter is coming from. This value is used during updates; the data adapter passes the value of a specific column to the update command.

  • SourceVersion property, which is used in conjunction with the SourceColumn property. If a record has changed in the dataset, multiple copies of the record are available, including the original value (as read from the data source) and current value (after changes). When specifying a column value to pass as a parameter, you select which of the two values to pass. Typically, the original value is used as a parameter in a WHERE clause to identify the record in the database, and the current value is used in SET or VALUES clauses.

  • Value property, which is an explicit literal value to be passed as the parameter. Setting this property overrides the SourceColumn property; the two properties are mutually exclusive.

User Interface Element List

  • Add
    Creates a new parameter in the collection. By default, the table is named Parametern, where n is a sequential number. You can rename the table by setting its ParameterName property in the properties grid.

    Note

    The OleDbParameter Collection Editor does not check that you are creating the correct number of parameters for the command, or that they are of the correct type.

    Parameters are matched to parameter placeholders in the statements or commands by position. Therefore, be sure that the parameters are ordered properly in the collection.

  • Remove
    Deletes the selected parameter from the collection.

See Also

Reference

SqlParameter

Concepts

Binding Windows Forms Controls to Data in Visual Studio

Preparing Your Application to Receive Data

Fetching Data into Your Application

Binding Controls to Data in Visual Studio

Editing Data in Your Application

Validating Data

Saving Data

Other Resources

TableAdapters

Connecting to Data in Visual Studio