从VBA生成带参数的存储过程到AS400，结果为-2147217865 [英] Run stored procedure with parameters to AS400 from VBA result in -2147217865

问题描述

Sub Call_API003_2()
    
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    kod = "ZX311266012Y56Q9E7EBI38"
    'Data Source=S652B48C;
    cnn.ConnectionString = "Driver={iSeries Access ODBC Driver};System=172.18.0.51;Default Collection=MVX9MOD;Uid=SKS6400;Pwd=password;"
    On Error GoTo err:
    cnn.Open
    
                If cnn.State = adStateOpen Then
                 Debug.Print "Connected"
                Else
                    MsgBox "Not Connected"
                End If
            
                Set cmd = New ADODB.Command
                        
                        With cmd
                                .ActiveConnection = cnn
                                .CommandType = adCmdStoredProc
                                .CommandText = "API003"
                                .Parameters.Append .CreateParameter("@INFORMAT", adChar, adParamInput, 4, "0001")
                                .Parameters.Append .CreateParameter("@INDATA", adChar, adParamInput, 29, "001SKA" & kod)
                                .Parameters.Append .CreateParameter("@OUTFORMAT", adChar, adParamInput, 4, "0001")
                                .Parameters.Append .CreateParameter("@OUTDATA", adVarChar, adParamOutput, 255, "")
                                .Parameters.Append .CreateParameter("@ERRORDATA", adVarChar, adParamOutput, 255, "")
                                 Debug.Print "Indata: " & .Parameters("@INDATA").Value
                                .Execute
            
                        End With
            
                        Debug.Print "Retur: " & cmd.Parameters("@OutData").Value
                        Debug.Print "Fel: " & cmd.Parameters("@ErrorData").Value
    cnn.Close
    
    
err:
    Debug.Print ("Error:" & err)
End Sub

我尝试了什么：



我在C＃中尝试过它运行正常，现在我努力让它在vba中运行。

我收到错误-2147217865找不到N型。



有参数经验的人吗？

What I have tried:

I tried in C# and it runs fine, Now I struggle hard to get it running in vba.
I get error -2147217865 "type N not found".

Anyone with experience with parameters ?

推荐答案

谢谢你的所有输入。我调查过，询问了SYSOP，并找出了API003的存储位置。 C＃并不关心这个，但是在更改了连接字符串之后，我现在也可以在vba中运行!!



解决方案：在iSeries Navigator中检入存储过程的位置，并添加数据库和默认库：

Thankyou all for your inputs. I have investigated, asked the SYSOP, and found out where the API003 is stored. C# did not care about this, but after changing the connectionstring, I can now run also in vba!!

Solution : Check in iSeries Navigator where the Procedure is stored, and add database and Default Libraries:

DATABASE=QGPL;DefaultLibraries=MVX9MOD;

正确连接字符串：

Correct connectionstring:

cnn.ConnectionString = "Driver={iSeries Access ODBC Driver};System=172.18.0.51;DATABASE=QGPL;DefaultLibraries=MVX9MOD;Uid=SKS6400;Pwd=password;"

这篇关于从VBA生成带参数的存储过程到AS400，结果为-2147217865的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！