如何循环表 [英] How to loop through table
本文介绍了如何循环表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
现在我的代码获取指定日期之间的所有顺序并将它们放入一个表中。但是我希望每个订单都有自己的表格...我将如何去做这个
公共 Sub GetBrokenUpOrders（）
Dim dv As DataView = CType （SqlShippingReport。选择（DataSourceSelectArguments.Empty），DataView）
如果 dv.Count> 0 然后
Dim dr As DataRowView = dv.Item（ 0 ）
Session（ SHIPPEDSUMMARYTABLE）= GridView1.ToString
结束 如果
SqlShippingReportDataTable.SelectCommand = SELECT ITEM.ITEMNM，ITEM.DSC，ITMPRICE.PRICE& _
FROM ITEM INNER JO IT ITMPRICE on ITEM.ITEMNM = ITMPRICE.ITEMNM& _
WHERE（ITMPRICE.ACCTNBR =？）'& lblAccount.Text& '
SqlShippingReportDataTable.SelectCommand = SELECT QTY& _
FROM ORDERDET& _
WHERE ACCTNBR ='& lblAccount.Text& '
' 创建数据表以存储信息
Dim dt As 新 DataTable
dt.Columns.Add（ 库存代码， GetType （ String ））
dt.Columns.Add（ 库存描述， GetType （ String ））
dt.Columns.Add（ 数量， GetType （ String ））
dt.Columns.Add（ 价格， GetType （ String ））
dt.Columns。添加（ Extension， GetType （ String ））
Session（ DT ）= dt
' 使用信息填写表
Dim t As DataTable = DirectCast （会话（ DT），DataTable）
Dim sGroup As String =
Dim gr oupCt 作为 整数 = 0
执行直到groupCt = 7
选择 案例 groupCt
案例 0
sGroup = Shakes
案例 1
sGroup = PackKits
案例 2
sGroup = 主体
案例 3
sGroup = 谷物
案例 4
sGroup = Flavorings
案例 5
sGroup = Bars
案例 6
sGroup = 材料
结束 选择
SqlShippingReportDataTable.SelectCommand = SELECT ITEM.ITEMNM，ITE M.DSC，ITMPRICE.PRICE& _
FROM ITEM INNER JO IT ITMPRICE on ITEM.ITEMNM = ITMPRICE.ITEMNM& _
WHERE（ITMPRICE.ACCTNBR ='& lblAccount.Text& < span class =code-string> '）和ITEM.STATUS ='A'& _
和GroupCode IN（选择'& sGroup& '）rptsrtodr的命令
' 数据视图
Dim DV2 作为 DataView = CType （SqlShippingReportDataTable。选择（DataSourceSelectArguments.Empty），DataView）
如果 DV2.Count> 0 然后
Dim i 作为 整数 = 0
直到i = DV2.Count
' 创造&填充Datarow
Dim dr As DataRowView = DV2.Item（i）
' 创建&填充数据表行
Dim 行 As DataRow = t.NewRow（）
行（ 库存代码）= dr.Item（ 0 ）。ToString
行（ 库存描述 ）= dr.Item（ 1 ）。ToString
行（ < span class =code-string> Price）=（ CDec （dr.Item（ 2 ).ToString)).ToString( C）
SqlShippingReportDataTable.SelectCommand = 从ORDERDET选择SUM（OD.QTY）为OD，Orderhdr为O，其中OD.ITEMID = '& _
dr.Item（ 0 ）。ToString& '和O.CRDT> ='&会话（ StartDate）& '和O.CRDT< ='& _
会话（ EndDate）& '和OD.ORDERHDRID = O.ORDERHDRID和O.ACCTNBR ='& _
lblAccount.Text& '和O.ORDSTATUS<>'已删除'
Dim DV3 As DataView = CType （SqlShippingReportDataTable 。选择（DataSourceSelectArguments.Empty），DataView）
如果 DV3.Count> 0 然后
Dim dr1 As DataRowView = DV3.Item（ 0 ）
' 如果Quanity为null，则在数量列中输入0
If IsDBNull（dr1.Item（ 0 ）。ToString）= False 然后
行（ 数量）= dr1.Item （ 0 ）。ToString
Else
row（ 数量）= 0
结束 如果
' 如果Price为null，则在数量列中输入0.00
如果 IsDBNull（dr.Item（ 2 ）。ToString）= False 然后
行（ Price ）= dr.Item（ 2 ）。ToString
Else
行（ 价格）= 0.00
结束 如果
其他
行（ 数量）= 0
行（ 价格）= 0.00
结束 如果
' 如果数量为空，则在列中添加0
如果行（ Quantity）。ToString = 字符串 .Empty 然后
行（ 数量）= 0
结束 如果
' 如果价格为空，则在列中显示0.00
如果 row（ Price）。ToString = String .Empty 然后
行（ 价格）= 0.00
结束 如果
' 获取m的扩展名最终数量按价格计算
行（ 扩展名）=（< span class =code-keyword> CDbl （row（ Quantity）。 ToString）* CDbl （行（ Price）。ToString））。ToString（ C）
row（ Price）=（ CDec （博士。 Item（ 2 ）。ToString））。ToString（ C）
' 如果数量大于0，则显示行，如果数量为0则不显示行
如果行（ 数量）> 0 然后
t.Rows.Add（row）
结束 如果
i = i + 1
循环
结束 如果
groupCt = groupCt + 1
循环
' 保存表，以便我们可以添加它
会话（ DT） = t
' 将数据表写入gridview并显示结果
GridView2.DataSource =会话（ DT）
GridView2.DataBind（）
结束 Sub
解决方案
< blockquote>你现在的方式是正确的，你不想为每个订单创建一个新表。当您有1000个订单或5000个订单时会发生什么？你将有5000张桌子。从数据库管理的角度来看，这是一场噩梦，在您开始添加关系和触发器后，您将遇到真正的问题。
将您的订单保存在一个表中，它更容易使用，以后更容易扩展，并且在数据库引擎上更容易。
Right now my code takes all the order between a specified date and puts them into one table ..However I want each order to have its own table.. how would i go about doing that
Public Sub GetBrokenUpOrders()
Dim dv As DataView = CType(SqlShippingReport.Select(DataSourceSelectArguments.Empty), DataView)
If dv.Count > 0 Then
Dim dr As DataRowView = dv.Item(0)
Session("SHIPPEDSUMMARYTABLE") = GridView1.ToString
End If
SqlShippingReportDataTable.SelectCommand = "SELECT ITEM.ITEMNM, ITEM.DSC, ITMPRICE.PRICE" & _
"FROM ITEM INNER JOIN ITMPRICE ON ITEM.ITEMNM = ITMPRICE.ITEMNM" & _
"WHERE (ITMPRICE.ACCTNBR = ?)'" & lblAccount.Text & "'"
SqlShippingReportDataTable.SelectCommand = "SELECT QTY" & _
"FROM ORDERDET " & _
"WHERE ACCTNBR='" & lblAccount.Text & "'"
'Create Datatable to store info
Dim dt As New DataTable
dt.Columns.Add("Inventory Code", GetType(String))
dt.Columns.Add("Inventory Description", GetType(String))
dt.Columns.Add("Quantity", GetType(String))
dt.Columns.Add("Price", GetType(String))
dt.Columns.Add("Extension", GetType(String))
Session("DT") = dt
'Fill Table with Info
Dim t As DataTable = DirectCast(Session("DT"), DataTable)
Dim sGroup As String = ""
Dim groupCt As Integer = 0
Do Until groupCt = 7
Select Case groupCt
Case 0
sGroup = "Shakes"
Case 1
sGroup = "PackKits"
Case 2
sGroup = "Entrees"
Case 3
sGroup = "Cereal"
Case 4
sGroup = "Flavorings"
Case 5
sGroup = "Bars"
Case 6
sGroup = "Materials"
End Select
SqlShippingReportDataTable.SelectCommand = "SELECT ITEM.ITEMNM, ITEM.DSC, ITMPRICE.PRICE " & _
"FROM ITEM INNER JOIN ITMPRICE ON ITEM.ITEMNM = ITMPRICE.ITEMNM " & _
"WHERE (ITMPRICE.ACCTNBR = '" & lblAccount.Text & "')and ITEM.STATUS = 'A'" & _
"and GroupCode IN (select '" & sGroup & "') order by rptsrtodr"
' Dataview
Dim DV2 As DataView = CType(SqlShippingReportDataTable.Select(DataSourceSelectArguments.Empty), DataView)
If DV2.Count > 0 Then
Dim i As Integer = 0
Do Until i = DV2.Count
'Create & fill Datarow
Dim dr As DataRowView = DV2.Item(i)
'Create & fill Data Table Row
Dim row As DataRow = t.NewRow()
row("Inventory Code") = dr.Item(0).ToString
row("Inventory Description") = dr.Item(1).ToString
row("Price") = (CDec(dr.Item(2).ToString)).ToString("C")
SqlShippingReportDataTable.SelectCommand = "Select SUM(OD.QTY) from ORDERDET as OD, Orderhdr as O where OD.ITEMID ='" & _
dr.Item(0).ToString & "' and O.CRDT >='" & Session("StartDate") & "' and O.CRDT <='" & _
Session("EndDate") & "' and OD.ORDERHDRID = O.ORDERHDRID and O.ACCTNBR = '" & _
lblAccount.Text & "' and O.ORDSTATUS <> 'Deleted'"
Dim DV3 As DataView = CType(SqlShippingReportDataTable.Select(DataSourceSelectArguments.Empty), DataView)
If DV3.Count > 0 Then
Dim dr1 As DataRowView = DV3.Item(0)
' If Quanity is null then put in a 0 in the quantity column
If IsDBNull(dr1.Item(0).ToString) = False Then
row("Quantity") = dr1.Item(0).ToString
Else
row("Quantity") = "0"
End If
' If Price is null then put 0.00 in the quantity column
If IsDBNull(dr.Item(2).ToString) = False Then
row("Price") = dr.Item(2).ToString
Else
row("Price") = "0.00"
End If
Else
row("Quantity") = "0"
row("Price") = "0.00"
End If
' If Quantity is empty place a 0 in the column
If row("Quantity").ToString = String.Empty Then
row("Quantity") = "0"
End If
' If Price is empty place 0.00 in the column
If row("Price").ToString = String.Empty Then
row("Price") = "0.00"
End If
' Get Extension by multiplying Quantity by the Price
row("Extension") = (CDbl(row("Quantity").ToString) * CDbl(row("Price").ToString)).ToString("C")
row("Price") = (CDec(dr.Item(2).ToString)).ToString("C")
' If the quantity is greater than 0 then show the row, if the quantity is 0 don't show the row
If row("Quantity") > "0" Then
t.Rows.Add(row)
End If
i = i + 1
Loop
End If
groupCt = groupCt + 1
Loop
'Save table so we can add to it
Session("DT") = t
'Write Datatable to gridview and display results
GridView2.DataSource = Session("DT")
GridView2.DataBind()
End Sub 解决方案
The way you are doing it now is correct, you do NOT want to create a new table for each order. What happens when you have 1000 orders, or 5000? You will have 5000 tables. This is a nightmare from a database management standpoint, after you start adding relationships and triggers then you are going to have a real problem.
Keep your orders in a single table, its much easier to work with, much easier to expand later, and is a hell of a lot easier on the database engine.
这篇关于如何循环表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
