DOC: Correction to Using Parameters with an OLEDBCommand (316744)

SUMMARY

The following text from the Microsoft .NET Framework Developer's Guide has a documentation error:

Using Parameters with an OleDbCommand

When using parameters with an OleDbCommand, the names of the parameters added to the OleDbParameterCollection must match the names of the parameter markers in your stored procedure. The OLE DB .NET Data Provider treats parameters in the stored procedure as named parameters and searches for the matching parameter markers.

To view this documentation error, please view the following Microsoft "Using Stored Procedures with a Command" Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.asp

MORE INFORMATION

Contrary to what the preceding documentation error describes, the OleDbCommand parameters are positional when they are used with the Microsoft SQL Server OLE DB provider. The names of the parameters can be arbitrary, but this is not true with SqlCommand. The order of the parameters that you add to the OleDbParameterCollection must match the order of the parameters in your stored procedure.

You can use named parameters out of order with the SQL Managed Provider. With the SQL Managed provider you cannot use arbitrary names; the names of the parameters must match the parameter names in the stored procedure.

Steps to Reproduce the Behavior

Start Microsoft Visual Studio .NET.
Create a new Windows program in Visual Basic .NET.
Add the following statements at the very top of the code window for the default form:
```
Imports System.Data
Imports System.Data.OleDb
					
```

Add the following code to the Load event handler of Form1:

Dim cn As OleDb.OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=YourServer;User ID=YourUser;Password=YourPassword;Initial Catalog=Northwind")

cn.Open()
Dim cmd As OleDb.OleDbCommand = New OleDbCommand("[Sales By Year]", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim p1 As OleDbParameter = cmd.Parameters.Add("@Beginning_Date", OleDbType.DBDate)
Dim p2 As OleDbParameter = cmd.Parameters.Add("@Ending_Date", OleDbType.DBDate)
' Comment out the 2 above lines and uncomment the following 2 lines
' to demonstrate that OLEDBParameters must be in same order as in Stored procedure
'Dim p2 As OleDbParameter = cmd.Parameters.Add("@Ending_Date", OleDbType.DBDate)
'Dim p1 As OleDbParameter = cmd.Parameters.Add("@Beginning_Date", OleDbType.DBDate)
p1.Value = "1/1/1997"
p2.Value = "1/1/1998"
Dim dr As OleDbDataReader = cmd.ExecuteReader()
Debug.WriteLine(dr.GetName(0), dr.GetName(1))

Do While dr.Read()
   Debug.WriteLine(dr(0))
Loop
dr.Close()
cn.Close()

Change the connection string in the declaration for cn to reflect your SQL Server computer name and credentials.
Press F5 to compile and then run the project. Press CTRL+ALT+O to view the results of the stored procedure in the output window. You see about 400 records from the Northwind database.

Comment out the following lines of code:

Dim p1 As OleDbParameter = cmd.Parameters.Add("@Beginning_Date", OleDbType.DBDate)
Dim p2 As OleDbParameter = cmd.Parameters.Add("@Ending_Date", OleDbType.DBDate)

and uncomment the following lines of code

'Dim p2 As OleDbParameter = cmd.Parameters.Add("@Ending_Date", OleDbType.DBDate)
'Dim p1 As OleDbParameter = cmd.Parameters.Add("@Beginning_Date", OleDbType.DBDate)

Press F5 to compile and then run the project. Press CTRL+ALT+O to view the results of the stored procedure in the output window. Now you cannot see any records in the output window. Because Ending_Date is first, it is being treated as the first parameter in the stored procedure, (Beginning_Date).

Make sure that the order that parameters are added to the OleDbParameterCollection matches the order of the parameters in your stored procedure.