hi there, i have 3 tables, tbl_stock, tbl_stock_location and tbl_del_location. the user will enter a stock id and qty onto the aspx page, the code then needs to select the record from tbl_stock_location - thats not a problem. what i am having real trouble
with is this.
in tbl_stock_location the fields are like this ID stock_id location1 qty1 location2 qty2 location3 qty3
======================================================
001 145 12 1 11 3 19 5
what it needs to do is to
<div mce_keep="true">take the qty entered by the user </div>
<div mce_keep="true">loop through tbl_location and add the qtys and location's to tbl_del_location each time subtracting the number in the qty field until qty_entered = 0</div>
<div mce_keep="true">then it needs to do the same again, but just update tbl_location (which would be deleteing the location e.g. making it = 0 when the qty was 0) </div>
there is more to do on this, but this part of it is the core and i am really struggling to do this, any help would be great. I am using .Net with C# and have never used BLL or DAL, i just want something simple in the code behiend file to do this, once its
up and running then i will seperate it.
Thanks in advance
Jez
Jeremy Huson
Senior Network Engineer
Carden Computers - PC Repair Brighton
hi there, yeah i know its getting on my nerves! well tbl_stock dosnt really come into it, its just there to fill some fields nothing actually important.
tbl_del_location will look like this
order_id will be got from the DB as the stock items will be inserted into tbl_order before this
ID order_id stock_ID qty1 location1 qty2 location2 ...... qty5 location5
I really do want to thank you for your help. I live in brighton, if i get really stuck i might turn up at your door!
Jeremy Huson
Senior Network Engineer
Carden Computers - PC Repair Brighton
in tbl_stock_location the fields are like this ID stock_id location1 qty1 location2 qty2 location3 qty3
======================================================
001 145 12 1 11 3 19 5
Are you stuck with the current design of the tbl_stock_location table? It violates good relational design principles.
Usually, you should design the table like this:
ID stock_id location qty
========================
001 145 12 1
002 145 11 3
003 145 19 5
This makes looping easy. You can have as many locations as you need and never have to worry about those that aren't being used.
If this answered your question, be sure to mark it as the answer. That way, everybody after you will know it's the answer also!
you can do all your processing over the data in the stored proc in the backend at the database level. you can actually use cursor to loop through each of your records and where you can work with any operation you want on each of the record.
can the SP loop through till it reaches 0 copying the values into a different table, then loop through again and delete the values? im not great at SQL and woudl prefere it to be done in c#, but if you have an example i could use then that would be great
Jeremy Huson
Senior Network Engineer
Carden Computers - PC Repair Brighton
hi there, i know how to read data with a DataReader and to write data using objcmd, i just cant figure out the loop and where / how to put in my read write bit - i am gussing for the first read bit just do select * from tbl_location where stock_id = txtStockID.Text;
Jeremy Huson
Senior Network Engineer
Carden Computers - PC Repair Brighton
jezh
Member
285 Points
510 Posts
reading from one table, writing to another in loop
Nov 10, 2007 05:35 PM|LINK
Senior Network Engineer
Carden Computers - PC Repair Brighton
scott@elband...
Star
11346 Points
1865 Posts
Re: reading from one table, writing to another in loop
Nov 10, 2007 06:44 PM|LINK
Hi Jez can you post the table definition for:
Lets get this sort once and for all! [:)]
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 10, 2007 07:20 PM|LINK
hi there, yeah i know its getting on my nerves! well tbl_stock dosnt really come into it, its just there to fill some fields nothing actually important.
tbl_del_location will look like this
order_id will be got from the DB as the stock items will be inserted into tbl_order before this
ID order_id stock_ID qty1 location1 qty2 location2 ...... qty5 location5
I really do want to thank you for your help. I live in brighton, if i get really stuck i might turn up at your door!
Senior Network Engineer
Carden Computers - PC Repair Brighton
david wendel...
All-Star
15865 Points
2243 Posts
Re: reading from one table, writing to another in loop
Nov 10, 2007 09:25 PM|LINK
Are you stuck with the current design of the tbl_stock_location table? It violates good relational design principles.
Usually, you should design the table like this:
ID stock_id location qty
========================
001 145 12 1
002 145 11 3
003 145 19 5
This makes looping easy. You can have as many locations as you need and never have to worry about those that aren't being used.
chalamarc
Member
88 Points
24 Posts
Re: reading from one table, writing to another in loop
Nov 10, 2007 09:53 PM|LINK
you can do all your processing over the data in the stored proc in the backend at the database level. you can actually use cursor to loop through each of your records and where you can work with any operation you want on each of the record.
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 11, 2007 01:56 AM|LINK
hi, yes i know its not correct, however this was the best solution for the customer, i only have to add in an extra loop and it will be fine
Senior Network Engineer
Carden Computers - PC Repair Brighton
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 11, 2007 01:57 AM|LINK
can the SP loop through till it reaches 0 copying the values into a different table, then loop through again and delete the values? im not great at SQL and woudl prefere it to be done in c#, but if you have an example i could use then that would be great
Senior Network Engineer
Carden Computers - PC Repair Brighton
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 11, 2007 02:00 AM|LINK
all i really want is just a c# example of my problem at the top so i can add it to my code, tweak it and get it to work
Senior Network Engineer
Carden Computers - PC Repair Brighton
david wendel...
All-Star
15865 Points
2243 Posts
Re: reading from one table, writing to another in loop
Nov 11, 2007 02:22 AM|LINK
Do you know how to query data and get it into a DataTable object? And how to issue an Update command to the database?
If so, the loop/processing logic should be straightforward from there.
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 11, 2007 03:18 PM|LINK
hi there, i know how to read data with a DataReader and to write data using objcmd, i just cant figure out the loop and where / how to put in my read write bit - i am gussing for the first read bit just do select * from tbl_location where stock_id = txtStockID.Text;
Senior Network Engineer
Carden Computers - PC Repair Brighton