Problem solve Get help with specific problems with your technologies, process and projects.

ADO.NET calling CLP with parms

WebForm1.aspx can either be for Web forms or used as VB.NET. LSTDBRV is the CL program that is called by the Web form.

WebForm1.aspx can either be for Web forms or used as VB.NET. LSTDBRV is the CL program that is called by the Web...

form. Basically, the VB/ASPX calls a CL program on the iSeries, passing it a parameter (file name). After the CLP is done, a second parameter is passed back with the status. The CLP builds an *OUTFILE that the VB/ASPX then reads using SQL and displays on a Web page.


Imports ADODB
Imports System.Data.Odbc
Imports System.Diagnostics

Public Class WebForm1
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    Private Sub InitializeComponent()

    End Sub
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents btnContinue As System.Web.UI.WebControls.Button
    Protected WithEvents txtFileName As System.Web.UI.WebControls.TextBox
    Protected WithEvents txtStatus As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblFileName As System.Web.UI.WebControls.Label
    Protected WithEvents lblStatus As System.Web.UI.WebControls.Label
    Protected WithEvents btnExit As System.Web.UI.WebControls.Button

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
    End Sub

#End Region

    Private Sub btnContinue_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnContinue.Click
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
    End Sub

    Sub Page_Load(ByVal txtFileName As String)
        'Put user code to initialize the page here

        Dim MySystem As New ADODB.Connection
        Dim MyProgram As New ADODB.Command
        Dim Rcds As VariantType
        Dim Parms As Array = Array.CreateInstance(GetType(Object), 2)
        Dim strStatus As String = "*NOT DONE*"
        Dim ODBCConn As OdbcConnection

        ' Me.TextBox1 = New System.Web.UI.WebControls.TextBox

        ' Open ADO connection to iSeries
        MySystem.Open("Provider=IBMDA400;Data Source=yourIPaddr;User ID=xxxx;Password=yyyy;")

        ' Activate the connection
        MyProgram.ActiveConnection = MySystem

        ' Initialiing the array for parameters
        Parms.SetValue(txtFileName, 0)
        Parms.SetValue(txtStatus, 1)

        ' Define the type of command
        MyProgram.CommandType = CommandTypeEnum.adCmdText

        ' Define the parameters to be passed
        MyProgram.Parameters.Append(MyProgram.CreateParameter("FILENAME", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 10, 

        MyProgram.Parameters.Append(MyProgram.CreateParameter("STATUS", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamOutput, 10, 


        ' Define the command
        MyProgram.CommandText = "{{CALL /QSYS.LIB/BOBS.LIB/LSTDBRV.PGM(?,?)}}"

        ' Not sure ..........
        MyProgram.Prepared = True

        ' Run the iSeries program
        MyProgram.Execute(Rcds, Parms, MyProgram.CommandType.adCmdText)

        ' Get the returned parameter
        strStatus = MyProgram.Parameters.Item("STATUS").Value
        txtStatus.Text = strStatus

        ' If the program was successful, read the data extracted
        If strStatus = "***DONE***" Then

            ' Set up a new connection - for ODBC & SQL
            ODBCConn = New OdbcConnection("DRIVER=Client Access ODBC Driver (32-bit);SYSTEM=;UID=BOBS;PWD=STONEISL;")

            ' Set up the SQL statement
            Dim myCommand As New OdbcDataAdapter("select * from BOBS.MYDSPDBR", ODBCConn)

            ' Run the SQL, filling a DataSet with the result from the file MYDSPDBR
            Dim ds As DataSet = New DataSet

            myCommand.Fill(ds, "MYDSPDBR")

            ' Fill the DataGrid with the data returned
            DataGrid1.DataSource = ds.Tables("MYDSPDBR").DefaultView
            DataGrid1.Visible = True


            DataGrid1.Visible = False

        End If

    End Sub

End Class


            PGM        PARM( &FILENAME &STATUS )                         
             DCL        VAR( &FILENAME ) TYPE( *CHAR ) LEN( 10 )          
             DCL        VAR( &STATUS   ) TYPE( *CHAR ) LEN( 10 )          
             DLTF       FILE( BOBS/MYDSPDBR )                             
             MONMSG     MSGID( CPF2105 )                                  
             CHGVAR     VAR( &STATUS ) VALUE( '***DONE***' )              
                          OUTFILE( BOBS/MYDSPDBR )                        
             MONMSG     MSGID( CPF0000 ) +                                
                          EXEC( CHGVAR VAR( &STATUS ) +                   
                            VALUE( '***FAIL***' ) )                       



The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.

Dig Deeper on iSeries CL programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.