DbProviderfactory ODBC opening stored proc with parameter failure

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()

Answer 1

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: [e-mail] - Yahoo: kalilanipk

Answer 2

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?

Answer 3

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: [e-mail] - Yahoo: kalilanipk

Answer 4

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)

Answer 5

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)

Answer 6

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"

Answer 7

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)

Answer 8

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)

Answer 9

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)