Thursday, October 19, 2006 5:50 PM
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
then i add this paramter in the command object
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???
Thursday, October 19, 2006 6:02 PM
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.CommandText = "select * from Customers where CustomerID = @para"
cmd.Connection = cnn ' say a connection object
Dim customerID As String = cmd.ExecuteScalar().ToString
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.
Thursday, October 19, 2006 6:03 PM
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.
Saturday, October 21, 2006 9:50 AM
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???
Saturday, October 21, 2006 12:55 PM
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.
Saturday, October 21, 2006 4:03 PMModerator
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.
Saturday, October 21, 2006 5:14 PMI 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.
Sunday, October 22, 2006 2:33 AM
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.