none
DbProviderfactory ODBC opening stored proc with parameter failure RRS feed

  • Question

  • Hi,
    I have a Microsoft Database with a stored proc that needs 1 parameter.
    I want by using odbc dbfactory get the data from the QsGate

    However i get the message back that it needs a parameter. But i did enter it. Anybody an idea what i did wrong?

        Dim ConString As String = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\somedata.mdb;Uid=Admin;Pwd=;"
    
        Dim Dbf As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.Odbc")
        Dim Con As DbConnection = Dbf.CreateConnection
        Con.ConnectionString = ConString
        Con.Open()
    
        Dim ConComm As DbCommand = Con.CreateCommand
        ConComm.CommandText = "Select * from QSGate"
    
        Dim Db As New Odbc.OdbcParameter
        Db.Value = 1
        Db.DbType = DbType.Int16
        ConComm.Parameters.Add(Db)
    
        Dim Read As DbDataReader = ConComm.ExecuteReader()
    
    
    • Moved by eryang Thursday, October 29, 2009 7:13 AM wrong forum (From:.NET Base Class Library)
    Wednesday, October 28, 2009 7:36 AM

Answers

  • I tested this and encountered the same problem. I also tested using the System.Data.Odbc library (w/o DBProviderFactory) and encountered the same problem. It's possible that the Microsoft Access ODBC driver does not support this parameter syntax. The driver is badly outdated and doesn't support as many features as Jet OLEDB. 

    I would recommend using System.Data.OleDb with the Jet OLEDB Provider instead.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Tuesday, November 3, 2009 6:34 AM
    Thursday, October 29, 2009 4:38 PM

All replies

  • Code seems confusing. You said that you have used stored procedure but code is apparently using as SQL text command having no parameters used. Still you have added one parameter to your command object.

    To the best of my guess, the above code should work fine without adding any parameter to the command object.


    Share your knowledge. It's a way to achieve immortality. - Dalai Lama MSN: kalilani@hotmail.com - Yahoo: kalilanipk
    Wednesday, October 28, 2009 12:13 PM
  • My Fault.
    The QSGate mentioned is a Query in MS Access with 1 parameter.

    The error returned is that it needs 1 parameter, but i did add this one....
    So do I have to specify the parameter in a different way?

    Wednesday, October 28, 2009 2:14 PM
  • I can't tell without analysing the actual .NET code and stored procedure code generating error. Pleae post here a sample code to generate the error.
    Share your knowledge. It's a way to achieve immortality. - Dalai Lama MSN: kalilani@hotmail.com - Yahoo: kalilanipk
    Wednesday, October 28, 2009 3:49 PM
  • Error returned is:

    ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

    Errorcode = -2146232009

    The code is as written in my first post.
    QSGate is a very simple query in MS access which contains a parameter (expression)
    Thursday, October 29, 2009 9:23 AM
  • Could you post your code with the corrected SQL statement. As Ali .NET mentioned, there is no parameter placeholder in your SQL statement.

    Select * from QSGate ' <--- Where is the parameter?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, October 29, 2009 12:23 PM
  • Why doesnt this work than?

    Dim Db As New Odbc.OdbcParameter
    Db.Value = 1
    Db.DbType = DbType.Int16
    ConComm.Parameters.Add(Db)




    Until now (hour ago) the only thing I found out that works is:

    Dim ConComm As DbCommand = Con.CreateCommand
    ConComm.CommandType = CommandType.StoredProcedure
    ConComm.CommandText = "Execute QSGate 1"


    Thursday, October 29, 2009 1:13 PM
  • If I understand your example, QSGate is actually a stored procedure (Access QueryDef) and not a table. If this is the case then you can't use the SELECT syntax to pass a parameter in this way.

    For a QueryDef the only thing that should appear in the CommandText is the name of the QueryDef. Then define your parameter(s) according to your example.

    Dim ConComm As DbCommand = Con.CreateCommand
    ConComm.CommandType = CommandType.StoredProcedure
    ConComm.CommandText = "QSGate"
    
    Dim Db As New Odbc.OdbcParameter
    Db.Value = 1
    Db.DbType = DbType.Int16
    ConComm.Parameters.Add(Db)
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Tuesday, November 3, 2009 6:34 AM
    • Unmarked as answer by VMazurModerator Tuesday, November 3, 2009 11:36 AM
    Thursday, October 29, 2009 1:26 PM
  • If I do that i get:

    ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    Only by using the text "Execute QSGate 1" it works..

    Isn't this strange?
    If I use OLE DB it works as you mentioned:

     

    Dim ConComm As DbCommand = Con.CreateCommand
    ConComm.CommandType = CommandType.StoredProcedure
    ConComm.CommandText =
    "Qsgate"
    Dim Db As New OleDb.OleDbParameter
    Db.Value = 1
    Db.DbType = DbType.Int16
    ConComm.Parameters.Add(Db)

    Thursday, October 29, 2009 3:01 PM
  • I tested this and encountered the same problem. I also tested using the System.Data.Odbc library (w/o DBProviderFactory) and encountered the same problem. It's possible that the Microsoft Access ODBC driver does not support this parameter syntax. The driver is badly outdated and doesn't support as many features as Jet OLEDB. 

    I would recommend using System.Data.OleDb with the Jet OLEDB Provider instead.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Tuesday, November 3, 2009 6:34 AM
    Thursday, October 29, 2009 4:38 PM
  • Dear Willie

    I ve faced the same problem but solved it. Try following.

    Dim ConComm As DbCommand = Con.CreateCommand
    ConComm.CommandType = CommandType.StoredProcedure

    ConComm.CommandText =

    Dim Db As New Odbc.OdbcParameter
    Db.Value = 1
    Db.DbType = DbType.Int16
    ConComm.Parameters.Add(Db)

    "Execute QSGate ?";

    Wednesday, March 9, 2011 10:17 AM