calculate on dynamic data 2

[kiwieur]

I posted this question in the javascript forum

I have a web page that displays data in a table from a databse however the number of rows can change based on the criria selected.

I have a column that shows the order qty for a particular specification however there are 2 types of order

1. make product for stock
2. call off order from stock

If anyone could point me in the right direction it would be much appeciated

such as

spec_number order type order qty
12345 make stock 10000
12345 call off stock 5000

what i would like is to have another column that shows current stock like so

spec_number order type order qty current
12345 make stock 10000 10000
12345 call off stock 4000 6000
12345 make stock 5000 11000

is it possible to recreate this so that it looks at the current stock on the line above and depending on the stock type either adds or deletes the order qty to the relevant row field.

**********************************************************

and it was suggested by Dan that

"This is very possible, however, given you've got server-side scripting to read from a database, I'd suggest doing this server-side rather than client-side."

So I was wondering whether anyone could point me in the right direction please

This is my access SQL

SELECT tblTopUpCallOffOrders.spec_number, tblTopUpCallOffOrders.date_ordered, tblTopUpCallOffOrders.due_date, tblTopUpCallOffOrders.Job_number, tblTopUpCallOffOrders.QtyOrdered, tblTopUpCallOffOrders.OrderType, 0 AS CurrentStock
FROM tblTopUpCallOffOrders
WHERE (((tblTopUpCallOffOrders.spec_number)="229895"))
ORDER BY tblTopUpCallOffOrders.due_date;



Regards

Paul

 

[kiwieur]

People,

I fully understand that the purpose of these forums is not to actually create the code solution for the posters and I have had excellent help in the past.

However in this case I am not actually sure where I should start to do what I need therefore if someone could actually give me a starting point I would be most grateful.

I also understand that perhaps my initial post was not very clear and so I will try to explain again what I am trying to achieve.

I have a recordset that pulls data from an access database based on certain criteria and therefore the number of rows displayed can vary from 1 to many.

2 of the fields in the recordset display the following data

Order Type
Order Qty

and an example would be

Row No. Order Type Order Qty
1 Top Up 10000
2 Call Off 5000
3 Call Off 3000

I also have a field from another recordset that displays the "Total Stock" value from the database

Now what I would like to do is have another field next to order qty named "Current Stock" and depending on the order type either add or subtracts values. This value would be calculated as follows using the example above


Order Type Order Qty Current Stock
Top Up 10000 =TotalStock + Row1 order qty
Call Off 5000 = row1 Currentstock - order qty
Call Off 3000 = row2 Currentstock - order qty

and so on

so the above example figures would be as shown below assuming that the "Total Stock Qty" is 12000


Order Type Order Qty Current Stock
Top Up 10000 22000
Call Off 5000 17000
Call Off 3000 14000

If anyone can help me at all I would appreciate it very much




Regards

Paul

 

[tsuji]

[1] If you mean only to get the set of data out as required, you can sure do the weighted sum "current stock" calculated via the recordset retrieved containing only the prime data ("order type", "order qty") etc. That would be the easiest.

[2] If you want to get the complex sql to make the weighted sum in one-go, it would be very much involved in the construction of the sql. The difficulty is more as it is not clear whether the table of orders involved has some primary key (unique) to facilate the weighted sum.

[3] Based on the approach [1], this is the straightforward implementation.

[3.1] Suppose the tables are (a) "orders" (maybe yours "tblTopUpCallOffOrders"), containing spec_number, order_type, order_qty, etc, (simplified in your 2nd post); and (b) "stock", containing spec_number and total_stock. Suppose the connection object be conn opened. The process would be something like this.
[tt]
spec_number="12345" 'supposed a string, if it is a number, change accordingly
ssql="select orders.spec_number as spec_number, " & _
"orders.order_qty as order_qty, " & _
"orders.order_type as order_type, " & _
"stock.total_stock as total_stock " & _
"from orders, stock " & _
"where orders.spec_number=stock.spec_number and " & _
"order.spec_number=""" & spec_number & """"
set rs=conn.execute(ssql)
if not rs.eof then
rs.movefirst
init_stock=rs("total_stock").value
current_stock=init_stock
do while not rs.eof
'spec_number is available already
order_type=rs("order_type").value
order_qty=rs("order_qty").value
select case lcase(order_type)
case "call off stock"
current_stock=current_stock-order_qty
case "make stock"
current_stock=current_stock+order_qty
case else
current_stock=current_stock 'decide what to do
end select
rs.movenext
[blue]'now you have all the quantities avail, do the response here...[/blue]
loop
else
'decide what to do
end if
rs.close
set rs=nothing
[/tt]

 

[kiwieur]

tsuji,

thank you for your response, explanation and example code.

I am not in a position to test this out at the moment but as soon as i can i will try it and let you know the outcome

Regards

Paul

 

[kiwieur]

Hi tsuji,

I have only just got back to this issue which you gave me some code for in november, I have tried to incororate what you gave me into my code however I am struggling a little.

I use Dreamweaver to create my web pages and I knoew that most of the guru's do not like it but that is what I am confident in using.

I am pasting my code and would be grateful if you could have a look over it and perhaps tell me where i am going wrong

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../Connections/StockReplen.asp" -->
<%
Dim rsRunSum__MMColParam
rsRunSum__MMColParam = "311314"
If (Request.QueryString("spec_number") <> "") Then 
  rsRunSum__MMColParam = Request.QueryString("spec_number")
End If
%>
<%
Dim rsRunSum
Dim rsRunSum_cmd
Dim rsRunSum_numRows

Set rsRunSum_cmd = Server.CreateObject ("ADODB.Command")
rsRunSum_cmd.ActiveConnection = MM_StockReplen_STRING
rsRunSum_cmd.CommandText = "SELECT spec_number, due_date, Job_number, Qty, InitStock, OrderType FROM tblSR_FutureOrders WHERE spec_number = ?" 
rsRunSum_cmd.Prepared = true
rsRunSum_cmd.Parameters.Append rsRunSum_cmd.CreateParameter("param1", 200, 1, 255, rsRunSum__MMColParam) ' adVarChar

Set rsRunSum = rsRunSum_cmd.Execute
rsRunSum_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="../Styles/RigidTextStyles.css" rel="stylesheet" type="text/css" />
</head>

<body>
<table width="60%" border="0" align="center" class="tblbodaOutset">
  <tr class="tblHeaderCaps">
    <td rowspan="2">spec no</td>
    <td rowspan="2">due date</td>
    <td rowspan="2">order no</td>
    <td rowspan="2">order type</td>
    <td rowspan="2">order qty</td>
    <td>current stock</td>
  </tr>
  <tr class="tblHeaderCaps">
    <td><%=(rsRunSum.Fields.Item("InitStock").Value)%></td>
  </tr>
  <%
Dim CStock
Dim OStock
Dim Stock
Dim SType

CStock = rsRunSum.Fields.Item("InitStock").Value
OStock = rsRunSum.Fields.Item("Qty").Value
%>
  <tr class="TextNoBorderLeftPlain10px">
    <%
If not rsRunSum.eof Then
rsRunSum.movefirst
CStock = rsRunSum.Fields.Item("InitStock").Value
CurrentStock = CStock
do while not rsRunSum.eof
OrderType = rsRunSum("OrderType").value
OrderQty = rsRunSum("Qty").value
select case lcase(OrderType)
case "calloff"
CurrentStock = CurrentStock-OrderQty
case "topup"
CurrentStock = CurrentStock+OrderQty
case else CurrentStock = CurrentStock
end select
rsRunSum.movenext
loop
end if
%>
    <td><%=(rsRunSum.Fields.Item("spec_number").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("due_date").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("Job_number").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("OrderType").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("Qty").Value)%></td>
    <td><%=CurrentStock%></td>
  </tr>
</table>
</body>
</html>
<%
rsRunSum.Close()
Set rsRunSum = Nothing
%>

I am getting the following error message when I try to view my page

Microsoft OLE DB Provider for ODBC Drivers error '80040e18' 
Rowset position cannot be restarted. 

/CustService/StockRunSum.asp, line 57

Regards

Paul

 

[DotNetGnat]

try adding:

rsRunSum_cmd.Cursortype = adOpenStatic

I am trying to remember what else you might need...but give this a shot...

-DNG

 

[kiwieur]

DNG,

Thanks for the response, could you explain at what point in the code I would need to add the code you suggest please

Regards

Paul

 

[DotNetGnat]

....
....
Set rsRunSum_cmd = Server.CreateObject ("ADODB.Command")
rsRunSum_cmd.ActiveConnection = MM_StockReplen_STRING
rsRunSum_cmd.Cursortype = adOpenStatic
....
....

-DNG

 

[kiwieur]

Hi DNG,

Thanks for the reply I have tried adding the code as you suggested but as soon as I do the recordset I created via dreamweaver dissapears and the page will not load at all.

It appears that I may not be able to do this using dreamweaver to code the page. I am not sure where to go next now on this one.

however I do appreciate you trying to help me.

Regards

Paul

 

[kiwieur]

Hi Guys,

OK I have been trying to solve this today and I can now get my page to load however it only loads with the header rows and no data. I have removed one row of code and added another 2 rows of code to try and replicate something similar to what DNG sent me yesterday

Here is my new code with the new rows shown in red

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../Connections/StockReplen.asp" -->
<%
Dim rsRunSum__MMColParam
rsRunSum__MMColParam = "311314"
If (Request.QueryString("SpecNo")  <> "") Then 
  rsRunSum__MMColParam = Request.QueryString("SpecNo") 
End If
%>
<%
Dim rsRunSum
Dim rsRunSum_cmd
Dim rsRunSum_numRows

Set rsRunSum_cmd = Server.CreateObject ("ADODB.Command")
rsRunSum_cmd.ActiveConnection = MM_StockReplen_STRING
rsRunSum_cmd.CommandText = "SELECT spec_number, due_date, Job_number, Qty, InitStock, OrderType FROM tblSR_FutureOrders WHERE spec_number = ?" 
rsRunSum_cmd.Prepared = true
rsRunSum_cmd.Parameters.Append rsRunSum_cmd.CreateParameter("param1", 200, 1, 255, rsRunSum__MMColParam) ' adVarChar

[COLOR=red][b]Set rsRunSum = Server.CreateObject("ADODB.Recordset")
rsRunSum.Open rsRunSum_cmd, , 3[/b][/color]
rsRunSum_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsRunSum_numRows = rsRunSum_numRows + Repeat1__numRows
%>
<%
Dim CStock
If not rsRunSum.EOF Then
rsRunSum.MoveFirst()
CStock = rsRunSum.Fields.Item("InitStock").Value
do while not rsRunSum.EOF
OrderType = rsRunSum("OrderType").value
OrderQty = rsRunSum("Qty").value
select case (OrderType)
case "CallOff"
CStock = CStock-OrderQty
case "TopUp"
CStock = CStock+OrderQty
case else CStock = CStock
end select
rsRunSum.MoveNext()
loop
end if
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="../Styles/RigidTextStyles.css" rel="stylesheet" type="text/css" />
</head>

<body>
<table width="60%" border="0" align="center" class="tblbodaOutset">
  <tr class="tblHeaderCaps">
    <td rowspan="2">spec no</td>
    <td rowspan="2">due date</td>
    <td rowspan="2">order no</td>
    <td rowspan="2">order type</td>
    <td rowspan="2">order qty</td>
    <td>current stock</td>
  </tr>
  <tr class="tblHeaderCaps">
    <td>&nbsp;</td>
  </tr>
  <% 
While ((Repeat1__numRows <> 0) AND (NOT rsRunSum.EOF)) 
%>
  <tr class="TextNoBorderLeftPlain10px">
    <td><%=(rsRunSum.Fields.Item("spec_number").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("due_date").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("Job_number").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("OrderType").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("Qty").Value)%></td>
    <td><%=Response.Write(CStock)%></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsRunSum.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
rsRunSum.Close()
Set rsRunSum = Nothing
%>

the code i removed from where i have entered the new code was as follows

Set rsRunSum = rsRunSum_cmd.Execute

this is what I get if I view the source code

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="../Styles/RigidTextStyles.css" rel="stylesheet" type="text/css" />
</head>

<body>
<table width="60%" border="0" align="center" class="tblbodaOutset">
  <tr class="tblHeaderCaps">
    <td rowspan="2">spec no</td>
    <td rowspan="2">due date</td>
    <td rowspan="2">order no</td>
    <td rowspan="2">order type</td>
    <td rowspan="2">order qty</td>
    <td>current stock</td>
  </tr>
  <tr class="tblHeaderCaps">
    <td>&nbsp;</td>
  </tr>
  
</table>
</body>
</html>

obviously I have something wrong or missing but cannot tell what that may be. Any help would be appreciated

Regards

Paul

 

[kiwieur]

Hey Guys,

I am getting there slowly, I now have the page displaying with data albeit incorrectly.

This is what is showing at the moment

date          spec no      order no     order type    order qty     rem qty     init stock = 2550
14/04/2009    311314       C43920       CallOff       1200           6018 
16/04/2009    311314       C42185       CallOff       300            6018 
23/04/2009    311314       311314 L0    TopUp         2484           6018 
30/04/2009    311314       311314 M0    TopUp         2484           6018

Therfore what seems to be happening is that it is taking the init stock of 2550 then adding or taking away the order values based on the case statement and giving me the same total at the end of each row what I expected to see was the following

date          spec no      order no     order type    order qty     rem qty     init stock = 2550
14/04/2009    311314       C43920       CallOff       1200           1350 
16/04/2009    311314       C42185       CallOff       300            1050 
23/04/2009    311314       311314 L0    TopUp         2484           3534 
30/04/2009    311314       311314 M0    TopUp         2484           6018

Below is the code as I have it now, can someone explain what I need to do to be able to achieve what I need please

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../Connections/StockReplen.asp" -->
<%
Dim rsRunSum__MMColParam
rsRunSum__MMColParam = "311314"
If (Request.QueryString("spec_number") <> "") Then 
  rsRunSum__MMColParam = Request.QueryString("spec_number")
End If
%>
<%
Dim rsRunSum
Dim rsRunSum_cmd
Dim rsRunSum_numRows

Set rsRunSum_cmd = Server.CreateObject ("ADODB.Command")
rsRunSum_cmd.ActiveConnection = MM_StockReplen_STRING
rsRunSum_cmd.CommandText = "SELECT * FROM tblSR_FutureOrders WHERE spec_number = ?" 
rsRunSum_cmd.Prepared = true
rsRunSum_cmd.Parameters.Append rsRunSum_cmd.CreateParameter("param1", 200, 1, 255, rsRunSum__MMColParam) ' adVarChar

Set rsRunSum = Server.CreateObject("ADODB.Recordset")
rsRunSum.Open rsRunSum_cmd, , 3
rsRunSum_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsRunSum_numRows = rsRunSum_numRows + Repeat1__numRows
%>
<%
Dim CStock
If not rsRunSum.EOF Then
CStock = rsRunSum.Fields.Item("InitStock").Value
do while not rsRunSum.EOF
OrderType = rsRunSum("OrderType").value
OrderQty = rsRunSum("Qty").value
select case (OrderType)
case "CallOff"
CStock = CStock-OrderQty
case "TopUp"
CStock = CStock+OrderQty
case else CStock = CStock
end select
rsRunSum.MoveNext()
loop
rsRunSum.MoveFirst()
end if
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="../Styles/RigidTextStyles.css" rel="stylesheet" type="text/css" />
</head>

<body>
<table width="60%" border="0" align="center" class="tblbodaSolid">
  <tr class="tblHeaderCaps10px">
    <td rowspan="2">date</td>
    <td rowspan="2">spec no</td>
    <td rowspan="2">order no</td>
    <td rowspan="2">order type</td>
    <td rowspan="2">order qty</td>
    <td>init stock</td>
  </tr>
  <tr class="tblHeaderCaps10px">
    <td><%=(rsRunSum.Fields.Item("InitStock").Value)%></td>
  </tr>
  <% 
While ((Repeat1__numRows <> 0) AND (NOT rsRunSum.EOF)) 
%>
  <%If (Repeat1__numRows Mod 2) Then%>
<tr bgcolor="#FFFF99" class="TextCentrePlain10px">		
<%else%>
<tr bgcolor="#CCFFFF" class="TextCentrePlain10px">
<%End If%>
    <td><%=(rsRunSum.Fields.Item("due_date").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("spec_number").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("Job_number").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("OrderType").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("Qty").Value)%></td>
    <td><%=CStock%></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsRunSum.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
rsRunSum.Close()
Set rsRunSum = Nothing
%>

Thanking You in Anticipation

Regards

Paul

 

[DotNetGnat]

You are calculating the CStock value (correctly) but displaying it much later...at the time of displaying you will have the final CStock value and it will be shown the same for all the rows...

you need to merge the both the display loop and calculation loop...

-DNG

 

[kiwieur]

Hi DNG,

I have changed the position of the calcyulation code however I now recieve the following error message when trying to run the page

Response object error 'ASP 0251 : 80004005' 

Response Buffer Limit Exceeded 

/CustService/StockRunSum6.asp, line 0 

Execution of the ASP page caused the Response Buffer to exceed its configured limit.

Do you have any idea why this may be happening

Regards

Paul

 

[kiwieur]

OK, I have tried what DNG said and now have the running sum working but have another problem in so much as only the first row shows my other data after that only the running sum values show as seen below

date          spec no      order no     order type    order qty     rem qty     init stock = 2550
14/04/2009    311314       C43920       CallOff       1200           1350
1050
3534
6018

my code is posted below can anyone see what my problem is now please

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../Connections/StockReplen.asp" -->
<%
Dim rsRunSum__mSpec
rsRunSum__mSpec = "311314"
If (Request.QueryString("SpecNo") <> "") Then 
  rsRunSum__mSpec = Request.QueryString("SpecNo")
End If
%>
<%
Dim rsRunSum
Dim rsRunSum_cmd
Dim rsRunSum_numRows

Set rsRunSum_cmd = Server.CreateObject ("ADODB.Command")
rsRunSum_cmd.ActiveConnection = MM_StockReplen_STRING
rsRunSum_cmd.CommandText = "SELECT tblSR_FutureOrders.SpecNo, DDate, OrderNo, Qty, OrderType, WHStock FROM tblSR_FutureOrders, tblSR_WHStock WHERE tblSR_FutureOrders.SpecNo = tblSR_WHStock.SpecNo AND  tblSR_FutureOrders.SpecNo = ? ORDER BY DDate ASC" 
rsRunSum_cmd.Prepared = true
rsRunSum_cmd.Parameters.Append rsRunSum_cmd.CreateParameter("param1", 200, 1, 255, rsRunSum__mSpec) ' adVarChar

Set rsRunSum = rsRunSum_cmd.Execute
rsRunSum_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsRunSum_numRows = rsRunSum_numRows + Repeat1__numRows
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="../Styles/RigidTextStyles.css" rel="stylesheet" type="text/css" />
</head>

<body>
<table width="60%" border="0" align="center" class="tblbodaSolid">
  <tr class="tblHeaderCaps10px">
    <td rowspan="2">date</td>
    <td rowspan="2">spec no</td>
    <td rowspan="2">order no</td>
    <td rowspan="2">order type</td>
    <td rowspan="2">order qty</td>
    <td>init stock</td>
  </tr>
  <tr class="tblHeaderCaps10px">
    <td></td>
  </tr>
  <% 
While ((Repeat1__numRows <> 0) AND (NOT rsRunSum.EOF)) 
%>
  <%If (Repeat1__numRows Mod 2) Then%>
<tr bgcolor="#FFFF99" class="TextCentrePlain10px">		
<%else%>
<tr bgcolor="#CCFFFF" class="TextCentrePlain10px">
<%End If%>
    <td><%=(rsRunSum.Fields.Item("DDate").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("SpecNo").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("OrderNo").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("OrderType").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("Qty").Value)%></td>
   <%
Dim CStock
If not rsRunSum.EOF Then
CStock = rsRunSum.Fields.Item("WHStock").Value
do while not rsRunSum.EOF
OrderType = rsRunSum("OrderType").value
OrderQty = rsRunSum("Qty").value
select case (OrderType)
case "CallOff"
CStock = CStock-OrderQty
case "TopUp"
CStock = CStock+OrderQty
case else CStock = CStock
end select
%>
<td><%=(CStock)%></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsRunSum.MoveNext()
loop
end if
Wend
%>
</table>
</body>
</html>
<%

rsRunSum.Close()
Set rsRunSum = Nothing
%>

Regards

Paul

 

[DotNetGnat]

why are you having another do while loop inside:

.....
Dim CStock
If not rsRunSum.EOF Then
CStock = rsRunSum.Fields.Item("WHStock").Value
do while not rsRunSum.EOF
.......

you do not need it...

-DNG

 

[kiwieur]

Hi DNG,

I really appreciate your help in prompting me in the right direction.

I have done as you suggested and removed the extra "do while" however I then recieved an error message

Microsoft VBScript compilation error '800a040e' 

'loop' without 'do'
 

/CustService/StockRunSum7.asp, line 86 

loop

so I then removed the "loop" and now I do not get the running sum but the following data

date          spec no      order no     order type    order qty     rem qty     init stock = 2550
14/04/2009    311314       C43920       CallOff       1200           1350 
16/04/2009    311314       C42185       CallOff       300            2250 
23/04/2009    311314       311314 L0    TopUp         2484           5034 
30/04/2009    311314       311314 M0    TopUp         2484           5034

Therefore what is happening now it is just adding or subtracting each line from the init stock, I can see that I am nearly there however I am struggling to work out exactly what code needs to go where.

Any other pointers you can give me would be really appreciated.

Once again Thanks for your persistent in helping me


Regards

Paul

 

[DotNetGnat]

good...looks like you are closer...try this

...
<% 
[red]Dim CStock[/red]
While ((Repeat1__numRows <> 0) AND (NOT rsRunSum.EOF)) 
%>
  <%If (Repeat1__numRows Mod 2) Then%>
<tr bgcolor="#FFFF99" class="TextCentrePlain10px">        
<%else%>
<tr bgcolor="#CCFFFF" class="TextCentrePlain10px">
<%End If%>
    <td><%=(rsRunSum.Fields.Item("DDate").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("SpecNo").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("OrderNo").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("OrderType").Value)%></td>
    <td><%=(rsRunSum.Fields.Item("Qty").Value)%></td>
   <%
If not rsRunSum.EOF Then
[red]If CStock&"" <> "" Then
CStock = rsRunSum.Fields.Item("WHStock").Value
End If
[/red]
OrderType = rsRunSum("OrderType").value
OrderQty = rsRunSum("Qty").value
select case (OrderType)
case "CallOff"
CStock = CStock-OrderQty
case "TopUp"
CStock = CStock+OrderQty
case else CStock = CStock
end select
%>
<td><%=(CStock)%></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsRunSum.MoveNext()
loop
end if
Wend
%>
...
...

-DNG

 

[kiwieur]

Hi DNG,

tried your modified code however I now get the HTTP 500 Error

And the page will not display at all.

Sorry to be a pain



Regards

Paul

 

[DotNetGnat]

I haven't checked your complete code...but at a first glance it looked like you are fetching initial stock value everytime you loop through your recordset...thats the reason I had put a new if condition...

can you tell us the exact error message that your receiving...http 500 error does not give me much information to suggest further...

-DNG

 

[kiwieur]

Hi DNG,

The error relates to the loop code

Microsoft VBScript compilation error '800a040e' 

'loop' without 'do' 

/CustService/StockRunSum7.asp, line 90 

loop

I have tried taking it out and I now get the same result whereby it is just adding or subtracting each line from the init stock

date          spec no      order no     order type    order qty     rem qty     init stock = 2550
14/04/2009    311314       C43920       CallOff       1200           1350 
16/04/2009    311314       C42185       CallOff       300            2250 
23/04/2009    311314       311314 L0    TopUp         2484           5034 
30/04/2009    311314       311314 M0    TopUp         2484           5034

Regards

Paul