oledb parameters

Question

 

how to use oledb parameters in oledb command object

I declare the oledb variables & others like this

dim cmd as new oledb.OledbCommand

dim prn as new oledb.oledbParamter

prn.ParameterName = "@para"

prn.OledbType = varchar

prn.value = "" 'say a string

'etc etc

-------------

then i add this paramter in the command object

cmd.parameters.add(prn)

cmd.commandText = "select * from table where field = @para"

cmd.connection = cnn ' say a connection object

cmd.ExecuteNonScalar    ' I just need one column actually as the query is supposed to return one row only

*********************************************************************

when this code is executed, the system gives the following error,

"Variable @para not declared" or "You must declare the variable @para"

why this error is coming this is the question?????

is there any thing left???

Answer 1

Hi

What database are you using?

I tried the following code (based on yours) to retrieve the CustomerID field from the Northwinds Access database and it works fine:

        Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=G:\Northwind.mdb")

        Dim cmd As New OleDb.OleDbCommand

 

        Dim prn As New OleDbParameter

 

        prn.ParameterName = "@para"

        prn.OleDbType = OleDbType.VarChar

        prn.Value = "ALFKI" 'say a string

 

        cmd.Parameters.Add(prn)

        cmd.CommandText = "select * from Customers where CustomerID = @para"

        cmd.Connection = cnn ' say a connection object

 

        cnn.Open()

        Dim customerID As String = cmd.ExecuteScalar().ToString

        cnn.Close()

 

        MessageBox.Show(customerID)

The customerID string fields returns "ALFKI" which is the first column of the first row returned.

If this does not help, then you could try chaning your select statement to using the ? place holder rather than a named parameter and ommitting the prn.ParameterName line of code.

 

HTH

Answer 2

The OLEDB library does not support named parameters. Parameters are processed according to their ordinal position in the Parameter collection.

In your SQL string you should replace @para with a question mark. The actual parameter object does not require a name.

Answer 3

yes u r iright paul

but what if we use multiple parameters in a query

what if one para is used multiple times in the same query with other paras too,

how does the system understad this???

Answer 4

You can use multiple parameters in a SQL statement but each one should be represented by a question mark symbol (parameter placeholder) and not a name. During execution each parameter placeholder in the SQL statement is replaced by a corresponding parameter that has been added to the Parameter collection for the Command object.

So for example, if you have a SQL statement with four parameters then the Parameter collection must also have four parameters. The first parameter placeholder in the SQL statement (reading reading from the start of the SQL string) is replaced by the value of the first parameter in the Parameters collection, the second parameter placeholder is replaced by the second parameter in the Parameters collection, and so on. You cannot use a parameter in the Parameter collection for more than one parameter placeholder in the SQL statement.

Unfortunately this is the limitation when using OLEDB under ADO.NET.

Answer 5

Most likely provider (nor .NET OLEDB provider) does NOT support named parameters. Everything depends on OLEDB provider's capabilities. If I remember correctly OLEDB Provider for SQL Server supports both named paramters and question mark parameters in queries. Other providers could support only question marks and in this case you need to define your query like

select * from table where field = ?

In this case all the parameters will be processed in exact same order as your application adds them to the collection of the parameters.

Answer 6

I was under the impression that you can use named parameters with both Microsoft SQL Server and Microsoft Access. I understand that Microsoft Access doesn't understand Named parameters but if the parameters are added to the collection in the same order as defined in  the statmeent then it doesn't matter whether they are marked by placeholders or named parameters. This is especially useful when working with the DbProviderFactory and DbProviderFactories classes as you can write generic code to work with both Microsoft Access and Microsoft SQL Server as long as each parameter declared in an SQL Statement is added to the parameters collection in the same order.

Answer 7

I would have to double check but I believe that Jet OLEDB (and Access) does support named parameters. However, the .NET OLEDB library does not.

You can specify parameter names when using the .NET OLEDB library, such as for the purpose of code documentation, but they are otherwise ignored during the processing of the statement.