VB.NET DataGridView - >添加列并使用其他单元格中的数据填充单元格 [英] VB.NET DataGridView -> Add a column and fill cells with data from another cell

问题描述

我有一个DataGridView，我用以下方式填写：

Hi,
I have a DataGridView which I fill in the following way:

Function updategrid(ByVal CurrentBroker, ByVal CurrentPallet)
        Dim i = 0
        Dim SQLText As String
        SQLText = "SELECT " & _
        "invoer.idInvoer," & _
        "invoer.BRKNo as Broker," & _
        "invoer.Palletno as Pallet," & _
        "invoer.Tabblad AS ValTabblad," & _
        "Tabblad.TabbladType, " & _
        "invoer.Hardwaretype as Hardwaretype," & _
        "invoer.Fabrikant AS Fabrikant," & _
        "invoer.Model As Model, " & _
        "invoer.Serienummer, " & _
        "invoer.AssetTag, " & _
        "invoer.Schade, " & _
        "invoer.Opmerkingen, " & _
        "Tabblad.idTabblad, " & _
        "invoer.details, " & _
        "invoer.aantal, " & _
        "invoer.RegisterDate " & _
        "FROM Invoer as invoer " & _
        "INNER JOIN Tabblad as Tabblad ON invoer.Tabblad = tabblad.idTabblad " & _
        "WHERE invoer.BRKNo = '" & CurrentBroker & "'" & _
        "AND invoer.PalletNo = " & CurrentPallet & "  " & _
        "ORDER BY invoer.RegisterDate DESC"

        ds.Tables.Clear()
        Try
            Data = New DataTable
            dataAdap = New MySqlDataAdapter(SQLText, dbconn)

            cmdBuilder = New MySqlCommandBuilder(dataAdap)
            dataAdap.Fill(ds, "Overzicht")
            With DGVOverzicht
                .DataSource = ds.Tables("Overzicht")
                .ColumnHeadersVisible = True
                .AutoGenerateColumns = True
                .Visible = True

                For i = 0 To .ColumnCount - 1 Step 1
                    Select Case .Columns(i).HeaderText
                        Case "ValTabblad" : .Columns(i).Visible = False
                        Case "details"
                            ' hide the ID's, display text..
                            .Columns(i).Visible = False
                        Case "idTabblad" : .Columns(i).Visible = False
                        Case "idHWModel" : .Columns(i).Visible = False
                        Case "idHardware" : .Columns(i).Visible = False
                        Case "idInvoer" : .Columns(i).Visible = False
                    End Select
                Next
            End With
            Return True
        Catch ex As MySqlException
            MsgBox(ex.Message)
            Return False
        End Try

        Return 1
    End Function

这就像一个魅力，但我需要在我隐藏的列旁边添加一列：详细信息。

列详细信息包含记录中选择的所有详细信息的ID（例如， 1; 5; 2; 19; 20; 100）

我想向用户提供有关实际所选细节的更多信息，例如通过在MySQL中查找以查看这些ID实际上是什么细节..



但是，我被告知不要在MySQL中以逗号分隔的列表进行内连接因为它会像'冻结的蜗牛一样慢'..

所以接下来我能想到的是用数据库中的所有信息填充DGV，隐藏ID栏和'找到并替换'另一个单元格中的值，然后在MySQL查找...



如果有人能指出我在这方面的某种方向，我'我非常感激..因为我现在盯着代码看了几个小时，而且似乎没有一个灯泡弹出:)



感谢提前！

This works like a charm, but I need to add a column next to the column which I hide: Details.
The Column Details holds the ID's of all details selected in the record (e.g. 1;5;2;19;20;100)
I want to give the user a bit more information about the actual selected details, e.g. by do a lookup in MySQL to see what details those ID's actually are..

However, I was told not to do a inner join on a comma separated list in MySQL due to the fact that it would be 'slow as frozen snail'..
So the next thing I can think of is filling the DGV with all the information from the Database, hide the ID column and 'find and replace' the values in another cell, and thus looking it up in MySQL..

If anyone could point me into some kind of direction in this, I'd be very grateful.. since I'm staring at the code for over a few hours now, and not a single lightbulbs seems to pop up :)

Thank in advance!

推荐答案

您可以使用VB.NET中的Split方法来实现此目的，请参阅下面的Split方法示例。假设用户单击其隐藏列id包含{1; 2; 3; 4; 5}的行，将ID拆分为数组，然后循环访问数组以获取每个单独的ID，并查询数据库以获取详细信息每个id。

You can use the Split method in VB.NET to achieve this, see a sample on this Split method below. Let say a user click a row where its hidden column ids contains {1;2;3;4;5}, split ids up into array, then loop thru the array to pick up each individual id, and query the database for detail of each id.

Dim ids As String
Dim i As Integer
Dim idArray() As String

ids = "1;2;3;4;5"

idArray = ids.Split(";")

For i = 0 To UBound(idArray)

    'sql query to retrieve detail by id from database

    MsgBox(idArray(i))

Next i

' replace ID's in Details_Explain with ShortCodes from DGV

For row = 0 To DGVOverzicht.Rows.Count - 1
    Dim strDetailsExplain = ""
    Dim DetailIDs As Array
    DetailIDs = Split(DGVOverzicht.Rows(row).Cells("details").Value.ToString, ";")
    For Each item In DetailIDs
        If Not item = Nothing Then
            strDetailsExplain = strDetailsExplain & DGV.Rows(Int(item)).Cells("DetailsCode").Value & ":"
        End If
    Next

    DGVOverzicht.Rows(row).Cells("Details_Explain").Value = strDetailsExplain
Next

这就是我所做的，但它让它变得很慢，所以它不是首选的解决方案。

现在我已经决定'向他们展示ID那么......'的解决方案，因为它不会给PC和Dbase带来太多压力..所以感谢你的帮助，

你的解决方案是一个解决方案，所以我会接受它。

This is what I did, but it made it slow as hell, so it's not a preferred solution.
Right now I've settled for the 'show them the ID's then..' solution, because it does not invoke a lot of strain on the PC nor the Dbase.. so thanks for your help,
your solution was a solution, so I will accept it.

这篇关于VB.NET DataGridView - >添加列并使用其他单元格中的数据填充单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！