how to use a stored procedure?

[Ben]

Hi,

I want to execute a stored procedure ("myquery") in Access. I use the oledb
connector.
......
Dim oConnection As System.Data.OleDb.OleDbConnection
oConnection = New System.Data.OleDb.OleDbConnection()
Dim comd As System.Data.OleDb.OleDbCommand
Dim sConnectionString As String
sConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source
= c:\mydb.mdb"
oConnection.ConnectionString = sConnectionString
oConnection.Open()
comd = New System.Data.OleDb.OleDbCommand("myquery", oConnection)
......

This doesn't work. I don't know to tell it's not an sql command..
Thanks
Ben

 

[Paul Clement]

¤ Hi,
¤
¤ I want to execute a stored procedure ("myquery") in Access. I use the oledb
¤ connector.
¤ ......
¤ Dim oConnection As System.Data.OleDb.OleDbConnection
¤ oConnection = New System.Data.OleDb.OleDbConnection()
¤ Dim comd As System.Data.OleDb.OleDbCommand
¤ Dim sConnectionString As String
¤ sConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source
¤ = c:\mydb.mdb"
¤ oConnection.ConnectionString = sConnectionString
¤ oConnection.Open()
¤ comd = New System.Data.OleDb.OleDbCommand("myquery", oConnection)
¤ ......
¤

Set the CommandType property of the OleDbCommand object to CommandType.StoredProcedure


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Ben]

Thanks for repluing.
And maybe one more question ...

How can i pass a parameter to the query?
I tried:
comd.Parameters="myvar"

but this fails ....
Thanks
Ben

 

[Paul Clement]

¤ Thanks for repluing.
¤ And maybe one more question ...
¤
¤ How can i pass a parameter to the query?
¤ I tried:
¤ comd.Parameters="myvar"
¤

Just add to the Parameters collection:

comd.Parameters.Add("@ParamName", System.Data.OleDb.OleDbType.VarWChar).Value = "SomeValue"

Keep in mind that the parameters must be added in the order they appear in the query (or are defined
in the Access QueryDef).


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Ben]

Thanks

¤ Thanks for repluing.
¤ And maybe one more question ...
¤
¤ How can i pass a parameter to the query?
¤ I tried:
¤ comd.Parameters="myvar"
¤

Just add to the Parameters collection:

comd.Parameters.Add("@ParamName",

System.Data.OleDb.OleDbType.VarWChar).Value = "SomeValue"

 

[Guest]

Keep in mind that the parameters must be added in the order they appear in the query (or are defined in the Access QueryDef).
I don't think that concept doesn't apply for System.Data.SqlClient
namespace objects though.
But since Ben's using "OleDb" objects, order of adding parmaters
matters

 

[Paul Clement]

¤ > Keep in mind that the parameters must be added in the order they appear in the query (or are defined in the Access QueryDef).
¤ I don't think that concept doesn't apply for System.Data.SqlClient
¤ namespace objects though.

I think you meant to say "I don't think that concept *does* apply...".

¤ But since Ben's using "OleDb" objects, order of adding parmaters
¤ matters

Right.


Paul
~~~~
Microsoft MVP (Visual Basic)