Parameterized SQL statements with ADO.Command object 
Author Message
 Parameterized SQL statements with ADO.Command object

I'm trying to execute this code and this is the error message I get:

"-2147217839 (80040e51)  The provider cannot derive parameter info and
SetParameterInfo has not been called"

    Dim cmdCommand As New Command
    Dim rsRecSet As New Recordset
    cmdCommand.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=M:\DEV\LAMA\VBfiles\test.mdb;"
    cmdCommand.CommandText = "SELECT ShapeBase,ShapeHeight,ShapeForm FROM ShapeTable WHERE ShapeName= ?"
    cmdCommand.Prepared = True
    cmdCommand.Parameters(0).value = GetName
    Set shape = SQLConnection.Execute

Can anyone help med please! Thanks alot!

//Kristoffer Lindn

-----------------** -- Posted from CodeGuru -- **-----------------
http://www.*-*-*.com/ ;       The website for VB programmers.



Sun, 23 Dec 2001 03:00:00 GMT  
 Parameterized SQL statements with ADO.Command object
I think you're trying to over-complicate the code.  You don't need to
use the Parameter object with dynamic SQL
Try..

    Dim cmdCommand As New Command
    Dim rsRecSet As Recordset
    cmdCommand.ActiveConnection =
"Provider=Microsoft.Jet.OLEDB.3.51;Data Source=M:
\DEV\LAMA\VBfiles\test.mdb;"
    cmdCommand.CommandText = "SELECT ShapeBase,ShapeHeight,ShapeForm
FROM ShapeTable WHERE ShapeName=" & GetName
   cmdCommand.Prepared = True
   Set rsRecSet = SQLConnection.Execute

Also, if you do use the parameter object, you must do it thus..

    Dim cmdCommand as New Command
    Dim adoParam As Parameter
    Dim rsRecSet As RecordSet

    cmdCommand.ActiveConnection =
"Provider=Microsoft.Jet.OLEDB.3.51;Data Source=M:
\DEV\LAMA\VBfiles\test.mdb;"

    Set adoParam = cmdCommand.CreateParameter("ParamName", adChar,
adParamInput, 20, GetName)
    cmdCommand.Parameters.Add adoParam

Jason Goff MCP(VB5)
Marketsquare Software Ltd



Quote:

> I'm trying to execute this code and this is the error message I get:

> "-2147217839 (80040e51)  The provider cannot derive parameter info and
> SetParameterInfo has not been called"

>     Dim cmdCommand As New Command
>     Dim rsRecSet As New Recordset
>     cmdCommand.ActiveConnection =

"Provider=Microsoft.Jet.OLEDB.3.51;Data Source=M:
\DEV\LAMA\VBfiles\test.mdb;"
Quote:
>     cmdCommand.CommandText = "SELECT ShapeBase,ShapeHeight,ShapeForm

FROM ShapeTable WHERE ShapeName= ?"

Quote:
>     cmdCommand.Prepared = True
>     cmdCommand.Parameters(0).value = GetName
>     Set shape = SQLConnection.Execute

> Can anyone help med please! Thanks alot!

> //Kristoffer Lindn

> -----------------** -- Posted from CodeGuru -- **-----------------
> http://www.codeguru.com/vb         The website for VB programmers.

--

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.



Sun, 23 Dec 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. changing ADO command′s SQL statement before showing a report

2. Newbie: How to change SQL statement of a command object

3. execute a SQL statement by command type object

4. How to change the SQL statement in command object dynamically

5. ADO object that splits-up an SQL statement

6. ADO - Running Parameterized Command More Than Once (Access97 Database)

7. Open multiple recordsets using single parameterized command object

8. T-SQL UPDATE in ADO Command object

9. T-SQL UPDATE in ADO Command object

10. Problem with nested and parameterized SQL querys in ADO

11. Call Parameterized SQL Server Stored Procedure from ADO

12. ADO Command object and MSShape commands

 

 
Powered by phpBB® Forum Software