reading from one table, writing to another in loop

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

Re: reading from one table, writing to another in loop

Hi Jez can you post the table definition for:

<div mce_keep="true">tbl_stock</div>
<div mce_keep="true">tbl_del_location</div>

Lets get this sort once and for all! [:)]

Re: reading from one table, writing to another in loop

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!

Re: reading from one table, writing to another in loop

jezh

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.

Re: reading from one table, writing to another in loop

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.

Re: reading from one table, writing to another in loop

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

Re: reading from one table, writing to another in loop

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

Re: reading from one table, writing to another in loop

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

Re: reading from one table, writing to another in loop

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.

Re: reading from one table, writing to another in loop

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;

Re: reading from one table, writing to another in loop

You are going to have to read your location table in a loop, then have a hard-coded set of if statements, one after another, one for each location in the record.

Or, if you had a normalized location table per my suggestion, just a loop. :)

Re: reading from one table, writing to another in loop

well i have written something similar before (loop) that writes values to the db in the way i described but putting a loop inside a loop, so it loop through to the end of the line and then down one line etc. Apart from that though, how will i do this, im still stuck :(

Re: reading from one table, writing to another in loop

Could you not get the code I suggested last time to work?

Re: reading from one table, writing to another in loop

no, sadly i couldn't at all, i tried doing it but got all confused and the real heart of it which was in the BLL i didnt get - if you had just a chunck of code which i could use and adapt thats what i really need, i just dont have the skills to write it :(

Re: reading from one table, writing to another in loop

Ok well Ramseys Kitchen Nightmares isn't on until 9:00 so I will have a look and see if I can come up with a simple to use example. So can you not change the structure of the database - its this that is causing all of the problems.

Re: reading from one table, writing to another in loop

well i suppose i could, but that table is used on a few other pages, the way i thought about it was (as i said in my earlier post) that just to put another loop inside the loop which make it count horizontally so it goes L1-1 L1-2 L1-3 etc then that loop finishes and then onto L2-1 L2-2 L2-3 etc and so on until it reaches L5-5

Re: reading from one table, writing to another in loop

How about this for a start. Its best to get your database design doen correctly to start with otherwise you will always be fighting agains't it. Have you got the option to redesing the database?

Database Normalization Basics
http://databases.about.com/od/specificproducts/a/normalization.htm

http://www.databasejournal.com/sqletc/article.php/1428511

If you did want to redesign you could ask for help on these forums.

If you decide you want to keep your exisitng design then I will give you a hand to get your application working... but I am sure you will hit more problems in the future so don't say we didn't warn you [;)]

Re: reading from one table, writing to another in loop

i did some database normalisation at uni and hated it! haha but i have to use is. The reason for the design like that is that one product can have parts to it. e.g pack 1, pack 2, and those can be in multiple locations, so when the user enters it, they enter say the part title e.g. box 1 then list all the locations and qtys in that location, so it saves them having to list it out many times. I know i could normalise this, but it means more tables and l lot more programming - and with the trouble i have had to get this working so far im not willing to take that chance! as im not that good at c# so you can see my problem

Re: reading from one table, writing to another in loop

[;)]

Right let me find the code I posted for you....

Re: reading from one table, writing to another in loop

Ok how about something like this, I don't know if it will work but it seems to do all you want. Its not perfect but its a start and its all in one page as you wanted :0)

My page, you would have your 11 text boxes on here:

<form id="form1" runat="server">
      <div>
         <br />
         QTY<br />
         <asp:TextBox ID="txtQty" runat="server"></asp:TextBox><br /><br />
         PRODUCT ID<br />
        <asp:TextBox ID="txtProductID" runat="server"></asp:TextBox>
         <br />
         <br />
         <asp:Button ID="btnSubmitOrder" runat="server" Text="Submit Order" />
     </div>
</form>

Code behind page:

Imports System.Collections.Generic
Partial Class _Default
Inherits System.Web.UI.Page

' This is the order button that is clicked by the Staff Member
Protected Sub btnSubmitOrder_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmitOrder.Click

                           ' This will hold all the values entered by the Staff Member
                           Dim myOrder As New List(Of OrderLine)
                           ' Loop around to fill up all of your orderlines (I only have one in this example)
                           myOrder.Add(New OrderLine(CType(Me.txtQty.Text, Integer), txtProductID.Text))

' Call our method to adjust the stock (This would be in Business Layer)
myOrder = adjustStock(myOrder)

' At this point the tbl_stock_location has been updated

                           ' Check to make sure we could fulfill the entire order
                           For Each orln As OrderLine In myOrder
                                        If orln._QtySupplied < orln._Qty Then

                                                   Response.Write(String.Format("We could only supply {0} of productid {1} - we just don't have any more stock<br/><br/>", orln._QtySupplied, orln._ProductID))

                                        End If
                            Next

                            ' Now we can go and update the tbl_del_location
                            For Each orLn As OrderLine In myOrder
                                    For Each stkAmnt As stockLocationAmount In orLn.locationQtys
                                                  ' We can now use these figures to update the other tables.
                                                  ' but here I am just dispalying the information to the screen.
                                                  Response.Write(String.Format("{0} of Product: {1} where taken from Location: {2}, which was from column Location{3} in the table tbl_stock_location", stkAmnt._QtySupplied, stkAmnt._ProductID, stkAmnt._LocationID, stkAmnt._LocationColumn))

                                   Next
                           Next
           End Sub

Public Function adjustStock(ByVal myOrder As List(Of OrderLine)) As List(Of OrderLine)

                  ' You could wrap this all in a System.Transaction so that if anything went
                  ' wrong all chnages to the database would roll back
                  For Each orln As OrderLine In myOrder
                           ' Loop through all of the stock locations
                           For stockloc As Integer = 1 To 5
                                   ' Get the stock level at the current stock location
                                   ' This is another method in our BLL that will get the result from the DAL
                                   Dim stockQty As Integer = getStockForProductAtLocation(orln._ProductID, stockloc)

                                    If Not stockQty = 0 Then

' get the real location of the stock
Dim actualLocation As Integer = getLocationID(orln._ProductID, stockloc)

                                                 ' Check to see if we have enough stock at this location to fulfill
                                                 ' the staff members order
                                                 If stockQty >= (orln._Qty - orln._QtySupplied) Then
                                                               ' We have got enough stock
                                                               ' Call another (again would be in BLL) method to update the stock - this in turn would call the DAL
                                                                adjustStockForProductAtLocation(orln._ProductID, stockQty - (orln._Qty - orln._QtySupplied), stockloc)

' Update the supplied stock in our OrderLine Object
orln._QtySupplied = orln._Qty

' Update the logged on what stock we got and from where
orln.locationQtys.Add(New stockLocationAmount(orln._ProductID, actualLocation, stockQty, stockloc))

                                                                 ' No need to keep looping now
                                                                 Exit For
                                                   Else
                                                                 ' We don't have enough so minus what we can
                                                                 ' Call another method to update the stock - this in turn would call the DAL
                                                                 adjustStockForProductAtLocation(orln._ProductID, stockQty, stockloc)
                                                                 ' Update the supplied stock in our OrderLine Object
                                                                 orln._QtySupplied += stockQty
                                                                 ' Update the logged on what stock we got and from where
                                                                 orln.locationQtys.Add(New stockLocationAmount(orln._ProductID, actualLocation, stockQty, stockloc))
                                                    End If
                                        End If
                           Next
              Next

Return myOrder

End Function

Public Function getLocationID(ByVal ProductID As String, ByVal StockLoc As Integer) As Integer

                    ' Gets the LocationID (Shelf ID?) for the stock column and product id
                    ' passed
                    ' The SQL will look Something like:
                    Dim strSQL As String
                    strSQL = "SELECT " & _
                                  "   location" & StockLoc & " " & _
                                  "FROM " & _
                                  "   tbl_stock_location " & _
                                  "WHERE " & _
                                  " stock_id = " & ProductID
         End Function

Public Sub adjustStockForProductAtLocation(ByVal ProductID As String, ByVal StockLoc As Integer, ByVal NewStockQTy As Integer)

                   ' Update the tbl_stock_location with the updated stock level.
                   ' The SQL will look Something like:
                   Dim strSQL As String
                   strSQL = "UPDATE " & _
                                  "    tbl_stock_location " & _
                                  "SET " & _
                                  "    qty" & StockLoc & " = " & NewStockQTy & " " & _
                                  "WHERE " & _
                                  "    stock_ID = " & ProductID
          End Sub

Public Function getStockForProductAtLocation(ByVal ProductID As String, ByVal StockLoc As Integer) As Integer

                       ' Query the database and return the amount of stock for this product
                       ' in this location.
                       ' The SQL will look Something like:
                       Dim strSQL As String
                       strSQL = "SELECT " & _
                                      "    ISNULL(qty" + StockLoc & ", 0) as Stock " & _
                                      "FROM " & _
                                      "    tbl_stock_location " & _
                                      "WHERE " & _
                                      "    stock_id = " & ProductID
           End Function

End Class

Public Class OrderLine
            ' I would add getter's and setters
            ' just doing this to be quick
             Public _Qty As Integer
             Public _ProductID As Integer
             ' This will hold the actual amount of stock fullfilled
             ' we need this incase the staff member wants a higher qty than
             ' we have in stock at all the locations combined.
            Public _QtySupplied As Integer
             ' The collection of stock supplied from each location
            Public locationQtys As New List(Of stockLocationAmount)

            Public Sub New(ByVal Qty As Integer, ByVal ProductID As Integer)
                       _Qty = Qty
                       _ProductID = ProductID
            End Sub

End Class

' This class will keep track of how much stock
' was used up from each location
Public Class stockLocationAmount

             Public _ProductID As String
             Public _LocationID As Integer
             Public _QtySupplied As Integer
             Public _LocationColumn As Integer

             Public Sub New(ByVal ProdID As String, ByVal LocationID As Integer, ByVal QtySupplied As Integer, ByVal LocColumn As Integer)
                      _ProductID = ProdID
                      _LocationID = LocationID
                      _QtySupplied = QtySupplied
                      _LocationColumn = LocColumn
             End Sub

End Class

Read through that all look at the notes I have made and see if that will help you. Let me know how you get on.