|
 |
asp_databases thread: Problems with output...(long)
Message #1 by "Matt Besseling" <mattb@h...> on Thu, 22 Jun 2000 9:31:30
|
|
Sorry for the long message, but it's the only way an idiot like me can
explain this problem:
I have a database of insurance clients who need to see their personal
details, insurance policies, and the cover for each policy. It is all
stored in tables Cients, PolicyDetails and CoverDetails respectively. They
use a username and password to access these.
The problem is that it only gives cover details for the first policy, and
thereafter only outputs the policy details, without any cover. I'm sure
this is just a matter of where to put the CoverDetails code in the asp
file.
I have included the section of code from the the output.asp file.
Set MyConnection = Server.CreateObject("ADODB.Connection")
MyConnection.Open "Indigo"
Set MyRecordSet = Server.CreateObject("ADODB.Recordset")
Dim UsernameValue
Dim PasswordValue
Dim ClientSessionID
UsernameValue = Request.Form("UsernameValue")
PasswordValue = Request.Form("PasswordValue")
SQLQuery = "SELECT * FROM Clients WHERE Username='" & UsernameValue & "'
AND Password ='" & PasswordValue & "'"
Set RSClientList = MyConnection.Execute(SQLQuery)
SQLQuery2 = "SELECT * FROM PolicyDetails Where ClientID=" &
RSClientList("ClientID")
Set demo = MyConnection.Execute(SQLQuery2)
SQLQuery3 = "SELECT * FROM CoverDetails Where PolNo='" & demo("PolNo") &
"'"
Set Cover = MyConnection.Execute(SQLQuery3)
%>
<% Do While Not RSClientList.EOF %>
<B><%= RSClientList("Title")%>
<%= RSClientList("CLFName") & " "%>
<%= RSClientList("CLSName")%></B><BR>
<A HREF="mailto:">
<%= RSClientList("Email")%><BR>
</A>
<B> Identity Number: </B>
<%= RSClientList("IDNo")%><BR>
<B>Telephone Home:</B>
<%= RSClientList("Tel Work")%><BR>
<B>Telephone Work:</B>
<%= RSClientList("Tel Home")%><BR>
<B>Cellphone Number:</B>
<%= RSClientList("Cell")%><BR>
<B>Fax Number:</B>
<%= RSClientList("Fax")%><BR>
<B>Postal Address:</B>
<%= RSClientList("PosAddress1")%><BR>
<B>Postal Address:</B>
<%= RSClientList("PosAddress2")%><BR>
<B>Postal Address:</B>
<%= RSClientList("PosAddress3")%><BR>
<B>Postal Address:</B>
<%= RSClientList("PosAddress4")%><BR>
<B>Residential Address:</B>
<%= RSClientList("ResAddress1")%><BR>
<B>Residential Address:</B>
<%= RSClientList("ResAddress2")%><BR>
<B>Residential Address:</B>
<%= RSClientList("ResAddress3")%><BR>
<B>Residential Address:</B>
<%= RSClientList("ResAddress4")%><BR>
<B>Postal Code:</B>
<%= RSClientList("PCode")%><BR>
<B>Client ID:</B>
<%= RSClientList("ClientID")%><BR>
<%
RSClientList.MoveNext
Loop
%>
<% Do While Not demo.EOF %>
<br>
<B>Policy No:</B>
<%= demo("PolNo")%>
<br>
<B>Premium:</B>
<%= demo("Premium")%>
<br>
<B>Annual/Monthly:</B>
<%= demo("Annual/Monthly")%>
<br>
<B>Company:</B>
<%= demo("Company")%>
<br>
<B>Inception Date:</B>
<%= demo("InceptionDate")%>
<br>
<B>Renewal Date:</B>
<%= demo("RenewalDate")%>
<br>
<% Do While Not cover.EOF %>
<br>
<B>Class of Cover:</B>
<%= cover("Class of Cover")%>
<br>
<B>Details:</B>
<%= cover("Details")%>
<br>
<B>Sum Insured:</B>
<%= cover("Sum Insured")%>
<br>
<B>Premium:</B>
<%= cover("Premium")%>
<br>
<%
cover.MoveNext
Loop
%>
<%
demo.MoveNext
Loop
%>
SEVERE gratitude to anyone who can help me. It is driving me crazy.
Regards
Matt Besseling
Message #2 by charles.nadeau@d... on Thu, 22 Jun 2000 20:13:26 -0400
|
|
Matt,
What is the structure of your tables (fields name, data type)? Maybe
"shaping" the data could help. Or just a joint query.
Charles
> =AB If we could travel in time, then history would become an
> experimental science.=BB, Carl Sagan
>
> Charles-E. Nadeau Ph.D.
> charlesnadeau@h...
> World Information Network (WIN)
> Charles.NADEAU@d...
> Regional System Manager
> Asia pacific
> Consulate general of Canada Phones:
> 13th Floor Tower 1 MITNET: 343-3481
> Exchange Square MITNET Fax: 343-3441
> 8 Connaught Place Office: +852
> 2847-7481
> Hong Kong Fax: +852
> 2847-7441
>
-----Original Message-----
From: Matt Besseling
Sent: 22 juin, 2000 17:32
To: ASP Databases
Subject: [asp_databases] Problems with output...(long)
Sorry for the long message, but it's the only way an idiot like me can
explain this problem:
I have a database of insurance clients who need to see their personal
details, insurance policies, and the cover for each policy. It is all
stored in tables Cients, PolicyDetails and CoverDetails respectively.
They
use a username and password to access these.
The problem is that it only gives cover details for the first policy,
and
thereafter only outputs the policy details, without any cover. I'm sure
this is just a matter of where to put the CoverDetails code in the asp
file.
I have included the section of code from the the output.asp file.
Set MyConnection =3D Server.CreateObject("ADODB.Connection")
MyConnection.Open "Indigo"
Set MyRecordSet =3D Server.CreateObject("ADODB.Recordset")
Dim UsernameValue
Dim PasswordValue
Dim ClientSessionID
UsernameValue =3D Request.Form("UsernameValue")
PasswordValue =3D Request.Form("PasswordValue")
SQLQuery =3D "SELECT * FROM Clients WHERE Username=3D'" & UsernameValue
& "'
AND Password =3D'" & PasswordValue & "'"
Set RSClientList =3D MyConnection.Execute(SQLQuery)
SQLQuery2 =3D "SELECT * FROM PolicyDetails Where ClientID=3D" &
RSClientList("ClientID")
Set demo =3D MyConnection.Execute(SQLQuery2)
SQLQuery3 =3D "SELECT * FROM CoverDetails Where PolNo=3D'" &
demo("PolNo") &
"'"
Set Cover =3D MyConnection.Execute(SQLQuery3)
%>
<% Do While Not RSClientList.EOF %>
<B><%=3D RSClientList("Title")%>
<%=3D RSClientList("CLFName") & " "%>
<%=3D RSClientList("CLSName")%></B><BR>
<A HREF=3D"mailto:">
<%=3D RSClientList("Email")%><BR>
</A>
<B> Identity Number: </B>
<%=3D RSClientList("IDNo")%><BR>
<B>Telephone Home:</B>
<%=3D RSClientList("Tel Work")%><BR>
<B>Telephone Work:</B>
<%=3D RSClientList("Tel Home")%><BR>
<B>Cellphone Number:</B>
<%=3D RSClientList("Cell")%><BR>
<B>Fax Number:</B>
<%=3D RSClientList("Fax")%><BR>
<B>Postal Address:</B>
<%=3D RSClientList("PosAddress1")%><BR>
<B>Postal Address:</B>
<%=3D RSClientList("PosAddress2")%><BR>
<B>Postal Address:</B>
<%=3D RSClientList("PosAddress3")%><BR>
<B>Postal Address:</B>
<%=3D RSClientList("PosAddress4")%><BR>
<B>Residential Address:</B>
<%=3D RSClientList("ResAddress1")%><BR>
<B>Residential Address:</B>
<%=3D RSClientList("ResAddress2")%><BR>
<B>Residential Address:</B>
<%=3D RSClientList("ResAddress3")%><BR>
<B>Residential Address:</B>
<%=3D RSClientList("ResAddress4")%><BR>
<B>Postal Code:</B>
<%=3D RSClientList("PCode")%><BR>
<B>Client ID:</B>
<%=3D RSClientList("ClientID")%><BR>
<%
RSClientList.MoveNext
Loop
%>
<% Do While Not demo.EOF %>
<br>
<B>Policy No:</B>
<%=3D demo("PolNo")%>
<br>
<B>Premium:</B>
<%=3D demo("Premium")%>
<br>
<B>Annual/Monthly:</B>
<%=3D demo("Annual/Monthly")%>
<br>
<B>Company:</B>
<%=3D demo("Company")%>
<br>
<B>Inception Date:</B>
<%=3D demo("InceptionDate")%>
<br>
<B>Renewal Date:</B>
<%=3D demo("RenewalDate")%>
<br>
<% Do While Not cover.EOF %>
<br>
<B>Class of Cover:</B>
<%=3D cover("Class of Cover")%>
<br>
<B>Details:</B>
<%=3D cover("Details")%>
<br>
<B>Sum Insured:</B>
<%=3D cover("Sum Insured")%>
<br>
<B>Premium:</B>
<%=3D cover("Premium")%>
<br>
=09
<%
cover.MoveNext
Loop
%>
<%
demo.MoveNext
Loop
%>
SEVERE gratitude to anyone who can help me. It is driving me crazy.
Regards
Matt Besseling
|
|
|
View Cart
