Re: Any ideas

Richard Sensale ( (no email) )
Tue, 6 Jan 1998 14:12:36 -0500

Thanks. Im still learning sql and that helped.
-----Original Message-----
From: Jeff Cummings <jcummings@foxinternet.net>
To: 'emerald@emerald.iea.com' <emerald@emerald.iea.com>
Date: Tuesday, January 06, 1998 2:07 PM
Subject: RE: Any ideas

>It doesn't look like your creating a table join between masteraccounts and
>subaccounts. Try adding a where clause that is something like: where
>masteraccounts.customerid = subaccounts.customerid This would be the
>easiest way to do it.
>
>I think that since you are not creating the join, it is looping endlessly.
>
>Jeff Cummings
>Fox Communications
>jeff@foxinternet.net
>
>On Monday, January 05, 1998 6:34 AM, Richard Sensale
>[SMTP:richards@glasscity.net] wrote:
>> Hi Im still trying to write a web page report using the emerald database
>and
>> I must be missing something. Does anyone see any glaring errors with the
>> following code. It tries to run but either times out the sql server or
>> fills up the transaction log. It reads in much more data than it should.
>> I'd appreciate some help, this simple page has been driving me nuts for
>over
>> 2 weeks and I honestly don't see where the problem is. I must be too
>close
>> to the problem.
>>
>> <% StartDate = Request("StartDate")
>>
>> EndDate = Request("EndDate") %>
>>
>> <% IF (StartDate <> "") AND (EndDate <> "") THEN
>>
>> Server.ScriptTimeout = 600
>>
>> Session.Timeout = 90
>>
>> IF IsObject(Session("EmeraldDSN_conn")) THEN
>>
>> Set conn = Session("EmeraldDSN_conn")
>>
>> ELSE
>>
>> Set conn = Server.CreateObject("ADODB.Connection")
>>
>> conn.open "DSN=EmeraldDSN; UID=Anonymous_WWW; PWD=anonweb4220"
>>
>> Set Session("EmeraldDSN_conn") = conn
>>
>> END IF
>>
>>
>>
>> sql = "SELECT sa.Login, sa.AccountType, ma.CustomerID, ma.FirstName,
>> ma.LastName, ma.Region, ma.ReferredBy, ma.StartDate"
>>
>> sql = sql & " FROM MasterAccounts ma, SubAccounts sa WHERE "
>>
>> sql = sql & "(StartDate >= CONVERT(datetime, '" & StartDate & "')) AND "
>>
>> sql = sql & "(StartDate <= CONVERT(datetime, '" & EndDate & "')) AND "
>>
>>
>> sql = sql & "(ma.Region = 'SEV') AND "
>>
>>
>> sql = sql & "(sa.AccountType = 'SEV-Residential')"
>>
>>
>>
>>
>>
>> Set rs = Server.CreateObject("ADODB.Recordset")
>>
>> rs.Open sql, conn, 3, 3
>>
>> On Error Resume Next
>>
>>
>>
>>
>>
>> END IF %>
>>
>> <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
>>
>> <html>
>>
>> <head>
>>
>> <meta http-equiv="Content-Type"
>>
>> content="text/html; charset=iso-8859-1">
>>
>> <meta name="GENERATOR" content="Microsoft FrontPage 2.0">
>>
>> <title>SEV User Report Residential</title>
>>
>> </head>
>>
>> <body bgcolor="#FFFFFF" bgproperties="fixed">
>>
>> <p align="center"><font color="#0000FF" face="Arial"><strong><% IF
>> (StartDate = "") OR (EndDate = "") THEN %></strong></font><font
>>
>> color="#0000FF" size="4" face="Arial"><strong>SEV Residential
>>
>> User Report</strong></font></p>
>>
>> <form action="sev_user_res.asp" method="POST">
>>
>> <div align="center"><center><table border="0">
>>
>> <tr>
>>
>> <td><font color="#000000" face="Arial"><strong>Starting
>>
>> Date:</strong></font><font face="Arial">(mm/dd/yy)</font></td>
>>
>> <td><font face="Arial"><input type="text" size="10"
>>
>> name="StartDate"></font></td>
>>
>> </tr>
>>
>> <tr>
>>
>> <td><font color="#000000" face="Arial"><strong>Ending
>>
>> Date:</strong></font><font face="Arial">(mm/dd/yy)</font></td>
>>
>> <td><font face="Arial"><input type="text" size="10"
>>
>> name="EndDate" value="<% =Date %>"></font></td>
>>
>> </tr>
>>
>> </table>
>>
>> </center></div><p align="center"><font face="Arial"><input
>>
>> type="submit" name="B1" value="View Report"></font></p>
>>
>> </form>
>>
>> <p>&nbsp;</p>
>>
>> <p><font face="Arial"><% ELSE %></font></p>
>>
>> <p align="center"><font color="#FF0000" size="5"
>face="Arial"><strong>User
>>
>> Report For Period ( <% = StartDate%> through <% = EndDate
>> %>)</strong></font></p>
>>
>> <p align="center"><font color="#FF0000" size="5" face="Arial"><strong>New
>>
>> users will have NEW in the Referred by column</strong></font></p>
>>
>> <p align="center"><font color="#FF0000" size="5" face="Arial"><strong>All
>>
>> others are existing SEV users.</strong></font></p>
>>
>> <div align="center"><center>
>>
>> <table border="1" cellpadding="2" cellspacing="1" width="100%"
>>
>> bgcolor="#00FFFF">
>>
>> <tr>
>>
>> <td bgcolor="#FFFF00"><font face="Arial"><strong>First
>>
>> Name</strong></font></td>
>>
>> <td bgcolor="#FFFF00"><font face="Arial"><strong>Last
>>
>> Name</strong></font></td>
>>
>> <td bgcolor="#FFFF00"><font
>face="Arial"><strong>Region</strong></font></td>
>>
>> <td bgcolor="#FFFF00"><font face="Arial"><strong>Referred
>>
>> By</strong></font></td>
>>
>> <td bgcolor="#FFFF00"><font face="Arial"><strong>Signup
>>
>> Date/Time</strong></font></td>
>>
>> <td bgcolor="#FFFF00"><font face="Arial"><strong>Login
>> ID</strong></font></td>
>>
>> </tr>
>>
>> <% On Error Resume Next
>>
>> rs.MoveFirst
>>
>> do while not rs.EOF %> <tr>
>>
>> <td><font face="Arial"><strong><% = rs.Fields("FirstName").Value
>> %></strong></font></td>
>>
>> <td><font face="Arial"><strong><% = rs.Fields("LastName").Value %>
>> </strong></font></td>
>>
>> <td><font face="Arial"><strong><% = rs.Fields("Region").Value
>> %></strong></font></td>
>>
>> <td><font face="Arial"><strong><% = rs.Fields("ReferredBy").Value
>> %></strong></font></td>
>>
>> <td><font face="Arial"><strong><% = rs.Fields("StartDate").Value
>> %></strong></font></td>
>>
>> <td><font face="arial"><strong><% = rs.Fields("Login").Value
>> %></strong></font></td>
>>
>> </tr>
>>
>> <font face="Arial"><strong><% rs.MoveNext
>>
>> loop %></strong></font>
>>
>> </table>
>>
>> </center></div>
>>
>> <form action="new_users.asp" method="POST">
>>
>> <p align="center"><font face="Arial"><strong><input
>>
>> type="submit" name="B1" value="New Report"></strong></font></p>
>>
>> </form>
>>
>> <p><font face="Arial"><% END IF %></font></p>
>>
>> </body>
>>
>> </html>
>>
>>
>>
>> ----------------------------------------------------------
>> Emerald Mailing List listserver@emerald.iea.com
>
> ----------------------------------------------------------
> Emerald Mailing List listserver@emerald.iea.com
>