sponsored links



No announcement yet.

Accessing the os/400 with Visual Basic 2010

  • Filter
  • Time
  • Show
Clear All
new posts

  • Accessing the os/400 with Visual Basic 2010

    I know this has been addressed before as I've googled for examples however the majority of the sites I come to are filled with bad links as their dates go back from the late 90s up to 2006. So hasn't quite helped with my search.

    The IBM Redbook Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET has given me some insight but seems to target Java and C++. And still I am a little confused. So looking for something a little simple that I can explore and branch out of on my own. I primarly program in RPGILE on a I5 development box and I write programs to convert data from one vendor system into our system. Been programming in RPG for about 17 years now. I took both C++ and Visual Basic back in the late 90s in college however never touched it again since till now. Some for VB is easy enough but other a decent learning curve but I'm progressing ok. Now enough of me babbling here is what I want to do:

    I can do this with MS Access however I want to write a stand alone program to do the same. Give it a feeling of accomplishment if I am able to develop something on my own this way. As it'll help teach me how to evolve beyond. This project will be a simple account look up. I will have 3 forms. The 1st form is the connection one. I want it to ask for user name and password (masking the password) with two buttons Connect and Exit. I can code the Exit easily enough however I want to make sure the connection closes if active when exit is chosen. So will need some insight on that. Otherwise it's a simple Me.Close()

    Then the 2nd form contains two buttons one for Enter and the 2nd to Exit (or maybe log off). There will be a label and a text field asking for a old encounter number. Once Enter is hit it will using the connection query a file just to make this easy TESTFILE out of TESTLIB. Then it will pull the information into the 3rd form showing the old encounter, new encounter number and the patient's name. Along with a button to go back to the selection form.

    So basically how with VB do I make that connection to the system and select the table? And yes client access is installed.

    Thanks and hopefully I presented enough information.

  • #2
    Re: Accessing the os/400 with Visual Basic 2010

    Here are some snippets using the IBM I Access for windows Active X Object Library (referenced in the project)

    The connection; This is from excel, hence the ranges, but its still the same vb
    UserId = UCase(Range("B5").Value)
    PassWord = UCase(Range("B6").Value)
    Library = UCase(Range("G1").Value)
    System = UCase(Range("G2").Value)
    StrConnection = "Provider=IBMDA400; " & _
                     "Data Source=" & System & ";" & _
                     "User ID= " & UserId & ";" & _
                     "Password=" & PassWord & ";"
    objConn.ConnectionString = StrConnection

    Having the host do something;
    'Now the program gets called to do the editing/processing for the file.
    ObjCommand.ActiveConnection = objConn
    ObjCommand.CommandType = adCmdText
    ObjCommand.Parameters.Append ObjCommand.CreateParameter("FILE", adChar, adParamInput, 10, "EXPOU_NOTR")
    ObjCommand.CommandText = "{{Call QGPL.PC001C01 (?)}}"
    ObjCommand.Prepared = True
    'This calls the program on

    close connection;

    Record playing. Clear and load a file.
    'Delete the records first
    objConn.Execute "DELETE FROM " & Library & ".EXPOU_NOTR"
    'Now upload the records
    objRs.Open "SELECT * FROM " & Library & ".EXPOU_NOTR", objConn, adOpenDynamic, adLockOptimistic
    ' Loop for each row and exit when there is no item number.
    For RowCount = 9 To 16000
        'If the user did not enter values in all three fields, get cout
        If Cells(RowCount, 1) = "" _
            Or Cells(RowCount, 2) = "" Or _
            Cells(RowCount, 3) = "" Then
        Exit For
        End If
        objRs.Fields(0) = UCase(Cells(RowCount, 1).Text) 'vendor number
        objRs.Fields(1) = UCase(Cells(RowCount, 2).Text) 'SKU
        BadField = "Quantity"   'This tests for a numeric value, not being numeric
        ThisInteger = Cells(RowCount, 3).Value
        objRs.Fields(2) = Cells(RowCount, 3).Value 'Quantity
        objRs.Fields(3) = UCase(Cells(RowCount, 4).Text) 'Originating store
        objRs.Fields(4) = UCase(Cells(RowCount, 5).Text) 'Destination Store
        ThisCount = ThisCount + 1
    ' Start next iteration of RowCount loop.
    Next RowCount

    Perhaps this gives you are starting place.
    Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply


    • #3
      Re: Accessing the os/400 with Visual Basic 2010

      Thanks DeadManWalks! That gives me something to start with. I will work on this later today when I get time and post up any other questions that I come across. I will need to review a little more on SQL. I have only done basic selects or updates/deletes on the os400 side. As I am use to Select From library/filename where fieldA = 'value'. Simple stuff. And I think in this example that I am testing with I could do a select but how do you reference the DDS fields in VB? In this example I will select the record out of this cross reference file where the primary key (patient number) equals the account number that is being passed from the text box entered from the form. Then I will display to other text boxes on a 3rd form fields out of this file. Is it referenced as library.filename("fieldname1") ?


      • #4
        Re: Accessing the os/400 with Visual Basic 2010

        The fields are by array.

        'First Field
        objRs.Fields(0) = UCase(Cells(RowCount, 1).Text) 'vendor number

        'Second Field
        objRs.Fields(1) = UCase(Cells(RowCount, 2).Text) 'SKU
        Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply


        • #5
          Re: Accessing the os/400 with Visual Basic 2010

          Think I got a hand on it. This is how I have the connection going so far. Suggestions or is it setup correctly?

          First form prompts for user id, password and system. Two buttons, one connect the other exit. User name is txtUserID.text Password is txtPassword.text and system txtSystem.text

          on the connection:

          Dim con As ADODB.Connection
          Dim rst As ADODB.Recordset

          con = New ADODB.Connection
          rst = New ADODB.Recordset

          Dim strConnection as String
          Dim strSQLstring as String

          StrConnection = "provider=IBMDA400; " & "Data Source=" & txtSystem.text & ";" & _
          "User ID=" & txtUserID.Text & ";" & "Password=" & txtPassword.Text & ";"


          From here I assume the connection has opened? When running the trail no errors were received. Will continue to code in the SQL statements and follow up. Posting my progress and other information in case anyone else searches how to do this as I did, will see an updated process.

          Thanks again.