Barney Evans
|
| Posted: 11/04/2004, 1:57 PM |
|
I have a a order system I am building in ASP. I have an order Table and a Parts Table in Access. I want to have a hidden field on the parts form that looks for and adds 1 to the highest line item number for that order.
Example
Order 6 (Value from the Orders Table)
Line item 1
Line Item 2
etc.
I hope I explaned this well enough!
Barney
|
|
|
 |
DonB
|
| Posted: 11/04/2004, 9:41 PM |
|
Why?
I ask because if this is because you need to store unique items the best
thing to do is to set the database column to "autonumber".
If you want each item sequentially number from 1 to 'n' (and to handle
insertions and deletions such that there are no gaps) then the best way
might be to implement the parts form as an Editable Grid, and put Before
Show row code to fill in a column with the numbers.
Check this to see if it sounds like what you are after
http://www.gotodon.com/ccbth/Features/Kbase.asp?find=index
and there are a couple tutorials in the download section as well that might
get you going.
--
DonB
http://www.gotodon.com/ccbth
<BarneyEvans@forum.codecharge (Barney Evans)> wrote in message
news:6418aa5ad75c8b@news.codecharge.com...
> I have a a order system I am building in ASP. I have an order Table and a
Parts
> Table in Access. I want to have a hidden field on the parts form that
looks for
> and adds 1 to the highest line item number for that order.
>
> Example
> Order 6 (Value from the Orders Table)
> Line item 1
> Line Item 2
> etc.
>
> I hope I explaned this well enough!
>
> Barney
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
BarneyE
|
| Posted: 11/10/2004, 8:58 AM |
|
Don,
The suggested links are close but I need one more piece of the puzzle.
I cannot autonumber the column, because the number of line items is unique to each order. One order may have 2 line items while another order will have 15 items. Each order needs to start with Line Item 1.
I set up a "Before Show" event for a hidden field (Lineitem) which the user does not need to see. Now I need code to query the table for an Orderid and find the highest numbered lineitem and add one to that value. The orderid and Customerid are passed via the URL http://localhost/CTSample/parts_maint.asp?orderid=6&customerid=4.
The Part table has the following columns:
Orderid (refers to the order table)
Customerid (refers to the customer table)
lineitem
Part
Quote
I am very new at this. I am still learning this program, any help is greatly appreciated.
Thanks!
|
|
|
|
smalloy
Posts: 107
|
| Posted: 11/10/2004, 2:17 PM |
|
Try adding this into the AfterInsert of the grid:
'Custom Code
Dim LastID
'Get the last inserted Key
LastID = CCDLookUp(max(lineitem), "Customerid","", DByour_database)
'Append the last inserted key to the URL by modifying the 'Redirect' variable
redirect = FileName &"?"&CCAddParam(CCRemoveParam(request.QueryString,"ccsForm"), "lineitem", LastID)
'End Custom Code
If you get an error for the database make sure you have an instance of the database open.
I hope this helps you!
_________________
Anything can be done, just give me time and money. |
 |
|
smalloy
Posts: 107
|
| Posted: 11/10/2004, 2:21 PM |
|
Need to correct the code, Just replace this line or you will get an error on 'MAX'
LastID = CCDLookUp("max(lineitem)", "Customerid","", DByour_database)
_________________
Anything can be done, just give me time and money. |
|
|
|
BarneyE
|
| Posted: 11/11/2004, 11:31 AM |
|
Thanks to smalloy!
Using his code I came up with this:
parts.orderitem.value = CCDLookUp("max(orderitem)", "Parts", "Orderid", DBconnection1) +1
It works, finding the highest value in the Orderitem column. But I need to filter by Orderid.
Orderid is a variable passed by the URL.
Example:
http://localhost/CTSample/parts_maint.asp?orderid=10&customerid=4
Is there additional code to make the filter work?
|
|
|
|
Don Safar
|
| Posted: 11/11/2004, 12:15 PM |
|
Dim OrderID
OrderID = CCGetParam("orderid",0); //will default to 0(zero) if no param
If OrderID <> 0 then
parts.orderitem.value = CCDLookUp("max(orderitem)", "Parts",
"Orderid="&OrderID, DBconnection1) +1
else
//whatever error logic you want goes here
end if
double check my asp syntax, but this should filter by orderid
"BarneyE" <BarneyE@forum.codecharge> wrote in message
news:64193be0a9cd74@news.codecharge.com...
> Thanks to smalloy!
>
> Using his code I came up with this:
>
> parts.orderitem.value = CCDLookUp("max(orderitem)", "Parts", "Orderid",
> DBconnection1) +1
>
> It works, finding the highest value in the Orderitem column. But I need to
> filter by Orderid.
> Orderid is a variable passed by the URL.
> Example:
> http://localhost/CTSample/parts_maint.asp?orderid=10&customerid=4
>
> Is there additional code to make the filter work?
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
BarneyE
|
| Posted: 11/11/2004, 2:31 PM |
|
Thanks Don!
That worked great!
|
|
|
|
|
