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. :)
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!
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 :(
Jeremy Huson
Senior Network Engineer
Carden Computers - PC Repair Brighton
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
:(
Jeremy Huson
Senior Network Engineer
Carden Computers - PC Repair Brighton
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.
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
Jeremy Huson
Senior Network Engineer
Carden Computers - PC Repair Brighton
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?
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 [;)]
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
Jeremy Huson
Senior Network Engineer
Carden Computers - PC Repair Brighton
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:
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.
david wendel...
All-Star
15865 Points
2243 Posts
Re: reading from one table, writing to another in loop
Nov 13, 2007 12:32 AM|LINK
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. :)
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 13, 2007 04:26 PM|LINK
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 :(
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 13, 2007 04:39 PM|LINK
Could you not get the code I suggested last time to work?
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 13, 2007 04:50 PM|LINK
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 :(
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 13, 2007 04:52 PM|LINK
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.
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 13, 2007 05:09 PM|LINK
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
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 13, 2007 07:21 PM|LINK
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 [;)]
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 13, 2007 07:29 PM|LINK
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
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 13, 2007 07:49 PM|LINK
[;)]
Right let me find the code I posted for you....
scott@elband...
Star
11346 Points
1865 Posts
Re: reading from one table, writing to another in loop
Nov 13, 2007 10:38 PM|LINK
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.