We help IT Professionals succeed at work.
Get Started

Adodb Command.Execute() - type value error

stanl
stanl asked
on
2,780 Views
Last Modified: 2008-02-20
I was experimenting with ADO and the "CREATE PROC" SQL to create stored procedures in an Access 2002 database.  I started with an example that used Northwind.

I then wrote a short VBS function to execute the Procedure via the adodb.command object -  I first tried it by using CreateParameter() and Append, but kept getting a type value error when executing - however, if I pass the parameters to the function then include them as an optional array (the 2nd parm of cmd.Execute() ) it works fine.  Most of the examples I have seen don't use the 2nd parameter, so I was wondering, is there a bug in Jet 4.0?  -  

======================================

function runcmd(nRec,cName)
' this is the query in Access
'PARAMETERS inProductID Long, inProductName Text ( 40 );
'UPDATE Products SET Products.ProductName = inProductName
'WHERE (((Products.ProductID)=[inProductID]));


Set conn = CreateObject("adodb.Connection")
conn.Open("PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb;")
Set cmd = CreateObject("adodb.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "procProductsUpdateItem"
cmd.CommandType = 4

' if the next 3 lines are executed you get a "Type Value" error
cmd.Parameters.Append cmd.CreateParameter("inProductID",139,1,,2)
cmd.Parameters.Append cmd.CreateParameter("inProductName",129,1,40,"New Chang")
cmd.Execute , , adExecuteNoRecords

' however, if you pass the function parameters to an array, it works fine
'cmd.Execute ,Array(nRec,cName), adExecuteNoRecords
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
conn.close
Set conn = Nothing
runcmd = 0
end function

=========================================

stan
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE