SQL update calculation

  • Thread starter Thread starter Omar K
  • Start date Start date
O

Omar K

Hi, I am doing a stock database and I have to update the quantity of a
stock item when the stock item either goes out or comes into the
warehouse. I am using the SQL part of the database wizard to write my
update code.
This is what it looks like:

UPDATE totalQty
SET Qty = ((SELECT Qty FROM StockFlow WHERE StockCode =
‘::StockCode::') + (SELECT Qty FROM totalQty WHERE StockCode =
‘::StockCode::'))
WHERE StockCode = ‘::StockCode::'

----

it is a '+' sign because i am testing the code first, so this would be
for incoming parts only.

I get an error - maybe because the calculation involves the same
column item that needs to be updated. Is this correct or is it
something else I am doing wrong? If what I think is true, what other
way is there correcting it other than creating a duplicate column of
the quantites ?
 
Actually, I am now seeing that my SELECT statement may be wrong. Does
anyone know how to update the quantity using a calculation, where one
of the numbers is drawn from an existing table? - SELECT statement does
not seem to work.

thanks
omar
 
You would need to use an UPDATE instead of SELECT.

Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.
 
Hi, yes, I am using an update statement, but i need something else to
pull data from an existing table to include in a calculation for my
update.
I am passing two variables to this SQL - Qty, and StockCode. It works
when I remove the SELECT statement, but i do need something to pull
'Quantity' from 'totalQty'
any suggestions?

UPDATE totalQty
SET Quantity = ((::Qty::) + (SELECT Quantity FROM totalQty WHERE
StockCode = '::StockCode::'))
WHERE StockCode = '::StockCode::'
 
Run two queries, the first get the info you need in order to do the update in the second or retrieve
the data needed on a prior page, calculate and pass that to the update statement.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================
 
I was afraid of that ...

How do i pass the data from a SELECT query statement to another page,
page C?

Also, I am passing my form data to page B, how do i pass some of the
form field data to page C ?
 
1. To pass the data from a query to another page, either place the value in a hidden form field or
place it in a session

Session("myvalue") = recordset("myvalue")

or

<input type="hidden" name="myvalue" value="<%=recordset("myvalue")%>">


then on the page needing the value, retrieve the value as:

myvalue = Session("myvalue")

or from a hidden form field

myvalue = Request.Form("myvalue")

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================
 
How your SQL is structured depends upon the dcatabase you're using. What
database?

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.
 
hi,

i'm using access as my database. is it not possible to have more than
1 SQL statement on one page, like my combined UPDATE and SELECT
statements?

when i am selecting the database value of the OldQty of the stock item,
i do not know how to pass that to another page for manipulation. I
tired the hidden value statements, but i think that is only used if i
had a form with user input? my retrieval of the OldQty has no user
input except for the 'StockCode'

so, on one page, i use the DRW to make a SQL query and i use the SELECT
statement, then i delete the <<OldQty>> because i do not want to
display it, i just want to use it as a variable.
 
i tried to save my query SELECT result as follows:

<input type="hidden" name="OldQty" value="<%recordset(fp_sQry)%>">


the page cannot display when i click on it

i also tried it with quotation marks around fp_sQry
 
Change
<input type="hidden" name="OldQty" value="<%recordset(fp_sQry)%>">
to
<input type="hidden" name="OldQty" value="<%=fp_sQry("fieldname")%>">
--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================
 
I am getting this error using <input type="hidden" name="OldQty"
value="<%=fp_sQry("fieldname")­%>">

I have chosen my fieldname as Qty which is the same name of the data
value from my SELECT statement.

Error Type:
Microsoft VBScript compilation (0x800A0408)
Invalid character
/example/Form.asp, line 130, column 29
Response.Write(fp_sQry("Qty")­)


Let me tell you everything that is going on here: this is my web
structure:

I have form.htm that asks the user for two things - StockCode and QtyIN
i pass these values to form.asp via POST with action form.asp

form.asp contains a DRR query: SELECT Qty FROM totalQty WHERE StockCode
= '::StockCode::'

i have included the <input type="hidden" name="OldQty"
value="<%=fp_sQry("fieldname")­%>"> somewhere in the HTML code. (to
capture the value as a variable) - but this seems not to work.

on the same form.asp is an inserted web component page called send.asp
-

send.asp has a form with a submit button and settings to send the data
to a database. the advanced (hidden) options include the two variables
StockCode and QtyIN via <%=request.form("QtyIN")%> which i
know works for sure when i tested it separately to send one table to
two different databases.

the confirmation page of send.asp (accessed from form.asp) is another
page, final.asp

final.asp has the database results region with a query for an UPDATE
statement. UPDATE totalQty
SET Qty = (::QtyIN:: + ::OldQty::)
WHERE StockCode = '::StockCode::'

there is then a link on final.asp to return to the homepage.

there are two main problems left in this:

1) how to send the SELECT query 'Qty' (OldQty) to final.asp

2) how to send the form.htm data to final.asp (it is already being
sent to form.asp)

it would help a lot if you can tell me where to input your suggested
code as sometimes frontpage does not allow me to alter the code.

lastly, when i POST some form fields to a certain page - is it only
accessible from that page? so form.htm only goes to form.asp and not
final.asp?

I hope i made this complicated web structure somewhat easy to follow
i know i have a lot of questions, but your help is greatly appreciated

omar k
mechanical engineer doing computer programming.
 
Sorry, I don't used the FP database components, but try the following

Change
<input type="hidden" name="OldQty" value="<%=fp_sQry("fieldname")­%>">
to
<input type="hidden" name="OldQty" value="<%=fp_rs("Qty")­%>">

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

I am getting this error using <input type="hidden" name="OldQty"
value="<%=fp_sQry("fieldname")­%>">

I have chosen my fieldname as Qty which is the same name of the data
value from my SELECT statement.

Error Type:
Microsoft VBScript compilation (0x800A0408)
Invalid character
/example/Form.asp, line 130, column 29
Response.Write(fp_sQry("Qty")­)


Let me tell you everything that is going on here: this is my web
structure:

I have form.htm that asks the user for two things - StockCode and QtyIN
i pass these values to form.asp via POST with action form.asp

form.asp contains a DRR query: SELECT Qty FROM totalQty WHERE StockCode
= '::StockCode::'

i have included the <input type="hidden" name="OldQty"
value="<%=fp_sQry("fieldname")­%>"> somewhere in the HTML code. (to
capture the value as a variable) - but this seems not to work.

on the same form.asp is an inserted web component page called send.asp
-

send.asp has a form with a submit button and settings to send the data
to a database. the advanced (hidden) options include the two variables
StockCode and QtyIN via <%=request.form("QtyIN")%> which i
know works for sure when i tested it separately to send one table to
two different databases.

the confirmation page of send.asp (accessed from form.asp) is another
page, final.asp

final.asp has the database results region with a query for an UPDATE
statement. UPDATE totalQty
SET Qty = (::QtyIN:: + ::OldQty::)
WHERE StockCode = '::StockCode::'

there is then a link on final.asp to return to the homepage.

there are two main problems left in this:

1) how to send the SELECT query 'Qty' (OldQty) to final.asp

2) how to send the form.htm data to final.asp (it is already being
sent to form.asp)

it would help a lot if you can tell me where to input your suggested
code as sometimes frontpage does not allow me to alter the code.

lastly, when i POST some form fields to a certain page - is it only
accessible from that page? so form.htm only goes to form.asp and not
final.asp?

I hope i made this complicated web structure somewhat easy to follow
i know i have a lot of questions, but your help is greatly appreciated

omar k
mechanical engineer doing computer programming.
 
I found a website with seemingly useful code and adapted it:

<%
dim strCost
dim strNumber
dim strQuery
dim rs

strNumber = session("Number")

strCost = session("Cost")

strQuery = "SELECT total FROM math WHERE ID = 23"

set rs = createObject("ADODB.Recordset")

rs.open strQuery, application("Connection_String")

response.write "A previous total was" & rs.fields(0) & ", and the
newest total is" & strTotal &

rs.close

set rs = nothing

%>


I am taking strTotal from another page where:

<%
dim n,c,t
dim strTotal

n = request("Number")

c = request("Cost")

t = c*n

strTotal = t

session("Total") = strTotal

%>

The code is not working on the last page (the code at the beginning of
this post). The error I get is:

Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/example/find.asp, line 45, column 95
response.write "A previous total was" & rs.fields(0) & ", and the
newest total is" & strTotal &

1) What is wrong / missing here?

2) I am also wondering about the rs.fields(0) statement. I think that
is for integers only, but what if i wanted a string? what would the
statement look like then?
 
Back
Top