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

iSeries & AS/400 data in an HTML table

This tip defines how to create an ASP to retrieve data from a file on the 400 and display those records 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.



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




   Set conn = Server.CreateObject("ADODB.Connection") "Provider=IBMDA400;Data Source=SYSNAME;User 


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


    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 & 


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


    Response.write "</tr>" 






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




Dig Deeper on iSeries CL programming