Calculating field in ASP

G

Guest

I have an Access database used to track donor pledges. In it, there is a
table that contains three fields for each donor: Gift_Amount, Gift_Per_Year,
and Matching_Gift_Ratio.

The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).

A total Pledge for all donors would just sum up the calculated values.

My goal is to create an ASP that would show this total amount pledged. Here
is the ASP that I have created so far:

***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "Careathon_SP2004"

'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"

'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon

'Loop through the recordset
Do While not rsGiving.EOF

'Calculate total donor pledge and add to cumulative total - THIS IS WHERE
THE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))

'Move to the next record in the recordset
rsGiving.MoveNext

Loop

'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)

'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")


'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************

However, when I run this page, I get the following error:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch

It occurs on the line with the calculations.

What do I need to change in order for this page to work correctly?
 
C

Cowboy \(Gregory A. Beamer\)

Why not:

SELECT DonorID, ((Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1))
FROM TableName

AND

SELECT SUM(GiftAmount) FROM TableName

Run these two queries and you do not have to calculate in ASP.

NOTE: I generally develop against SQL Server (MSDE is also acceptable) or
Oracle, not Access, so you may have to tweak the SQL.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
T

tpscharf

What I need is the first query to calculate (Gift_Amount * Gift_Per_Year) *
(Matching_Gift_Ratio + 1)) for each donor, and then a second query to sum
the results of the first query.
 
C

Chris Hohmann

I have an Access database used to track donor pledges. In it, there is a
table that contains three fields for each donor: Gift_Amount, Gift_Per_Year,
and Matching_Gift_Ratio.

The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).

A total Pledge for all donors would just sum up the calculated values.

My goal is to create an ASP that would show this total amount pledged. Here
is the ASP that I have created so far:

***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "Careathon_SP2004"

'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"

'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon

'Loop through the recordset
Do While not rsGiving.EOF

'Calculate total donor pledge and add to cumulative total - THIS IS WHERE
THE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))

'Move to the next record in the recordset
rsGiving.MoveNext

Loop

'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)

'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")


'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************

However, when I run this page, I get the following error:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch

It occurs on the line with the calculations.

What do I need to change in order for this page to work correctly?
Click to expand...

You should perform the calculation in the database. Save the following
query to your database.
[qryPledgeTotal]
SELECT
SUM(Gift_Amount * Gifts_Per_Year * (Matching_Gift_Ratio + 1)) AS
PledgeTotal
FROM
Giving
WHERE
Gift_Amount > 0 AND
Gifts_Per_Year > 0

Here's how you call it:
[PledgeTotal.asp]
<%
Dim sConn,cn,rs,PledgeTotal
sConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open sConn
cn.qryPledgeTotal rs
PledgeTotal = rs(0).Value
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Response.Write PledgeTotal
%>

In answer to your original question, the error may be occurring because
you are referencing a field object and not it's value when performing
the calculation. Specifically the line in question should look like
this:

totalamt = totalamt + rsGiving("Gift_Amount").Value *
rsGiving("Gifts_Per_Year").Value *
(rsGiving("Matching_Gift_Ratio").Value + 1))

Note: Please consider using the native Jet OLE DB Provider. Here's a
related article:
http://aspfaq.com/2126

HTH
-Chris Hohmann
 
T

tpscharf

I already had the query in the database. In the asp tutorials, I had gotten
the impression that I couldn't connect to Access queries, only tables. So,
I was trying to recreate the queries in asp, when all I had to do was
connect to to that query. Thanks for showing me that little bit of code
that makes it work.


Chris Hohmann said:
I have an Access database used to track donor pledges. In it, there is a
table that contains three fields for each donor: Gift_Amount, Gift_Per_Year,
and Matching_Gift_Ratio.

The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).

A total Pledge for all donors would just sum up the calculated values.

My goal is to create an ASP that would show this total amount pledged. Here
is the ASP that I have created so far:

***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "Careathon_SP2004"

'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"

'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon

'Loop through the recordset
Do While not rsGiving.EOF

'Calculate total donor pledge and add to cumulative total - THIS IS WHERE
THE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))

'Move to the next record in the recordset
rsGiving.MoveNext

Loop

'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)

'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")


'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************

However, when I run this page, I get the following error:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch

It occurs on the line with the calculations.

What do I need to change in order for this page to work correctly?
Click to expand...

You should perform the calculation in the database. Save the following
query to your database.
[qryPledgeTotal]
SELECT
SUM(Gift_Amount * Gifts_Per_Year * (Matching_Gift_Ratio + 1)) AS
PledgeTotal
FROM
Giving
WHERE
Gift_Amount > 0 AND
Gifts_Per_Year > 0

Here's how you call it:
[PledgeTotal.asp]
<%
Dim sConn,cn,rs,PledgeTotal
sConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open sConn
cn.qryPledgeTotal rs
PledgeTotal = rs(0).Value
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Response.Write PledgeTotal
%>

In answer to your original question, the error may be occurring because
you are referencing a field object and not it's value when performing
the calculation. Specifically the line in question should look like
this:

totalamt = totalamt + rsGiving("Gift_Amount").Value *
rsGiving("Gifts_Per_Year").Value *
(rsGiving("Matching_Gift_Ratio").Value + 1))

Note: Please consider using the native Jet OLE DB Provider. Here's a
related article:
http://aspfaq.com/2126

HTH
-Chris Hohmann
Click to expand...
 
B

Brynn

why are these answers in both groups ... dork


I have an Access database used to track donor pledges. In it, there is a
table that contains three fields for each donor: Gift_Amount, Gift_Per_Year,
and Matching_Gift_Ratio.

The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).

A total Pledge for all donors would just sum up the calculated values.

My goal is to create an ASP that would show this total amount pledged. Here
is the ASP that I have created so far:

***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "Careathon_SP2004"

'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"

'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon

'Loop through the recordset
Do While not rsGiving.EOF

'Calculate total donor pledge and add to cumulative total - THIS IS WHERE
THE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))

'Move to the next record in the recordset
rsGiving.MoveNext

Loop

'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)

'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")


'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************

However, when I run this page, I get the following error:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch

It occurs on the line with the calculations.

What do I need to change in order for this page to work correctly?
Click to expand...
 
C

Chris Hohmann

Brynn said:
why are these answers in both groups ... dork
Click to expand...

These answers are in both groups because the OP (original poster)
cross-posted to both the m.p.i.asp.db and m.p.i.asp.general groups. Had
the answer(s) not been posted to both groups, it's conceivable that
someone may have wasted their time by attempting to explore a solution
to a problem that had already been resolved.
 
B

Bob Lehmann

Not quite bright enough to figure that one out, Brynn?
So, who's the loser - dork?

Bob Lehmann
 
B

Brynn

I though tradition stated that you guys didn't answer cross-posting
spammer posts ... LOL
 
B

Brynn

I love you Bob ... could you go back to the thread about the trolls
and post some more ... that soap opera was too funny
 
B

Brynn

I have been on google doing a search for Bob Lehnmann ... from what I
can tell from all the posts ... it seems like it has just been the
past year where you turned into an asshole ... what has happened in
the last year to you Bob ... LOL

prick
 
B

Bob Barrows

Brynn said:
I though tradition stated that you guys didn't answer cross-posting
spammer posts ... LOL
Click to expand...
No, it's multiposts that get our goats. I'll leave it to you to look up the
difference. ;-)
 
R

Roy Danon

Why don't you just do the calculation using the access?

for example :

"select ((giving.Gift_Amount * giving.Gift_Per_Year) *
(givingMatching_Gift_Ratio + 1)) as pledgeamount from giving"

it makes the work much easier and wastes less resources.

Roy.

Chris Hohmann said:
I have an Access database used to track donor pledges. In it, there is a
table that contains three fields for each donor: Gift_Amount, Gift_Per_Year,
and Matching_Gift_Ratio.

The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).

A total Pledge for all donors would just sum up the calculated values.

My goal is to create an ASP that would show this total amount pledged. Here
is the ASP that I have created so far:

***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "Careathon_SP2004"

'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"

'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon

'Loop through the recordset
Do While not rsGiving.EOF

'Calculate total donor pledge and add to cumulative total - THIS IS WHERE
THE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))

'Move to the next record in the recordset
rsGiving.MoveNext

Loop

'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)

'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")


'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************

However, when I run this page, I get the following error:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch

It occurs on the line with the calculations.

What do I need to change in order for this page to work correctly?
Click to expand...

You should perform the calculation in the database. Save the following
query to your database.
[qryPledgeTotal]
SELECT
SUM(Gift_Amount * Gifts_Per_Year * (Matching_Gift_Ratio + 1)) AS
PledgeTotal
FROM
Giving
WHERE
Gift_Amount > 0 AND
Gifts_Per_Year > 0

Here's how you call it:
[PledgeTotal.asp]
<%
Dim sConn,cn,rs,PledgeTotal
sConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open sConn
cn.qryPledgeTotal rs
PledgeTotal = rs(0).Value
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Response.Write PledgeTotal
%>

In answer to your original question, the error may be occurring because
you are referencing a field object and not it's value when performing
the calculation. Specifically the line in question should look like
this:

totalamt = totalamt + rsGiving("Gift_Amount").Value *
rsGiving("Gifts_Per_Year").Value *
(rsGiving("Matching_Gift_Ratio").Value + 1))

Note: Please consider using the native Jet OLE DB Provider. Here's a
related article:
http://aspfaq.com/2126

HTH
-Chris Hohmann
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Solution For Retrieving Data From Linked Table 2
asp and access db - edit textarea 2
how to store asp code in access and call back as text 3
Current Recordset does not support updating. 2
Can I use a Query stored in an MS Access database from an ASP page? 1
Forgotten Password script 1
add data to access - fail 3
Need help with ASP script. 2

Members online

No members online now.
Total: 35 (members: 1, guests: 34)
Robots: 197

Forum statistics

Threads
473,766
Messages
2,569,569
Members
45,042
Latest member
icassiem

Latest Threads

Top