Example Query using a fetch loop

Using group by SQL, show how many weeks each artist had #1 singles on the pop charts. This query result also shows how to split results onto multiple pages and how to use a parameter from the calling page on the URL line to restrict the query to one letter for the artist name.

<% ' Get a letter of the alphabet from the URL to restrict query %> <% Letter = Request.QueryString("Letter") %> <% MySQL = "select artist,sum(weeks) from allhits where artist like '"+Letter+"%' and single_album = 'S' and pop_country = 'P' group by artist" %> <% Mv = Request.Form("Mv") If Not (Mv = "PgUp" or Mv = "PgDn") Then %> Here are the results from the query:
<%= MySQL %>

<% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "DataFlex Test32" Set rs = Server.CreateObject("ADODB.Recordset") rs.Open MySQL, Conn, 3 rs.PageSize = 10 'rs.AbsolutePage = 1 Set Session("rs") = rs ' Store page in session var because AbsolutePage is Write-only Session("pg") = 1 private A Else Set rs = Session("rs") End If %>

<% For j = 1 to rs.PageSize %> <% For i = 0 to RS.Fields.Count - 1 %> <% Next %> <% rs.MoveNext If rs.EOF Then ' Don't try to print the EOF record. Exit For End If Next %>
ArtistWeeks
<%= RS(i) %>
<% If not rs.EOF Then %> <% End If %>

The web page you see here was created with the results of an SQL query. The data was collected from a DataFlex data file by the Flex/ODBC 32 bit driver, then the merge was done using Microsoft Active Server Pages scripting with VBScript code imbedded in the source document. The results were returned to this web browser by the Microsoft Internet Information Server.

This query can be run using the standard Flex/ODBC product available now, running on NT Server 4.0 with our included sample databases.

Here is the source code to show how this query was created.

df3.asp: 

  <HTML><HEAD>
  <TITLE>Flex/ODBC Live Data Demo</TITLE>
  </HEAD><BODY>
  <body background=dingbats/801.gif>
    <% ' Get a letter of the alphabet from the URL to restrict query %>
    <% Letter = Request.QueryString("Letter") %>
    <% MySQL = "select artist,sum(weeks) from allhits where artist like '"+Letter+"%' and single_album = 'S' and pop_country = 'P' group by artist" %>
  Here are the results from the query:<BR><I><B> <%= MySQL %></I></B><P>
    <%
    Mv = Request.Form("Mv")
    If Not (Mv = "PgUp" or Mv = "PgDn") Then
            Set Conn = Server.CreateObject("ADODB.Connection")
            Conn.Open "DataFlex Test32"
            Set rs = Server.CreateObject("ADODB.Recordset")
            rs.Open MySQL, Conn, 3
            rs.PageSize = 10
            'rs.AbsolutePage = 1
            Set Session("rs") = rs
            ' Store page in session var because AbsolutePage is Write-only
            Session("pg") = 1
            private A
    Else
       Set rs = Session("rs")
    End If
    %>
    <Center>
    <TABLE BORDER=1>
    <% For j = 1 to rs.PageSize %>
      <TR>
      <% For i = 0 to RS.Fields.Count - 1 %>
        <TD><%= RS(i) %></TD></TD>
      <% Next %>
      </TR>
    <%
      rs.MoveNext
      If rs.EOF Then
             ' Don't try to print the EOF record.
             Exit For
      End If
      Next
    %>
    </TABLE>
    <Form Action=df4.asp Method="POST">
    <% If not rs.EOF Then %>
      <INPUT TYPE="Submit" Name="Mv" Value="PgDn">
    <% End If %>
    </Form>
    </Center>
  <BR>
  </BODY></HTML>

Note: There appears to be a bug in ASP when trying to display a numeric value greater than 99, for some reason it stops and shows the error: Microsoft OLE DB Provider for ODBC Drivers. I don't know why.

----------

Home Search Feedback