|
| |
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
 |
Incorrect results - 10/29/2003 3:23:07
Hi, here is the story:
I have 2 tables charge and payment, table charge has student_number field
and charges field, payment has student_number field and payment field.
I’m using the following code to get the total charges and total payments
and the balance. The problem starts when one table has more row than
other one in this case total charges or total payments will be two times more!
I give you an example:
If there is a charge of $1000 and $500 payment the result is:
Total payment = 500
Total charge = 1000
Total Balance is 500
Which is ok but if I add an other $1000 charge but no payment the result is:
Total payment = 1000
Total charge = 2000
Total Balance is 1000
And so on please have a look and let me know what is wrong! Thanks a lot.
<!--#include file="_fpclass/fpdblib.inc"-->
<%fp_sQry="SELECT * FROM charge, pay WHERE charge.student_number=pay.student_number"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="new_page_1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<%dim mvalue: mvalue=0%>
<%dim mvalue1: mvalue1=0%>
<%dim mvalue2: mvalue2=0%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<%MValue = MValue + cInt(FP_FieldVal(fp_rs,"payment"))%>
<%MValue1 = MValue1 + cInt(FP_FieldVal(fp_rs,"charges"))%>
<%MValue2 = MValue1-MValue%>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
Total payment = <%=MValue%> <br>
Total charge = <%=MValue1%> <br>
Total Balance is <%=MValue2%> <br>
|
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/30/2003 4:34:03
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT SUM(charges), SUM(payment) FROM charge, pay WHERE (student_number = '::student_number::')"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="new_page_1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<%dim mvalue: mvalue=0%>
<%dim mvalue1: mvalue1=0%>
<%dim mvalue2: mvalue2=0%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<%MValue = MValue + cInt(FP_FieldVal(fp_rs,"payment"))%>
<%MValue1 = MValue1 + cInt(FP_FieldVal(fp_rs,"charges"))%>
<%MValue2 = MValue1-MValue%>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
Total payment = <%=MValue%> <br>
Total charge = <%=MValue1%> <br>
Total Balance is <%=MValue2%> <br>
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] The specified field 'student_number' could refer to more than one table listed in the FROM clause of your SQL statement.
Number: -2147467259 (0x80004005)
Source: Microsoft OLE DB Provider for ODBC Drivers
Total payment = 0
Total charge = 0
Total Balance is 0
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/30/2003 12:39:28
Any Idea Please! Thanks
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/30/2003 16:08:00
Hi Spooky here is the final code I double checked the fields and I'm getting the following error if I put SUM and if I remove them no error
but calculation is wrong please help!
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT sum(charges), sum(payment) FROM charge,pay WHERE (charge.student_number = '::student_number::')"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="new_page_1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%> <%dim mvalue: mvalue=0%>
<%dim mvalue1: mvalue1=0%>
<%dim mvalue2: mvalue2=0%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<%MValue = MValue + cInt(FP_FieldVal(fp_rs,"payment"))%>
<%MValue1 = MValue1 + cInt(FP_FieldVal(fp_rs,"charges"))%>
<%MValue2 = MValue1-MValue%>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
Total payment = <%=MValue%> <br>
Total charge = <%=MValue1%> <br>
Total Balance is <%=MValue2%> <br>
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/mvalue/_fpclass/fpdblib.inc, line 48
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/30/2003 18:49:24
Hi Spooky now I'm getting this:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Circular reference caused by alias 'charges' in query definition's SELECT list.
Number: -2147467259 (0x80004005)
Source: Microsoft OLE DB Provider for ODBC Drivers
Total payment = 0
Total charge = 0
Total Balance is 0
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT SUM(charges)as charges, SUM(payment)as payment FROM charge,pay WHERE (charge.student_number = '::student_number::')"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="new_page_1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<%dim MValue: mvalue=0%>
<%dim MValue1: mvalue1=0%>
<%dim MValue2: mvalue2=0%> <!--#include file="_fpclass/fpdbrgn1.inc"-->
<%MValue = (FP_FieldVal(fp_rs,"payment"))%>
<%MValue1 = (FP_FieldVal(fp_rs,"charges"))%>
<%MValue2 = MValue1-MValue%><!--#include file="_fpclass/fpdbrgn2.inc"-->
Total payment = <%=MValue%> <br>
Total charge = <%=MValue1%> <br>
Total Balance is <%=MValue2%> <br>
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/30/2003 19:15:35
I'm sorry spooky but if I change charges to yourcharge it means I have
to change the field's name in table! as a result sum(charge) wont work!
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'FP_SetLocaleForPage'
/mvalue/_fpclass/fpdbrgn1.inc, line 4
< Message edited by Light -- 10/30/2003 7:15:56 PM >
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/30/2003 19:41:55
Thanks a lot for your time now there is no error but calculation is the same as before! here is the code and result:
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT SUM(charges)as yourcharges, SUM(payment)as yourpayment FROM charge,pay WHERE (charge.student_number = '::student_number::')"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="new_page_1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<%dim MValue: mvalue=0%>
<%dim MValue1: mvalue1=0%>
<%dim MValue2: mvalue2=0%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<%MValue = (FP_FieldVal(fp_rs,"yourpayment"))%>
<%MValue1 = (FP_FieldVal(fp_rs,"yourcharges"))%>
<%MValue2 = MValue1-MValue%>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
Total payment = <%=MValue%> <br>
Total charge = <%=MValue1%> <br>
Total Balance is <%=MValue2%> <br>
Total payment = 1000 "has to be $500"
Total charge = 2000 "this is ok"
Total Balance is 1000 "has to be $1500"
On charge table there is 2 charges of $1000
On pay table there is 1 payment of $500
< Message edited by Light -- 10/30/2003 9:12:13 PM >
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/30/2003 22:03:38
Here is the error:
Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/mvalue/pay_charge1.asp, line 4, column 145
fp_sQry="SELECT Sum(charge.Charges) AS YourCharges, Sum(select pay.payment from pay where pay.student_number='::student_number::') AS YourPayment
------------------------------------------------------------------------------------------------------------------------------------------------^
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/30/2003 23:04:33
Spooky you’re the man I wish I had 1/1000,000 of your knowledge and your patient! Thanks now it’s working great, I’m really happy!
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/31/2003 0:28:18
quote:
SELECT Sum(charge.Charges) AS YourCharges, Sum(select pay.payment from pay where pay.student_number='::student_number::') AS YourPayment FROM charge where charge.student_number ='::student_number::';
Sorry Spooky I'm back! looks like if there is only one record in pay table
works ok but if I have more than one record in pay table I get this:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] At most one record can be returned by this subquery.
Number: -2147467259 (0x80004005)
Source: Microsoft OLE DB Provider for ODBC Drivers
Total payment = 0
Total charge = 0
Total Balance is 0
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/31/2003 0:52:05
quote:
fp_sQry="SELECT Sum(charge.Charges) AS YourCharges, (Sum(select pay.payment) from pay where pay.student_number='::student_number::') AS YourPayment FROM charge where charge.student_number ='::student_number::';"
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression 'Sum(select pay.payment)'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
Total payment = 0
Total charge = 0
Total Balance is 0
< Message edited by Light -- 10/31/2003 2:09:57 AM >
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/31/2003 3:55:11
It's me again! sorry! the only issue is if there is only charges and no payment or only payment but no charge also if there is record for charges or payments this is the result:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: '[string: " "]'
/mvalue/pay_charge1.asp, line 26
If there is no match for charge and payment for specific student_number I get the same error!
< Message edited by Light -- 10/31/2003 4:12:10 AM >
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/31/2003 15:16:54
Hi, is there any other way to do this? thanks
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/31/2003 16:52:59
I think line 26? /mvalue/pay_charge1.asp, line 26 refer to:
<%MValue2 = MValue1-MValue%>
On the database they are as text! is there any way to get value 0
if there is no record returned? thanks
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/31/2003 18:21:19
quote:
<%
If MValue1 = "" then MValue1 = 0
If MValue = "" then MValue = 0
MValue2 = MValue1-MValue
%>
Hi Spooky I did put the code in different area but the error message is
the same how would be the same suyntax for
<%MValue = (FP_FieldVal(fp_rs,"yourpayment"))%>
<%MValue1 = (FP_FieldVal(fp_rs,"yourcharges"))%> ?
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: '[string: " "]'
| |
|
|
|
Light
Posts: 199
Joined: 3/28/2002
From: Earth
Status: offline
|
RE: Incorrect results - 10/31/2003 18:40:08
I modifed the code to:
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT Sum(charge.Charges) AS YourCharges, (select sum(pay.payment) from pay where pay.student_number='::student_number::') AS YourPayment FROM charge where charge.student_number ='::student_number::';"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="bursar"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<%dim MValue: mvalue=0%>
<%dim MValue1: mvalue1=0%>
<%dim MValue2: mvalue2=0%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<%IF FP_FieldVal(fp_rs," yourpayment" ) = " " THEN%>
<%MValue=0%>
<%END IF%>
<%IF FP_FieldVal(fp_rs," yourcharges" ) = " " THEN%>
<%MValue1=0%>
<%END IF%>
<%MValue = (FP_FieldVal(fp_rs,"yourpayment"))%>
<%MValue1 = (FP_FieldVal(fp_rs,"yourcharges"))%>
<%MValue2 = MValue1-MValue%>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
Total payment = <%=MValue%> <br>
Total charge = <%=MValue1%> <br>
Total Balance is <%=MValue2%> <br>
the error is:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/asp/_fpclass/fpdblib.inc, line 48
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
