?
Solved

How do I add values in a form from values from a database

Posted on 2005-05-12
14
Medium Priority
?
296 Views
Last Modified: 2009-08-24
I have an asp page that displays product info and gets its values from a database.  On the results page are shop fields e.g code, description,price qty etc...   How do I add the values of the products price and quantity together for each row of the table in order to dynamically display a total order price under the product list. For example :-

Code      Description    Price     Qty
12       item 1              £1.00     2
13       item 2               £2.00    3

                                 Total : ?????

The Qty is a textfield that the customer enters the quantity they would like to order.

:)
0
Comment
Question by:djpazza
  • 7
  • 6
14 Comments
 

Expert Comment

by:mlea
ID: 13987872
For this I would use JavaScript. All you need to do is include javascript OnBlur events on each of the QTY fields. These events will all call a routine that gets the values from all the QTY and PRICE fields and updates these totals and then displays them at the bottom of the list. You can use document.write or a read only text field (I prefer the read only text fields) to display the totals. Then when the form is submitted you can then record these values back to your database tables.
0
 
LVL 9

Author Comment

by:djpazza
ID: 13987978
Thanks mlea, any chance you could give me some example code :)  Im not a javascript expert yet :)
0
 
LVL 11

Expert Comment

by:JohnModig
ID: 13990682
First, a note on javascript calculations:
-----------------------
Because form field values are always strings it is necessary to convert them to numbers before summing them. For example if you do:
var a = '1';
var b = '2';
var c = a + b;
Then the variable c would contain '12'.

The Netscape recommended solution is to do the following:
var a = '1';
var b = '2';
var c = (a-0) + (b-0);
Now the variable c would contain '3'.
-----------------------
Ok?
Knowing that, we can now move on to the actual website code :)
-----------------------
Lets say your form looks like this:

<form>
<input type="text" name="field1" value="15">
<input type="text" name="field2" value="16">
<input type="text" name="field3" value="1">
<input type="text" name="field4" value="52">
<input type="button" value="Sum" onClick="sum(this.form)">
</form>

To add those fields together, put this code somewhere between your head tags:

<script language="JavaScript"><!--
function sum(objRef) {
  var result = 0;
  result =+ objRef.field1.value - 0;
  result =+ objRef.field2.value - 0;
  result =+ objRef.field3.value - 0;
  result =+ objRef.field4.value - 0;
  alert(result);
}
//--></script>

Play around with this, if you need more help Ill talk you thru it :P
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:JohnModig
ID: 13990853
A note on using javascript:
One problem you have to keep in mind would be that some users might have javascript disabled. One way to deal with this would be to have a javascript link to the order page, so if the user does not have javascript enabled, they cannot enter the order page. Another possible suggestion would be to do the calculation in server side, using ASP.
-----
Code example, javascript link:
<a href="javascript:location.href='orderpage.asp'">link</a>
-----
Code example, calculation using ASP:
<% Dim field1, field2, fieldSum
field1 = CInt(MyRecordset.Fields("MyField1").Value)    'the CINT function converts the string to a number
field2 = CInt(MyRecordset.Fields("MyField2").Value)
fieldSum = field1 + field2
%>
0
 
LVL 9

Author Comment

by:djpazza
ID: 13991595
thanks but im not sure thats what im after ?

Say for example customer wants 2 of item 1 @ £1 each I would like a total at the bottom of my table.  The total would then change if another product was entered say e.g customer puts 3 in the qty textfield for item 2 @ £2 each the total would change to £8

:)
0
 
LVL 11

Expert Comment

by:JohnModig
ID: 13993637
What I was describing to you in my previous two posts was how to calculate text fields. This is what your question is about, right? I showed you two different methods on how to do just that. My first post shows how to do it using javascript, so that it clculates 'on the fly'. My second post shows how to do it server side using ASP, not 'on the fly', but more reliable.

My examples only shows you how to add two fields up.
If you want to multiply two fields, like you describe above, use the * sign.

This is a more simple example. Please try the following code a you will see what I mean:
-----------------------
<FORM>
<INPUT TYPE="TEXT" NAME="number1"> x
<INPUT TYPE="TEXT" NAME="number2">
<INPUT TYPE="BUTTON" VALUE="=" onClick="this.form.answer.value = (this.form.number1.value - 0) * (this.form.number2.value - 0)">
<INPUT TYPE="TEXT" NAME="answer">
</FORM>
-----------------------
Try that code.
Afterwards, you will probably know how to modify your own. Otherwise, just let me know or past some code for me here, and I'll help you out.

Regards
John
0
 
LVL 9

Author Comment

by:djpazza
ID: 13996188
Thanks for the example.  I dont know how many form fields will be in my form though as its pulled from a database.  I already have code that will send the results of the page including the total amount of the order to an email.  So can I use similar code to display the total on the database results/order page? If so then all I would need is a way to display the (totalPrice) value in the array and have it change each time a different number is entered in qty.


code I already have

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim Code, Description, size, Price, qty

Code = request.form("code")
Description = Request.form("Description")
Size = Request.form("Size")
Price = Request.form("Price")
qty = Request.form("Qty")

Dim ArrCode, ArrDescription, ArrSize, ArrPrice, Arrqty

ArrCode = Split(Code,",")
ArrDescription = Split(Description,",")
ArrSize = Split(Size,",")
ArrPrice = Split(Price,",")
ArrQty = Split(qty,",")

'response.write "Code : " & Ubound(ArrCode) & "<BR>"
'response.write "Desc : " & Ubound(ArrDescription) & "<BR>"
'response.write "Size : " & Ubound(ArrSize) & "<BR>"
'response.write "Price : " & Ubound(ArrPrice) & "<BR>"
'response.write "QTY : " & Ubound(Arrqty) & "<BR>"
'response.end

Dim strBody, TotalQty, TotalPrice

Totalqty = 0
TotalPrice = 0.0

if ubound(ArrCode) <> ubound(ArrDescription) or ubound(ArrCode) <> ubound(ArrQty) or ubound(ArrCode) <> ubound(ArrSize) or ubound(ArrCode) <> ubound(ArrPrice) Then
     Response.write "<br>Number of parameters do not match, you passed:<br>"
     Response.write "&nbsp;" & ubound(arrCode) & " Codes, <br>"
     Response.write "&nbsp;" & ubound(arrDescription) & " Descriptions, <br>"
     Response.write "&nbsp;" & ubound(arrQty) & " Quantities, <br>"
     Response.write "&nbsp;" & ubound(arrSize) & " Sizes, <br>"
     Response.write "&nbsp;" & ubound(arrPrice) & " Prices.<br>"
     Response.End
End If

strBody = "<html><body>"
Strbody = strBody & "<table><tr><th>Code</th><th>Description</th><th>Size</th><th>Price</th><th>Qty</th></tr>"
 for x = 0 to Ubound(ArrCode)
     If cint(ArrQty(x)) <> 0 then
     strBody = strBody & "<tr><td>" & Arrcode(x) & "</td><td>" & ArrDescription(x) & "</td><td>" & ArrSize(x) & "</td><td>" & ArrPrice(x) & "</td><td>" &   ArrQty(x) & "</td></tr>"
   totalqty = totalqty + Int(ArrQty(x))
    TotalPrice = TotalPrice + Cdbl(ArrPrice(x)) * Cint(ArrQty(x))
   End If
  Next
strBody = Strbody & "<tr><th><td colspan=5 align=right>Total Price = " & formatcurrency(totalPrice) & "</TH></tr>"
strBody = strBody & "</body></html>"

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
 <%
 Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
Mailer.RemoteHost = "mrvnet.kundenserver.de"
Mailer.FromName = "test"
Mailer.FromAddress = "test@test.me.uk"
Mailer.AddRecipient "test", "test@hotmail.com"
Mailer.Subject = "Website Order"
Mailer.ContentType = "text/html"
Mailer.bodytext = "Confirmation of Order" & VbCrLf
Mailer.Bodytext = "Ordered by " & Request.Form("customer_number") & VbCrLf & Session("MM_Username") & VbCrLf
Mailer.BodyText = strBody & VbCrLf & VbCrLf  
Mailer.BodyText = "Comments:" & VbCrLf & Request.Form("comments") & VbCrLf
 
if Mailer.SendMail then
Response.Redirect "index.asp"
else
Response.Write "Mail send failure. Error was " & Mailer.Response
end if
%>
</body>
</html>
0
 
LVL 11

Expert Comment

by:JohnModig
ID: 13997651
I need the form code to do this.
Please post the form code, and I will modify it for you.
0
 
LVL 9

Author Comment

by:djpazza
ID: 14008880
Here's my customer product page code

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../Connections/inventive.asp" -->

<%
Dim rsproducts__MMColParam
rsproducts__MMColParam = "1"
If (Session("mm_username") <> "") Then
  rsproducts__MMColParam = Session("mm_username")
End If
%>
<%
Dim rsproducts
Dim rsproducts_numRows

Set rsproducts = Server.CreateObject("ADODB.Recordset")
rsproducts.ActiveConnection = MM_inventive_STRING
rsproducts.Source = "SELECT * FROM mybusiness WHERE customernumber = '" + Replace(rsproducts__MMColParam, "'", "''") + "'"
rsproducts.CursorType = 0
rsproducts.CursorLocation = 2
rsproducts.LockType = 1
rsproducts.Open()

rsproducts_numRows = 0
%>
<%
Dim rsmembers__MMColParam
rsmembers__MMColParam = "1"
If (Session("mm_username") <> "") Then
  rsmembers__MMColParam = Session("mm_username")
End If
%>
<%
Dim rsmembers
Dim rsmembers_numRows

Set rsmembers = Server.CreateObject("ADODB.Recordset")
rsmembers.ActiveConnection = MM_inventive_STRING
rsmembers.Source = "SELECT * FROM Customermybusiness WHERE customernumber = '" + Replace(rsmembers__MMColParam, "'", "''") + "'"
rsmembers.CursorType = 0
rsmembers.CursorLocation = 2
rsmembers.LockType = 1
rsmembers.Open()

rsmembers_numRows = 0
%>
<%
Dim total
Dim total_numRows

Set total = Server.CreateObject("ADODB.Recordset")
total.ActiveConnection = MM_inventive_STRING
total.Source = "SELECT sum(price)  FROM mybusiness"
total.CursorType = 0
total.CursorLocation = 2
total.LockType = 1
total.Open()

total_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsproducts_numRows = rsproducts_numRows + Repeat1__numRows
%>
<%
'  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim rsproducts_total
Dim rsproducts_first
Dim rsproducts_last

' set the record count
rsproducts_total = rsproducts.RecordCount

' set the number of rows displayed on this page
If (rsproducts_numRows < 0) Then
  rsproducts_numRows = rsproducts_total
Elseif (rsproducts_numRows = 0) Then
  rsproducts_numRows = 1
End If

' set the first and last displayed record
rsproducts_first = 1
rsproducts_last  = rsproducts_first + rsproducts_numRows - 1

' if we have the correct record count, check the other stats
If (rsproducts_total <> -1) Then
  If (rsproducts_first > rsproducts_total) Then
    rsproducts_first = rsproducts_total
  End If
  If (rsproducts_last > rsproducts_total) Then
    rsproducts_last = rsproducts_total
  End If
  If (rsproducts_numRows > rsproducts_total) Then
    rsproducts_numRows = rsproducts_total
  End If
End If
%>

<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (rsproducts_total = -1) Then

  ' count the total records by iterating through the recordset
  rsproducts_total=0
  While (Not rsproducts.EOF)
    rsproducts_total = rsproducts_total + 1
    rsproducts.MoveNext
  Wend

  ' reset the cursor to the beginning
  If (rsproducts.CursorType > 0) Then
    rsproducts.MoveFirst
  Else
    rsproducts.Requery
  End If

  ' set the number of rows displayed on this page
  If (rsproducts_numRows < 0 Or rsproducts_numRows > rsproducts_total) Then
    rsproducts_numRows = rsproducts_total
  End If

  ' set the first and last displayed record
  rsproducts_first = 1
  rsproducts_last = rsproducts_first + rsproducts_numRows - 1
 
  If (rsproducts_first > rsproducts_total) Then
    rsproducts_first = rsproducts_total
  End If
  If (rsproducts_last > rsproducts_total) Then
    rsproducts_last = rsproducts_total
  End If

End If
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>my business</title>
<style type="text/css">
<!--
.style9 {font-family: "MS Sans Serif"; font-size: 14px; color: #666666; }
-->
</style>
</head>

<body>

<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%">
  <tr>
    <td width="33%"><table width="246" border="0">
      <tr class="style9">
        <td width="24">&nbsp;</td>
        <td width="212"><div align="center"></div></td>
      </tr>
      <tr class="style9">
        <td>&nbsp;</td>
        <td><div align="center"></div></td>
      </tr>
      <tr class="style9">
        <td>. </td>
        <td><div align="center"></div></td>
      </tr>
      <tr class="style9">
        <td>&nbsp;</td>
        <td><div align="center"></div></td>
      </tr>
      <tr class="style9">
        <td>&nbsp;</td>
        <td><div align="center"></div></td>
      </tr>
    </table></td>
    <td width="33%"><div align="center"><a href="http://www.mybusiness.com">
    <img border="0" src="images/mybusinesslogo.jpg" width="210" height="92"></a></div></td>
    <td width="34%"><p>&nbsp;</p>
    <p>&nbsp;</p></td>
  </tr>
</table>

<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%">
  <tr class="style9">
    <td><div align="right"></div></td>
    <td>trading</td>
    <td><div align="left"><%=(rsmembers.Fields.Item("Trading_Name").Value)%>
    </div></td>
    <td width="3%"><div align="center"></div></td>
    <td width="15%">contact</td>
    <td><div align="left"><%=(rsmembers.Fields.Item("Contacts").Value)%></div></td>
  </tr>
  <tr class="style9">
    <td><div align="right"></div></td>
    <td>Name</td>
    <td><div align="left"><%=(rsmembers.Fields.Item("Customer_Name").Value)%></div></td>
    <td><div align="center"></div></td>
    <td>contact number</td>
    <td><div align="left"><%=(rsmembers.Fields.Item("Telephone_Number").Value)%></div></td>
  </tr>
  <tr class="style9">
    <td width="13%"><div align="right"></div></td>
    <td width="10%">acc no
      <!--** START NAVSTUDIO MENU TAGS (200428) **--></td>
    <td width="26%"><div align="left"><%=(rsmembers.Fields.Item("customernumber").Value)%></div></td>
    <td colspan="2"><div align="center"></div></td>
    <td width="33%"><div align="left"></div></td>
  </tr>
</table>
<div align="center">
 
</div>
<div align="center">
  <div id="vqp_about" style="position:absolute;visibility:hidden;">****JavaScript based drop down DHTML menu generated by NavStudio. (OpenCube Inc. - http://www.opencube.com)****</div>
    <table width="796" border="0">
      <tr>
        <td width="846"><form name="form1" method="post" action="order2.asp">
          <table width="783" border="0">
            <tr bgcolor="#F4F4F4" class="style9">
              <td width="191">product code </td>
              <td width="244">description</td>
              <td width="114">pack size </td>
              <td width="119">price</td>
              <td width="93">qty</td>
            </tr>
            <%
While ((Repeat1__numRows <> 0) AND (NOT rsproducts.EOF))
%>
            <tr>
              <td class="style9"><%=(rsproducts.Fields.Item("CODE").Value)%>
              <input name="code" type="hidden" id="code" value="<%=(rsproducts.Fields.Item("CODE").Value)%>"></td>
              <td class="style9"><%=(rsproducts.Fields.Item("DESCRIPTION").Value)%>
              <input name="description" type="hidden" id="product_description" value="<%=(rsproducts.Fields.Item("DESCRIPTION").Value)%>"></td>
              <td class="style9"><%=(rsproducts.Fields.Item("size").Value)%>
              <input name="size" type="hidden" id="pack" value="<%=(rsproducts.Fields.Item("size").Value)%>"></td>
              <td class="style9"><%=(rsproducts.Fields.Item("PRICE").Value)%>
              <input name="price" type="hidden" id="price" value="<%=(rsproducts.Fields.Item("PRICE").Value)%>"></td>
              <td><input name="Qty" type="text" id="Qty" value="0" size="5"></td>
            </tr>
            <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsproducts.MoveNext()
Wend
%>

            <tr>
              <td class="style9">comments
              <input name="customer_name" type="hidden" id="customer_name" value="<%=(rsmembers.Fields.Item("Customer_Name").Value)%>"></td>
              <td colspan="3" class="style9"><textarea name="comments" cols="75" id="comments"></textarea></td>
              <td class="style9">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="5" class="style9">
                <div align="center">
                  <div align="center"> </font> </div><input name="Submit" type="submit" id="Submit" value="Submit">
                </div></td>
            </tr>
          </table>
        </form></td>
      </tr>
    </table>
  <p>&nbsp;</p>
</div>
</body>

</html>

<%
rsmembers.Close()
Set rsmembers = Nothing
%>
<%
total.Close()
Set total = Nothing
%>
<%
rsproducts.Close()
Set rsproducts = Nothing
%>


0
 
LVL 11

Expert Comment

by:JohnModig
ID: 14014415
The tricky part is that your form field does not have unique names.
But since I don't wanna screw up your arrays, let's do the calculation, using the element index number.
-----------------------------------------------
1. In customer product page, put this between your <head> tags:
-----------------------------------------------
<SCRIPT LANGUAGE="JavaScript"><!--
function sum() {
  var result = 0;
<% 'asp script to find all fields to calculate
dim count, qtyIndex, priceIndex
count = 1
qtyIndex = 4
priceIndex = 3
do until count > rsproducts_total %>
  result = (result-0) + ((document.form1.elements[<%response.write(qtyIndex)%>].value - 0)*(document.form1.elements[<%response.write(priceIndex)%>].value - 0));
<%
count = count + 1
qtyIndex = qtyIndex + 5
priceIndex =  priceIndex + 5
loop %>  
  document.form1.total.value = result;
}
//--></SCRIPT>

-----------------------------------------------
2. In customer product page, change line 242 (<td><input name="Qty" type="text" id="Qty" value="0" size="5"></td>) to this:
-----------------------------------------------
<td><input name="Qty" type="text" id="Qty" value="0" size="5" onchange="sum()"></td>

-----------------------------------------------
3. In customer product page, change line 255 (<td class="style9">&nbsp;</td>) to this:
-----------------------------------------------
<td class="style9">Sum<br><input type="text" name="total" value="0" onchange="sum()"></td>

-----------------------------------------------
This is assuming your code for customer product page is still like the one in your previous post.
Regards,
John
0
 
LVL 9

Author Comment

by:djpazza
ID: 14014913
thanks John Thats exactly what I wanted just made a few changes onchange to onkeyup and disabled the total box :)

I have noticed though that when adding some of the fields the total box display's e.g

313.5300

How can I format the total box to 2 decimal places?

:)
0
 
LVL 11

Expert Comment

by:JohnModig
ID: 14015330
>>How can I format the total box to 2 decimal places?

In the javascript, change this line:
---------------------------------------
  document.form1.total.value = result;

...to this:
---------------------------------------
  document.form1.total.value = Math.round(result*100)/100  
0
 
LVL 9

Author Comment

by:djpazza
ID: 14023291
Great! Just what I was after ! Many thanks
0
 
LVL 11

Accepted Solution

by:
JohnModig earned 375 total points
ID: 14024759
No problem. Glad to help out :)

John
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Australian government abolished Visa 457 earlier this April and this article describes how this decision might affect Australian IT scene and IT experts.
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month16 days, 7 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question