VB.NET to call a AS400 Stored Procedure

[ben@neweraopt.com]

Dear Tom, How can you use an oleDbConnection object with VB.NET and the /400? Only odbcConnection seems to be supported .. How can you use oleDb Objects? I can connect in Windows Forms by dragging an odbcDataAdapter onto my Windows Form but, have no success with WEB FORMS. (I also am trying to use stored procedures ... Can do so in Windows Forms) Any info is much appreciated.

[Guest.Visitor]

I have been able to call AS400 stored procedure from VBA using an ADO connection. I don't include the pwd in the connection string, so it prompts the user for it if necessary. dim AS400_conn As ADODB.Connection Dim conn_str As String ' connection string Set AS400_conn = New ADODB.Connection conn_str = "Data Source=AS400_Name;Default Collection=AS400_Library;PROVIDER=IBMDA400;" With AS400_conn ' AS400 connection .CursorLocation = adUseClient .Open conn_str, UserName, "" ' opens ADO connection for user ..... end with

[Tom Yaz]

Ben, I am doing this behind a push button on a webform. I am not dragging any adaptors on the form,(not that it cant be done) but rather programatically doing it just as you see. This ole call works "sortof" Although it executed the CL correctly, I could not get a parm passed back. I ran this by a AS400/SQL/NET expert and he had the same problem. However he was able to do it successfully using odbc as was I. Also I should tell you I was able to create another OLE connection programtically to retreive and write data to the as400 from a web form. If you want some samples email me at tomyasnowski@hotmail.com Thanks Tom

[Tom Yaz]

Hi All: I am following a Ms example on how to call a AS400 stored procedure. Here is what I did: On the AS400: PGM (&STR1 &STR2) /*SWAPSTR*/ DCL VAR(&STR1) TYPE(*CHAR) LEN(10) DCL VAR(&STR2) TYPE(*CHAR) LEN(10) DCL VAR(&TMPSTR) TYPE(*CHAR) LEN(10) CHGVAR &TMPSTR &STR1 CHGVAR &STR1 &STR2 CHGVAR &STR2 &TMPSTR ENDPGM Created a SP "wrapper": CREATE PROCEDURE TESTTOM/SWAPSTR(INOUT PARAM1 CHAR (10 ), INOUT PARAM2 CHAR (10 )) LANGUAGE CL NOT DETERMINISTIC NO SQL EXTERNAL NAME TESTTOM/SWAPSTR PARAMETER STYLE GENERAL then the VB.NET stuff: Dim ConnString As String ConnString = "Provider=IBMDA400; Data Source=AS01DEV; User ID=TJYASNOW; Password=XXXXX;" Dim myConnection As New OleDb.OleDbConnection myConnection.ConnectionString = ConnString Dim myCommand As New OleDb.OleDbCommand("CALL TESTTOM.SWAPSTR(?,?)", myConnection) myCommand.CommandType = CommandType.Text myCommand.Connection = myConnection 'setup the parameters Dim myParameter As New OleDb.OleDbParameter("param1", OleDb.OleDbType.Char, 10) myParameter.Value = "String1" myParameter.Direction = ParameterDirection.InputOutput myCommand.Parameters.Add(myParameter) Dim myParameter2 As New OleDb.OleDbParameter("param2", OleDb.OleDbType.Char, 10) myParameter2.Value = "String2" myParameter2.Direction = ParameterDirection.InputOutput myCommand.Parameters.Add(myParameter2) 'execute Dim results As String myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() results = myParameter2.Value When executed I get a MS error: SQL7985: CALL statement complete. Cause . . . . . : Call to procedure SWAPSTR completed successfully. At face value irt appears that MS interprets a successfull execution as an error. I do not get the parm returned, but on the AS400 side the object is recorded as being "used". Any clues? Thanks Tom

[MikeInNH]

Hey Tom - Don't know if you are still stuck (and don't know if this will be any help but)...Does the provider assume a specific order for the return status (i.e., like the SQLOLE provider does for SQL Server)?? I can barely read the AS/400 stuff in your ex. and not much better with the .net. But in plain old ADO I know that you have to Create the first parameter (and Append to the collection) as the return status. Comprende?? -Mike