Run stored procedure with parameters to AS400 from VBA result in -2147217865

Question

0.00/5 (No votes)

See more:

, +

VB

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

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 ?

Posted 2-Mar-18 4:05am

Member 11977135

Updated 5-Mar-18 21:06pm

Add a Solution

Comments

Richard Deeming 2-Mar-18 11:41am

Sounds like you don't have a stored procedure called "API003" in your database.

Member 11977135 2-Mar-18 16:43pm

Indeed there is. I made a program in C# calling the procedure. That is working fine.

Member 11977135 5-Mar-18 8:35am

This is working C# code:

using System.ComponentModel;
using System.Drawing;
using System.Windows.Forms;
using IBM.Data.DB2.iSeries;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void textBox1_Leave(object sender, EventArgs e)
{

string Connection = "";
bool isChecked = prod.Checked;
if (isChecked)
Connection = "DataSource=172.18.0.50; DefaultCollection=MVX9MOD; UserID=SKS6400; Password=password";
else
Connection = "DataSource=172.18.0.51; DefaultCollection=MVX9MOD; UserID=SKS6400; Password=password";

using (iDB2Connection i5conn = new iDB2Connection(Connection))
try
{

String tidskod = ("001SKA" + scannat.Text);

i5conn.Open();
iDB2Command ptQuery = new iDB2Command("API003", i5conn);
ptQuery.CommandType = CommandType.StoredProcedure;

ptQuery.Parameters.Add("@InFormat", iDB2DbType.iDB2Char);
ptQuery.Parameters["@InFormat"].Direction = ParameterDirection.Input;
ptQuery.Parameters["@InFormat"].Value = "0001";

ptQuery.Parameters.Add("@InData", iDB2DbType.iDB2Char);
ptQuery.Parameters["@InData"].Direction = ParameterDirection.Input;
ptQuery.Parameters["@InData"].Value = tidskod;

ptQuery.Parameters.Add("@OutFormat", iDB2DbType.iDB2Char);
ptQuery.Parameters["@OutFormat"].Direction = ParameterDirection.Input;
ptQuery.Parameters["@OutFormat"].Value = "0001";

ptQuery.Parameters.Add("@OutData", iDB2DbType.iDB2VarChar);
ptQuery.Parameters["@OutData"].Direction = ParameterDirection.Output;
ptQuery.Parameters["@OutData"].Value = "";

ptQuery.Parameters.Add("@ErrorData", iDB2DbType.iDB2VarChar);
ptQuery.Parameters["@ErrorData"].Direction = ParameterDirection.Output;
ptQuery.Parameters["@ErrorData"].Value = "";
ptQuery.ExecuteNonQuery();

String str = (string)(ptQuery.Parameters["@OutData"].Value);
stelle.Text = "";
stelle.Text = (str.Substring(2,10));
omrade.Text = (str.Substring(7,2));
plats.Text = (str.Substring(9));

errors.Text = (string)(ptQuery.Parameters["@ErrorData"].Value);

i5conn.Close();

i5conn.Open();
iDB2Command ptQuery2 = new iDB2Command("API005", i5conn);
ptQuery2.CommandType = CommandType.StoredProcedure;

ptQuery2.Parameters.Add("@InFormat", iDB2DbType.iDB2Char);
ptQuery2.Parameters["@InFormat"].Direction = ParameterDirection.Input;
ptQuery2.Parameters["@InFormat"].Value = "0001";

ptQuery2.Parameters.Add("@InData", iDB2DbType.iDB2Char);
ptQuery2.Parameters["@InData"].Direction = ParameterDirection.Input;
ptQuery2.Parameters["@InData"].Value = tidskod;

ptQuery2.Parameters.Add("@OutFormat", iDB2DbType.iDB2Char);
ptQuery2.Parameters["@OutFormat"].Direction = ParameterDirection.Input;
ptQuery2.Parameters["@OutFormat"].Value = "0001";

ptQuery2.Parameters.Add("@OutData", iDB2DbType.iDB2VarChar);
ptQuery2.Parameters["@OutData"].Direction = ParameterDirection.Output;

Member 11977135 5-Mar-18 13:11pm

Since I know there is an API003, maybee it is the Connection string that points on wrong database ?
I use "Default Collection=MVX9MOD;". I used this with the .NET driver "using IBM.Data.DB2.iSeries;" where it is working, but maybee its different in "iSeries Access ODBC Driver" ?

ZurdoDev 2-Mar-18 16:12pm

What line of code causes the error?

Member 11977135 2-Mar-18 16:40pm

.Execute is the line

Maciej Los 5-Mar-18 16:15pm

Try to use different connection string: AS/400 connection strings - ConnectionStrings.com[^]. I'm betting on standard IBM Client Access OLE DB provider.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11977135 · Accepted Answer · 2018-03-05T21:07:00

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;"