none
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???

    Thursday, October 19, 2006 5:50 PM

Answers

  • 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

    Thursday, October 19, 2006 6:02 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 12:55 PM

All replies

  • 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

    Thursday, October 19, 2006 6:02 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.

    Thursday, October 19, 2006 6:03 PM
  • 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 9:50 AM

  • 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 12:55 PM
  • 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 4:03 PM
    Moderator
  • 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.
    Saturday, October 21, 2006 5:14 PM

  • 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.  

    Sunday, October 22, 2006 2:33 AM