如何使用一个datagridview和tabcontrol从5个不同的表中进行过滤 [英] How do I filter from 5 diferent tables using one datagridview and tabcontrol
问题描述
我有4张关于计算机硬件的表格。 桌面,笔记本, RAM , CPU 和 DISK 。
这是我所做的事情的剪辑,但效果并不好。
我是什么尝试过:
如果 tcStock.SelectedIndex = 0 然后
Dim ds 作为 新 DataSet
Dim dt 作为 新 DataTable
ds.Tables.Add(dt)
Dim da As 新 OleDbDataAdapter( SELECT * FROM CPU WHERE sold = 0,connection)
da.Fill(dt)
Dim coluna As DataRow
对于 每个 coluna 在 dt.Rows
lvProdutos .Items.Add(coluna.Item( 0 ))
lvProdutos.Items(lvProdutos.Items.Count - 1 )。SubItems.Add(coluna.Item( 1 ))
下一步
ElseIf tcStock.SelectedIndex = 1 然后
Dim ds 作为 新 DataSet
Dim dt As 新 DataTable
ds.Tables.Add(dt)
Dim da As 新 OleDbDataAdapter( SELECT * FROM RAM WHERE sold = 0 ,connection)
da.Fill(dt)
Dim coluna As DataRow
对于 每个 coluna In dt.Rows
lvProdutos.Items.Add(coluna.Item( 0 ))
lvProdutos.Items(lvProdutos.Items。计数 - 1 )。SubItems.Add(coluna.Item( 1 ))
下一步
ElseIf tcStock.SelectedIndex = 2 然后
Dim ds 作为 新 DataSet
Dim dt 作为 新 DataTable
ds.Tables.Add(dt)
Dim da As 新 OleDbDataAdapter( SELECT * FROM DISK WHERE sold = 0,connection)
da.Fill(dt)
Dim coluna As DataRow
对于 每个 coluna 在 dt.Rows
lvProdutos.Items.Add(coluna.Item( 0 ))
lvProdutos.Items(lvProdutos.Items.Count - 1 )。SubItems.Add(coluna.Item( 1 ))
下一步
ElseIf tcStock .SelectedIndex = 3 然后
Dim ds 作为 新 DataSet
Dim dt 作为 新 DataTable
ds.Tables.Add(dt)
Dim da 作为 新 OleDbDataAdapter(< span class =code-string> SELECT * FROM NOTEBOOK WHERE sold = 0,connection)
da.Fill(dt)
Dim coluna As DataRow
对于 每个 coluna 在 dt.Rows
lvProdutos.Items.Add(coluna.Item(< span class =code-digit> 0 ))
lvProdutos.Items(lvProdutos.Items.Count - 1 )。SubItems.Add (coluna.Item( 1 ))
下一步
ElseIf tcStock.SelectedIndex = 4 然后
Dim ds As 新 DataSet
< span class =code-keyword> Dim dt As 新 DataTable
ds.Tables.Add(dt)
Dim da As 新 OleDbDataAdapter( SELECT * FROM DESKTOP WHERE sold = 0,连接)
da.Fill(dt)
Dim coluna As DataRow
对于 每个 coluna 在 dt.Rows
lvProdutos.Items.Add(coluna.Item( 0 ))
lvProdutos.Items(lvProdutos.Items.Count - 1 )。SubItems.Add(coluna.Item( 1 ))
下一步
结束 如果
首先看重复在代码量。首先整理一下,这样我们就可以一目了然地看到我们正在处理的是什么...
第1版:Dim ds 作为 新 DataSet
Dim dt As 新 DataTable
ds.Tables.Add(dt)
Dim sql As String
如果 tcStock.SelectedIndex = 0 然后
sql = SELECT * FROM CPU WHERE sold = 0
ElseIf tcStock.SelectedIndex = 1 然后
sql = SELECT * FR OM RAM WHERE售出= 0
ElseIf tcStock.SelectedIndex = 2 < span class =code-keyword>然后
sql = SELECT * FROM DISK WHERE sold = 0
ElseIf tcStock.SelectedIndex = 3 然后
sql = SELECT * FROM NOTEBOOK WHERE sold = 0
ElseIf tcStock.SelectedIndex = 4 然后
sql = SELECT * FROM DESKTOP WHERE sold = 0
结束 如果
Dim da As 新 OleDbDataAdapter(sql,connection)
da.Fill(dt)
Dim coluna As DataRow
对于 每个 coluna 在 dt.Rows
lvProdutos.Items.Add(coluna.Item( 0 ))
lvProdutos。项目(lvProdutos.Items.Count - 1 )。SubItems.Add(coluna.Item( 1 ))
下一页这有点整洁,但仍有一些重复...它只是我们需要更改的表名在SQL中(假设您使用这种糟糕的数据库设计)。
第2版:Dim tableNames( )作为 字符串 = 新 字符串(){ CPU, RAM, DISK, NOTEBOOK, DESKTOP }
Dim sql As 字符串 = 字符串 .Format( SELECT * FROM {0} WHERE sold = 0,tableNames(tcStock.SelectedIndex))
Dim ds As 新 DataSet
Dim dt As 新 DataTable
ds.Tables.Add(dt)
昏暗温泉n> da 作为 新 OleDbDataAdapter(sql,connection)
da.Fill(dt)
对于 每个 coluna As DataRow 在 dt.Rows
lvProdutos.Items.Add(coluna.Item( 0 ))
lvProdutos.Items(lvProdutos.Items.Count - 1 )。SubItems.Add(coluna.Item( 1 ))
下一步
现在这很整洁我可以看看潜在的问题。
首先,你没有清理ListView中已有的任何项目,其次不清楚你是否已经设置了列。另外,虽然你已经意识到第二列需要进入子项目是很好的,但有一种更简单的方法。
尝试这样做而不是
< pre lang =VB> lvProdutos.Columns.Clear()' 清除列
lvProdutos.Items.Clear()' 清除所有现有项目
对于 每个 col 作为 DataColumn 在 dt.Columns
lvProdutos.Columns.Add(col.ToString)' 根据返回的数据添加列
下一步
对于 < span class =code-keyword>每个 coluna As DataRow 在 dt.Rows
lvProdutos.Items.Add(coluna.Item( 0 ),coluna.Item( 1 ))
下一页
但是现在让我们再看看你的桌子。你还没有分享很多关于它们的信息,但我可以猜测有可能是制造商,销售,初始库存等等所有表格都非常相似。
正如我在评论中所说,更好的设计是为所有硬件类型设置一个表,但该表上的每一行都知道它的硬件类型。将硬件类型列表保存在单独的参考表中。这样的东西...
CREATE TABLE HardwareType
(
TypeId int identity ( 1 , 1 ) NOT NULL ,
TypeName nvarchar ( 125 ),
< span class =code-keyword> PRIMARY KEY (TypeId)
)
INSERT INTO HardwareType VALUES
(' 桌面'),
(' Notebook'),
(' RAM') ,
(' CPU'),
(' DISK')
SELECT * FROM HardwareType
CREATE TABLE 硬件
(
HwId int identity ( 1 , 1 )
,HwType int
,已售出 int
,制造商 nvarchar ( 125 )
,[description] nvarchar ( 125 )
- ,其他所需列
, CONSTRAINT FK_HardwareType FOREIGN KEY (HwType)
REFERENCES HardwareType (TypeId)
)
要恢复数据,你可以这样做......
SELECT HW.manufacturer,HW.description,HWT.TypeName,售价
来自硬件HW
INNER JOIN HardwareType HWT ON HW.HwType = HWT.TypeId
请注意我的SQL语句中我明确列出了我需要查看的列,我不使用SELECT * ...
。如果有人在以后向表中添加新列,我的listview列不会突然改变
I have 4 tables regarding computer hardware. Desktop, Notebook, RAM, CPU and DISK.
Here is a snipped of what I did, but it isn't quite working well.
What I have tried:
If tcStock.SelectedIndex = 0 Then
Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)
Dim da As New OleDbDataAdapter("SELECT * FROM CPU WHERE sold = 0", connection)
da.Fill(dt)
Dim coluna As DataRow
For Each coluna In dt.Rows
lvProdutos.Items.Add(coluna.Item(0))
lvProdutos.Items(lvProdutos.Items.Count - 1).SubItems.Add(coluna.Item(1))
Next
ElseIf tcStock.SelectedIndex = 1 Then
Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)
Dim da As New OleDbDataAdapter("SELECT * FROM RAM WHERE sold = 0", connection)
da.Fill(dt)
Dim coluna As DataRow
For Each coluna In dt.Rows
lvProdutos.Items.Add(coluna.Item(0))
lvProdutos.Items(lvProdutos.Items.Count - 1).SubItems.Add(coluna.Item(1))
Next
ElseIf tcStock.SelectedIndex = 2 Then
Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)
Dim da As New OleDbDataAdapter("SELECT * FROM DISK WHERE sold = 0", connection)
da.Fill(dt)
Dim coluna As DataRow
For Each coluna In dt.Rows
lvProdutos.Items.Add(coluna.Item(0))
lvProdutos.Items(lvProdutos.Items.Count - 1).SubItems.Add(coluna.Item(1))
Next
ElseIf tcStock.SelectedIndex = 3 Then
Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)
Dim da As New OleDbDataAdapter("SELECT * FROM NOTEBOOK WHERE sold = 0", connection)
da.Fill(dt)
Dim coluna As DataRow
For Each coluna In dt.Rows
lvProdutos.Items.Add(coluna.Item(0))
lvProdutos.Items(lvProdutos.Items.Count - 1).SubItems.Add(coluna.Item(1))
Next
ElseIf tcStock.SelectedIndex = 4 Then
Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)
Dim da As New OleDbDataAdapter("SELECT * FROM DESKTOP WHERE sold = 0", connection)
da.Fill(dt)
Dim coluna As DataRow
For Each coluna In dt.Rows
lvProdutos.Items.Add(coluna.Item(0))
lvProdutos.Items(lvProdutos.Items.Count - 1).SubItems.Add(coluna.Item(1))
Next
End If
First look at the amount of repetition in your code. Tidy that up first so we can see at a glance what we're dealing with...
Pass 1:Dim ds As New DataSet Dim dt As New DataTable ds.Tables.Add(dt) Dim sql As String If tcStock.SelectedIndex = 0 Then sql = "SELECT * FROM CPU WHERE sold = 0" ElseIf tcStock.SelectedIndex = 1 Then sql = "SELECT * FROM RAM WHERE sold = 0" ElseIf tcStock.SelectedIndex = 2 Then sql = "SELECT * FROM DISK WHERE sold = 0" ElseIf tcStock.SelectedIndex = 3 Then sql = "SELECT * FROM NOTEBOOK WHERE sold = 0" ElseIf tcStock.SelectedIndex = 4 Then sql = "SELECT * FROM DESKTOP WHERE sold = 0" End If Dim da As New OleDbDataAdapter(sql, connection) da.Fill(dt) Dim coluna As DataRow For Each coluna In dt.Rows lvProdutos.Items.Add(coluna.Item(0)) lvProdutos.Items(lvProdutos.Items.Count - 1).SubItems.Add(coluna.Item(1)) NextThat's a bit tidier, but there is still some repetition ... it's only the table name that we need to change in the SQL (assuming you stay with this bad database design).
Pass 2:Dim tableNames() As String = New String() {"CPU", "RAM", "DISK", "NOTEBOOK", "DESKTOP"} Dim sql As String = String.Format("SELECT * FROM {0} WHERE sold = 0", tableNames(tcStock.SelectedIndex)) Dim ds As New DataSet Dim dt As New DataTable ds.Tables.Add(dt) Dim da As New OleDbDataAdapter(sql, connection) da.Fill(dt) For Each coluna As DataRow In dt.Rows lvProdutos.Items.Add(coluna.Item(0)) lvProdutos.Items(lvProdutos.Items.Count - 1).SubItems.Add(coluna.Item(1)) Next
Now that's quite tidy and I can see potential problems.
Firstly you are not clearing down any items already in the ListView and secondly it's not clear if you have set up the columns. Also, whilst it's good that you have realised the 2nd column needs to go into the sub-items, there is an easier way.
Try doing it this way instead
lvProdutos.Columns.Clear() 'Clear out the columns lvProdutos.Items.Clear() 'Clear out any existing items For Each col As DataColumn In dt.Columns lvProdutos.Columns.Add(col.ToString) 'Add columns depending on the data returned Next For Each coluna As DataRow In dt.Rows lvProdutos.Items.Add(coluna.Item(0), coluna.Item(1)) Next
But now let's revisit your tables. You haven't shared much information about them but I can guess that there is probably stuff like manufacturer, sold, initial stock etc and that all of the tables are very very similar.
As I said in my comment, a better design would be to have a single table for all hardware types, but each row on that table knows what "type" of hardware it is. Keep the list of hardware types in a separate reference table. Something like this...
CREATE TABLE HardwareType ( TypeId int identity(1,1) NOT NULL, TypeName nvarchar(125), PRIMARY KEY (TypeId) ) INSERT INTO HardwareType VALUES ('Desktop'), ('Notebook'), ('RAM'), ('CPU'), ('DISK') SELECT * FROM HardwareType CREATE TABLE Hardware ( HwId int identity(1,1) ,HwType int ,sold int ,manufacturer nvarchar(125) ,[description] nvarchar(125) -- ,other columns as required ,CONSTRAINT FK_HardwareType FOREIGN KEY (HwType) REFERENCES HardwareType(TypeId) )
To get the data back out you can do something like this...
SELECT HW.manufacturer, HW.description, HWT.TypeName, sold FROM Hardware HW INNER JOIN HardwareType HWT ON HW.HwType=HWT.TypeId
Note in my SQL statement I explicitly list the columns that I need to see, I don't useSELECT * ...
.That way if someone adds a new column to the table(s) at a later date my listview columns won't suddenly change.
这篇关于如何使用一个datagridview和tabcontrol从5个不同的表中进行过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!