ASP - SQL problem

M

MyaTiX

Hi Can someone please help!

I am having a problem with a form! I need to be able to
pull information from one table when the page loads and
update another table after I have made changes but the
second time the page loads I want to load the information
from the second table and not from the origional location.


How do I do something like this?


See the code below! I have added a whole load of comments!

Thanks for any help!


..: CODE :.

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
<%'Submits the updated info to an asp update page%>
<script>
function changetax () {
document.forma.task.value = "CT";
document.forma.submit ();
</script>

<%
dim rs, tmprs, conn, sql
dim procid, tableid, symbol
dim task, menu, exrate, currname

set conn = server.createobject("adodb.connection")
conn.open (application("proc"))

procid = request("procid")
task = request("task")

sql = "select * from tblpurchase where procid = '" &
procid & "';"
set rs = conn.execute(sql)

sql = "select * from tblCurrency where CurrencyID = " & rs
("CurrencyID") & ";"
set tmprs = conn.execute(sql)
symbol = tmprs("CurrencySymbol")
exrate = tmprs("ExRate")
TaxAmount = tmprs("TaxAmt")
%>
</head>

<body>
<form name="forma" action="dosupplies.asp" method="post">

<font style="font-size:12pt;"><b>New item</b></font>
<table cellpadding=3 cellspacing=0 border=0>

<tr><td valign="top"><b>Description</b></td>
<td colspan=3><textarea name="description0" cols="50"
rows="2"></textarea></td>

</tr>

<tr>
<td valign="bottom"><b>Qty</b></td>
<td valign="bottom"><b>Unit type</b></td>
<td valign="bottom"><b>Cost</b></td>
<td valign="bottom" align="center"><b>(%)
<br>Discount</b></td>
</tr>

<tr><td><input name="quan0" value="1" size=4></td>
<td><select name="unit0">
<% while not alphars.eof %>
<option value="<%=alphars("unit")%>"><%=alphars
("unit")%>&nbsp;(<%=alphars("unitdesc")%>)
<% alphars.movenext
wend
alphars.movefirst
%></select></td>
<td><input name="unitcost0" value="0" size=10></td>
<td><input name="disc0" value="0" size=5></td>
<td colspan=1>
<input type="button" value="add this item"
onclick="javscript:newitem();">
<% end if %>
</td>
</tr>

</table>

<!--- Current Added items --->

<hr>
<font style="font-size:12pt;"><b>Current line
items</b></font>
<% if not tmprs.eof then %>
<table cellpadding=3 cellspacing=0 border=0>



<% count = 0

while not tmprs.eof
count = count + 1
subtotal = tmprs("Quantity") * tmprs("UnitCost") %>

<tr><td valign="bottom"><b>#</b></td>
<td valign="bottom"><b>Qty</b></td>
<td valign="bottom"><b>Unit type</b></td>
<td valign="bottom"><b>Cost</b></td>
<td valign="bottom" align="center"><b>(%)
<br>Discount</b></td>
<td valign="bottom"
align="center"><b>Total<BR>Discount</b></td>
<td valign="bottom" align="center"><b>Tax</b></td>
<td valign="bottom" align="center"><b>Total</b></td>
</tr>

<tr><td><%=tmprs("itemno")%></td>
<td><input name="quan<%=count%>" value="<%=tmprs
("Quantity")%>" size=4></td>
<td><select name="unit<%=count%>">
<% while not alphars.eof %>
<option value="<%=alphars("unit")%>" <%if
alphars("unit")=tmprs("unit") then response.write
("SELECTED")%>><%=alphars("unit")%>&nbsp;(<%=alphars
("unitdesc")%>)
<% alphars.movenext
wend
alphars.movefirst

'Adds all unit costs together to provide a subtotal

Pdiscount=tmprs("Disc")
Tdiscount=formatnumber((subtotal) / 100 * Pdiscount)
Ttax=((subtotal-Tdiscount)/100) * TaxAmount
totalcost=(subtotal-Tdiscount)+Ttax
%>
</select>
</td>
<td><input name="unitcost<%=count%>" value="<%=tmprs
("UnitCost")%>" size=10></td>
<td><input name="disc<%=count%>" value="<%
=Pdiscount%>" size=5></td>
<td align="center"><%=Tdiscount%></td>
</b></td>
Click to expand...
<td>
&nbsp;<input type="button" value="update"
onclick="javscript:updateitem(<%=count%>,<%=tmprs
("supplyid")%>);">
<input type="button" value="delete"
onclick="javscript:killitem(<%=count%>,<%=tmprs
("supplyid")%>,<%=tmprs("itemno")%>);">
</td>
</tr>

<tr><td colspan=2 valign="top"><b>Description</b></td>
</textarea></td>
Click to expand...
</tr>

<% tmprs.movenext
wend
%>
</table>
<% else %>
<br>Currently there no line items.
<% end if %>

<%
'See below
'Tax Percentage allows the user to change the
'tax percentage to an alternative rather than the
'default taken from the table tblcurrencies, however when
'this is done it shouldn't change the value in the
'tblcurrencies table but rather in the tblpuchase.
'This is why the second time the page loads it sould no
'longer use the tblcurrencies table for it's information
'but rather the tblpurchase table.
%>

<hr>
<font style="font-size:12pt;"><b>Extra costs for this
procurement</b></font>
<table cellpadding=3 cellspacing=0 border=0>
<tr><td
valign="top"><b>Tax&nbsp;Percentage:</b>&nbsp;</td><td
valign="top">
<input name="taxamt" value="<%=TaxAmount%>" size=3>%
</td></tr>
<tr><td
valign="top"><b>Shipping&nbsp;Cost:</b>&nbsp;</td><td
valign="top">
<input name="ShipCost" value="<%=rs("ShipCost")%>"
size=12></td></tr>
<tr><td valign="top"><b>Surcharges:</b></td>&nbsp;<td
valign="top">
<input name="SurCharge" value="<%=rs("SurCharge")%>"
size=12></td></tr>
</tr>
<tr><td></td><td><input type="button" value="update costs
changes" onclick="javascript:changetax();">
</td></tr>

</table>
<% set tmprs = nothing %>
<% set alphars = nothing %>


</body>
</html>
 
R

Ray at

With all that unecessary html that you posted, your post is too long to
read. From what you wrote above the html, it appears that you're looking
for something like this. http://www.aspfaq.com/show.asp?id=2270 If not,
post back with only necessary html or code that you have that isn't working.

Ray at work
 
B

Bob Barrows

MyaTiX said:
Hi Can someone please help!

I am having a problem with a form! I need to be able to
pull information from one table when the page loads and
update another table after I have made changes but the
second time the page loads I want to load the information
from the second table and not from the origional location.
Click to expand...

This sounds ridi^H^H^H^H very convoluted. Why not simply include a column in
your table to specify whether the data has been edited or not? Why store the
same data in two tables? I'm sorry if you answered these questions in the
mass of code you posted, but I simply do not have time to plow through this
thing to find the answers.

Bob Barrows
 
B

Bob Barrows

Please do not multipost MyaTiX. This is definitely a database-related
question
so .asp.db was the perfect group in which to post it. Posting it here as
well did not increase your chances of getting an answer (most of us
subscribe to both groups). On the contrary, if somebody had taken his time
to answer it here, only to find that it was already resolved in the other
group, that person may have been annoyed enough to ignore any future posts
from you, thereby decreasing your chances of getting help in the future.

There are times when you will not be sure which group is most appropriate,
and you will want to post a question to both groups. In that situation, you
should use the cross-posting technique, rather than posting the same message
multiple times. To crosspost, put a semicolon-delimited* list of the
newsgroups to which you wish to post in the To: header of your post and post
it once. It, and any replies to it, will appear in all the newsgroups in
your list. So, if I reply in .asp.db, my reply will also appear here in
..asp.general.

* ... or whatever delimiter is recognized by your news client

Bob Barrows
 
B

Bob Barrows

Bob said:
Please do not multipost MyaTiX. This is definitely a database-related
Click to expand...
Oops - please ignore this. I was mistaken. I think I'll take a break from
the newsgroups toda ... yeah, right! :)

Bob
 
T

TomB

You know I could swear I read this post about a week ago.

1) Stop using request("item") you'll have problems, specify the collection.
2) Be wary of SQL Injection. Bob has a good link to an explanation. I
think it's www.sqlsecurity.com or something like that. But essentially, you
have
procid=request("procid")
"select * from tblPurchase where procid=" & procid

now if I stick on a "?procid=1 or 1=1; delete from tblPurchase" to the end
of the url, I think I'd cause a little trouble.

3) don't use Select *, specify your column names.

4) Why are you using two tables? I'm sure you have your reasons. I've not
read enough of your code to figure out what you are doing, but what I'd do
is put a hidden field in your form, such as <input type=hidden
name="postback" value="true"> then when you set your SQL Select statement,
throw an if in there...such as,

if Request.Form("postback")="true" then
sSQL="SELECT columnA, columnB FROM Table2"
else
sSQL"SELECT columnA, columnB FROM Table1"
end if
 
M

MyaTiX

Hi,

Sorry didn't realise that would happen!

I have a form which is used for purchasing, the tax
amount is pulled from a table in the database called
tblcurrencies which is used as the default tax amount,
however in the for the user has the option of changing
the default tax amount to another value of there choise.

When the user clicks on update the tax amount is updated
in a table called tblpurchase by an update page called
doupdate.asp, this then sends you back to the page where
the user made the changes! However when you are sent back
to the first page the page uses the default tax amount
again. HOW do I stop it from using the default tax amount
after a user has entered an alternate tax amount???

EG:

..: Code :.
Page1.asp
<script>
function changetax () {
document.forma.task.value = "CT";
document.forma.submit ();
</script>
<%
sql = "select * from tblCurrency where CurrencyID = " & rs
("CurrencyID") & ";"
set tmprs = conn.execute(sql)
symbol = tmprs("CurrencySymbol")
exrate = tmprs("ExRate")
TaxAmount = tmprs("taxamt")
%>
<form name="forma" action="doupdate.asp" method="post">
<table cellpadding=3 cellspacing=0 border=0>
<tr><td
valign="top"><b>Tax&nbsp;Percentage:</b>&nbsp;</td><td
valign="top">
<input name="taxamt" value="<%=TaxAmount%>" size=3>%
</td></tr>
</tr>
</table>
<input type="button" value="update costs changes"
onclick="javascript:changetax();">
</form>

doupdate.asp
<%
select case task
case "CT"
taxamt = request("taxamt")
shipcost = request("shipcost")
surcharge = request("surcharge")
sql = "update tblpurchase set taxamt = " & taxamt
& ", " &_
"shipcost = " & shipcost & ", surcharge = " &
surcharge & " " &_
"where procid = '" & procid & "';"
set rs = conn.execute(sql)
if step > 0 then
relocate = "page1.asp"
end if
end select
%>
 
R

Ray at

You'll have to select that value from the tblpurchase, if that's where
you're storing that value. Are you sure that you want to store the value in
the DB before the user has even completed the transaction anyway? How are
you identifying that tax value that you're storing as being associated with
that user? Are you using sessions? If so, you may want to just store that
tax value in a session variable or pass it in the querystring or something
along those lines.

Ray at work
 
B

Bob Barrows

MyaTiX said:
Hi,

Sorry didn't realise that would happen!

I have a form which is used for purchasing, the tax
amount is pulled from a table in the database called
tblcurrencies which is used as the default tax amount,
however in the for the user has the option of changing
the default tax amount to another value of there choise.

When the user clicks on update the tax amount is updated
in a table called tblpurchase by an update page called
doupdate.asp, this then sends you back to the page where
the user made the changes! However when you are sent back
to the first page the page uses the default tax amount
again. HOW do I stop it from using the default tax amount
after a user has entered an alternate tax amount???
Click to expand...

What kiind of database is this? I know the subject says "SQL" but all kinds
of databases use SQL. Type and version, please.

What you need to do in Page1.asp is first check tblPurchase to see if it
has a value before getting the value from tblCurrencies. The exact method of
doing this depends on your type of database.You could simply run a separate
query but that would be inefficient since an extra trip to the database
would be required.

If you show us the table structures and how they are related, we could come
up with a single query to get what you want.

Bob Barrows
 
B

Bob Barrows

MyaTiX said:
It is an SQL Server 7 Database!

I tried using the solution TomB suggested but I can't
seem to get it to work!


if Request.form("postback")="true" then
sql = "select TaxAmt from tblPurchase where procid
= '" & procid & "';"
set Taxrs = conn.execute(sql)
TaxAmount = Taxrs("TaxAmt")
else
sql = "select * from tblCurrency where CurrencyID = " & rs
("CurrencyID") & ";"
set Currs = conn.execute(sql)
TaxAmount = Currs("TaxAmt")
end if

Here is the table structure of the database!

.:tblCurrency
PK currencyid
currencysymbol
exchangerate
taxamt

.:tblPurchase
PK procid
taxamt
currencyid
etc
etc
Click to expand...

OK, good! Here is a query to get what you want (this assumes that the taxamt
column in tblPurchase is configured to allow NULL.):

Select
c.currencysymbol,
c.exchangerate,
COALESCE(p.taxamt, c.taxamt) As [taxamt]
FROM tblCurrency c LEFT JOIN tblPurchase p
ON c.currencyid = p.currencyid
WHERE c.currencyid = ....

I suggest that you encapsulate this in a stored procedure instead of using
dynamic sql

HTH,
Bob Barrows
 
T

TomB

That's a much better explanation.
How about, in the doupdate.asp page, when you update the value in
tblPurchase, set a Session variable to the new tax amount. THen back on
Page1.asp do this...

if Session("TaxAmount")="" then
TaxAmount=tempRS("taxamt")
else
TaxAmount=Session("TaxAmount")
end if
 

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

Can someone tell me if this a real tracker? Or is it one designed to show you a different message at certain times, ie. acting like one? 0
Javascript DOM 1
Can anyone please help? HTML - two tables applying different styles 4
How to have two html audio players on one page? 0
Filter table rows based on multiple checkboxes value 2
Uncaught ReferenceError: item is not defined at HTMLButtonElement.onclick in the: <button onclick="item.inserir()">Inserir dados</button> 1
SendGrid email issue in responsive Gmail 1
Registration form 13

Members online

No members online now.
Total: 41 (members: 1, guests: 40)
Robots: 356

Forum statistics

Threads
473,769
Messages
2,569,582
Members
45,065
Latest member
OrderGreenAcreCBD

Latest Threads

Top