AS400 data in HTML web page (DSN??)

[Hubertrum]

I am trying to show a table of AS400 data on a web page. I have created a System DSN through the ODBC connections utility on IBM Client Access for Windows (you can specify a default User ID but no password??)

Then I reference this in an HTML page thus:

<&#37; 
Set conn = Server.CreateObject("ADODB.Connection") 
conn.open "Provider=IBMDA400;Data Source=MyAS400;User ID=S2518059;Password=blahblah;" 
sql = "SELECT c4file, c4test from roboscript/scrfeld where c4srce = 'SABS'" 
Set rs = Server.CreateObject("ADODB.Recordset") 
set rs.ActiveConnection=conn 
rs.Open sql 

When I click the .html document the page with its title displays but no data.
How do I work out what I am doing wrong??

[jamief]

Re: AS400 data in HTML web page (DSN??)

First I learned its much better to create one connection string and then include
in your ASP source..

so my connection looks like this

<&#37;
'=======================================================================
  'Create DB objects
  
  Set cmd = Server.CreateObject("ADODB.Command")
  Set rs = Server.CreateObject("ADODB.Recordset")
  Set Connection = Server.CreateObject("ADODB.Connection")    
  
       
  Connection.Open "Provider=IBMDA400;User ID=FLANARY;Password=MYPASSWORD;Data Source=10.0.1.1;"
  
 '======================================================================= 
%>

Then I include in another page:

%>

<!--#include virtual="/includes/connection.asp" -->

<% 

My SQL Loop - notice the library and file are seperated by "." rather than "/"
also look at the way I build the single quote in my string....Lastly usually
you get a line number in error on the page? are you getting one?

Important then Move next is very important....or youll get a loop that sucks you into
a new dimension and drags the rest of your company with it

 SQLSTMT3 =  "SELECT mtd$ as invweight, LSTMTD$ as lminvweight , LSTMTDDAYS as SQLDays ,   " & _   
             "  MTDDAYS as daysthismonth  "  &_
             "from " & "jamielib" &  ".INSSALES  where ISIS = " & _
             "'" & RS.Fields("ohis") & "'"  
 Set RS3 = Connection.Execute(SQLStmt3)                       
 Do While Not RS3.EOF 
 
  count = count +1
   workdays = Round(RS3.Fields("SQLDays"))
  InvoicedWeight = Round(RS3.Fields("INVWEIGHT"))
  LastMonthWeight = Round(RS3.Fields("lmINVWEIGHT"))
  if workdays > 0 then
  monthaverage =  lastMonthWeight \ workdays
  end if
  
  dividedays = CDbl(RS3.Fields("daysthismonth") )
  'Response.Write("<Br>" & InvoicedWeight & "--" & dividedays)
  DailyAverage = InvoicedWeight / dividedays 
  if monthaverage > 0 then
  mypercent = (dailyaverage/monthaverage)
   end if
  
  if background = ""  or Background = "#FFFFFF" then
     background = "#CCFFCC"
  else
     background = "#FFFFFF"
  end if    
    Response.Write("<tr  bgcolor= " & background & ">")
  Response.Write("<td>")
  Response.Write(count)
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(RS2.Fields("SGSPNM"))
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(FormatNumber(RS2.Fields("totalorders"),0))
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(FormatNumber(RS.Fields("MyWeight"),0))
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(FormatNumber(RS3.Fields("INVWEIGHT"),0))
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(FormatNumber(dailyaverage,0))
  Response.Write("</td>")
  Response.Write("<td>")
  Response.write(FormatNumber(monthaverage,0))
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(FormatPercent(mypercent,0))
  Response.Write("</td>")
  Response.Write("</tr>")


save1 = save1 + Round(RS2.Fields("totalorders"))
save2 = save2 + Round(RS.Fields("MyWeight"))
save3 = save3 + Round(RS3.Fields("INVWEIGHT"))
save4 = save4 + dailyaverage
save5 = save5 + monthaverage



rs3.MoveNext
Loop
rs3.Close 

Yours

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider=IBMDA400;Data Source=MyAS400;User ID=S2518059;Password=blahblah;" 
sql = "SELECT c4file, c4test from roboscript/scrfeld where c4srce = 'SABS'" 
Set rs = Server.CreateObject("ADODB.Recordset")
set rs.ActiveConnection=conn
rs.Open sql 

[Hubertrum]

Re: AS400 data in HTML web page (DSN??)

I have incorporated what yuo say into my .html document. I didn't have an external connection.asp page as I wanted it to be as simple as possible and working before I started adapting it further. The document looks like;

<HTML> 

<HEAD> 

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

</HEAD> 

<BODY> 


<&#37;
'=======================================================================
  'Create DB objects
  
  Set cmd = Server.CreateObject("ADODB.Command")
  Set rs = Server.CreateObject("ADODB.Recordset")
  Set Connection = Server.CreateObject("ADODB.Connection")    
  
       
  Connection.Open "Provider=IBMDA400;User ID=S2518059;Password=password;Data Source=10.10.10.10;"

 '======================================================================= 
SQLSTMT3 =  "SELECT c4test as tester, c4file as file " & _   
             "from " & "roboscript" &  ".scrfeld  where c4srce = 'SABS' " 
 Set RS3 = Connection.Execute(SQLStmt3)                       
 Do While Not RS3.EOF 
 
  count = count +1
    
  if background = ""  or Background = "#FFFFFF" then
     background = "#CCFFCC"
  else
     background = "#FFFFFF"
  end if    
  
  Response.Write("<tr  bgcolor= " & background & ">")
  Response.Write("<td>")
  Response.Write(count)
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(RS2.Fields("tester"))
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(RS2.Fields("file"))
  Response.Write("</td>")
  Response.Write("</tr>")




rs3.MoveNext
Loop
rs3.Close


%> 

</BODY> 

</HTML>

It doesn't work. Where do I go from here?

[jamief]

Re: AS400 data in HTML web page (DSN??)

my guess is that either you dont have client access loaded on the server...or IIS setup on server is incorrect...

just to prove this change your sql to just a select on an IBM file....

select * from systables 

**Also you are define the record set as Rs3 (record set 3)
but when you write your html your referencing rs2 (record set 2)

I know in my example I used both but I didnt post the outer loop.

[Hubertrum]

Re: AS400 data in HTML web page (DSN??)

Jamie

Thanks for the response. I have made the changes as you suggested. My HTML document looks like;

<HTML> 
<HEAD> 
<CENTER><H1>Data Set Results</H1> 
</HEAD> 
<BODY> 

<&#37;
'=======================================================================
  'Create DB objects
  
  Set cmd = Server.CreateObject("ADODB.Command")
  Set rs = Server.CreateObject("ADODB.Recordset")
  Set Connection = Server.CreateObject("ADODB.Connection")    
  
       
  Connection.Open "Provider=IBMDA400;User ID=S2518059;Password=xxxxxxx;Data Source=10.10.10.10;"
  
 '======================================================================= 
SQLSTMT3 =  "SELECT * from QSYS2/systables"
 Set RS3 = Connection.Execute(SQLStmt3)                       
 Do While Not RS3.EOF 
 
  count = count +1
    
  if background = ""  or Background = "#FFFFFF" then
     background = "#CCFFCC"
  else
     background = "#FFFFFF"
  end if    
  
  Response.Write("<tr  bgcolor= " & background & ">")
  Response.Write("<td>")
  Response.Write(count)
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(RS3.Fields("table_name"))
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(RS3.Fields("table_owner"))
  Response.Write("</td>")
  Response.Write("</tr>")

rs3.MoveNext
Loop
rs3.Close
%> 

</BODY> 
</HTML>

No data is displayed still I'm afraid.

[jamief]

Re: AS400 data in HTML web page (DSN??)

when I get to work Ill test your code...if it works for me then we need to look @ your server...thats where I believe the errors are anyway...I would guess that in IIS you dont have scripting enabled.


jamie

[jamief]

Re: AS400 data in HTML web page (DSN??)

okay this gets me the attached image using the whole table is tooo much..

<HTML> 
<HEAD> 
<CENTER><H1>Data Set Results</H1> 
</HEAD> 
<BODY> 

<%
'=======================================================================
  'Create DB objects
  
  Set cmd = Server.CreateObject("ADODB.Command")
  Set rs = Server.CreateObject("ADODB.Recordset")
  Set Connection = Server.CreateObject("ADODB.Connection")    
  
       
  Connection.Open "Provider=IBMDA400;User ID=FLANARY;Password=xxxxxx;Data Source=10.10.10.10;"
  

 '======================================================================= 
SQLSTMT =  "SELECT TABLE_NAME, TABLE_OWNER from qsys2.systables where SYSTEM_TABLE_SCHEMA = 'QSYS'"
 Set RS = Connection.Execute(SQLStmt)                       

Response.Write("<table>")
Do While Not RS.EOF 
 
  count = count +1
    
  if background = ""  or Background = "#FFFFFF" then
     background = "#CCFFCC"
  else
     background = "#FFFFFF"
  end if    
  
  Response.Write("<tr  bgcolor= " & background & ">")
  Response.Write("<td>")
  Response.Write(count)
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(RS.Fields("TABLE_NAME"))
  Response.Write("</td>")
  Response.Write("<td>")
  Response.Write(RS.Fields("TABLE_OWNER"))
  Response.Write("</td>")
  Response.Write("</tr>")

rs.MoveNext
Loop
rs.Close

Response.Write("</table>")

%> 

</BODY> 
</HTML>