Incorrect results (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Light -> 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>



Spooky -> RE: Incorrect results (10/29/2003 13:38:57)

<%fp_sQry="SELECT sum(charges), sum(payment) FROM charge, pay WHERE .....



Light -> 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 -> RE: Incorrect results (10/30/2003 12:39:28)

Any Idea Please! Thanks



Spooky -> RE: Incorrect results (10/30/2003 14:42:49)

WHERE (charge.student_number= ........

Both tables have a student number, so you need to be clear which to use



Light -> 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



Spooky -> RE: Incorrect results (10/30/2003 16:17:46)

<%MValue = (FP_FieldVal(fp_rs,"payment"))%>
<%MValue1 = (FP_FieldVal(fp_rs,"charges"))%>
<%MValue2 = MValue1-MValue%>

Do all the columns exist in the database that are named?



Light -> RE: Incorrect results (10/30/2003 16:35:48)

quote:

<%MValue = (FP_FieldVal(fp_rs,"payment"))%>
<%MValue1 = (FP_FieldVal(fp_rs,"charges"))%>
<%MValue2 = MValue1-MValue%>


Hi Spooky I did change the code still I got this:[:(]
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



Spooky -> RE: Incorrect results (10/30/2003 17:26:48)

Ahh sorry -
fp_sQry="SELECT sum(charges) as charges, sum(payment) as payment from .....



Light -> 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>



Spooky -> RE: Incorrect results (10/30/2003 19:09:02)

ARRRGGGGHHH! 

<% 
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>



Light -> 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



Spooky -> RE: Incorrect results (10/30/2003 19:28:59)

No, you are using an alias - only the results change :

<%MValue = (FP_FieldVal(fp_rs,"yourpayment"))%>
<%MValue1 = (FP_FieldVal(fp_rs,"yourcharges"))%>



Light -> 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



Spooky -> RE: Incorrect results (10/30/2003 21:33:59)

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::';



Light -> 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
------------------------------------------------------------------------------------------------------------------------------------------------^



Spooky -> RE: Incorrect results (10/30/2003 22:41:38)

Did you put it all on one line?
this forum wraps the lines when its not supposed to. 
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::';



Light -> 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!



Spooky -> RE: Incorrect results (10/30/2003 23:08:59)

Oh thank god...... [8|]

[sm=lol.gif]



Light -> 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



Spooky -> RE: Incorrect results (10/31/2003 0:46:16)

Sum it inside the subquery :

(select Sum(pay.payment) from pay where .....



Light -> 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



Spooky -> RE: Incorrect results (10/31/2003 2:08:47)

Like so :

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::';



Light -> 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!



Light -> RE: Incorrect results (10/31/2003 15:16:54)

Hi, is there any other way to do this? thanks



Spooky -> RE: Incorrect results (10/31/2003 15:22:02)

Whats line 26?
/mvalue/pay_charge1.asp, line 26

The issue will be that the database return is not "0" and is being treated as a string.
As you are treating them as numbers, there needs to be error checking to ensure the value is zero if nothing is returned.



Light -> 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



Spooky -> RE: Incorrect results (10/31/2003 17:53:52)

<%
If MValue1 = "" then MValue1 = 0
If MValue = "" then MValue = 0
MValue2 = MValue1-MValue
%>



Light -> 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 -> 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



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625