I was trying to run Oracle stored procedure with output parameters from Excel VBA with the following piece of code:
Dim cmd As Object Dim resultSet As Object Set cmd = CreateObject("ADODB.Command") With cmd .CommandText = "PK_AUTH.LOGON" .NamedParameters = True .Parameters.Append .CreateParameter("login", adVarChar, adParamInput, 50, login_) .Parameters.Append .CreateParameter("pass", adVarChar, adParamInput, 50, pass_) .Parameters.Append .CreateParameter("ldb", adVarChar, adParamOutput, 50) .Parameters.Append .CreateParameter("pdb", adVarChar, adParamOutput, 50) .CommandType = adCmdStoredProc .ActiveConnection = GetConn_() Set resultSet = .Execute ldb_ = .Parameters.Item("ldb") End WithAnd I always got Run-time error 3001 'Arguments Are Of The Wrong Type, Are Out Of The Acceptable Range, or are in conflict with one another' upon invocation of
.CommandType = adCmdStoredProcor
.Parameters.Append .CreateParameter(...)no matter which statement I placed first.
After fighting for a while I found this post that stated that error is fired because of late binding of library references, so VB simply did not know of adCmdStoredProc and other constants
That meant that this error has nothing to do with ADODB or Ole or, I just said that it does not know the value of constant. Not very informative in fact...
So, I simply added
Const adVarChar As Long = 200 Const adParamInput As Long = &H1 Const adParamOutput As Long = &H2 Const adCmdStoredProc As Long = &H4to the Sub header and everything worked fine. Constants are defined in c:\Program Files\Common Files\System\ado\adovbs.inc
No comments:
Post a Comment