iSeries & AS/400 data in an HTML table

It's AMAZINGLY easy to create an Active Server Page to retrieve data from a file on the 400 and display those records in an HTML Table. ActiveX Data Objects (ADO) and IBM's OLE DB provider for the 400 make this possible.

There are a few requirements that need to be met to perform this. First off, you need to have a machine running Microsoft Internet Information Server (IIS). This machine must also have Client Access Express (V4R4M0 or higher) installed.

The attached code contains the source for an Active Server Page that uses a Server Side VB Script to read data and display it in the browser. Either select the text above and copy it to the clipboard and paste it into a new active server page document or click here to download the source in a text file. In this example, replace the SYSNAME value with the system name or IP address of your iSeries or AS/400. You will also need to use a valid user ID and password for your system. We first create the connection to the 400 using the IBMDA400 OLE DB provider. Next we use this connection along with a simple SQL SELECT statement to select the fields to be displayed on our page. This part is important because the example here will display all of the fields in the selected data source. This allows us to use this same peice of code over and over again. The only part that changes is the SELECT statement.

This example gives you the basic building blocks of using ADO and OLE DB from within an Active Server Page.


 <HTML> 

<HEAD> 

<CENTER><H1>Data Set Results</H1> 

</HEAD> 

<BODY> 

<% 

   Set conn = Server.CreateObject("ADODB.Connection") 

   conn.open "Provider=IBMDA400;Data Source=SYSNAME;User 
ID=USER;Password=SECRET;" 

    sql = "SELECT EMPNAM, EMPAD1, EMPAD2, EMPSTE, EMPZIP FROM QGPL.EMPMAST" 

    Set rs = Server.CreateObject("ADODB.Recordset") 

    set rs.ActiveConnection=conn 

    rs.Open sql 

    if conn.errors.count=0 Then 

    Response.write "<table border='1' cellspacing='1' cellpadding='1' 
width='80%'><thead><caption><FONT SIZE=-1 FACE='arial, helvetica'><b>Query 
for File " & request.Form("LibFile") & "</caption><tr>" 

    rs.MoveFirst 

    if Not rs.EOF Then 

    for x=0 to rs.fields.count-1 

    Response.write "<th BGCOLOR='#0066cc' WIDTH=60><FONT SIZE=-1 
FACE='arial, helvetica'><b><font color='#ffffff'>" & rs.fields(x).name & 
"</th>" 

    next 

    End If 

  

    do until rs1.eof 

    Response.write "<tr>" 

    For I=0 To rs1.Fields.Count-1 

        Response.write "<td><FONT SIZE=-1 FACE='arial, helvetica'>" & 
Trim(rs1.Fields(I).Value) & "</td>" 

    Next 

    Response.write "</tr>" 

        rs.MoveNext 

    loop 

    rs.close 

    conn.close 

  

    Response.write "</TBODY></TABLE>" 

%> 

</BODY> 

</HTML>

This was first published in December 2001

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

Back to top