VB.NET DataGridView -> Add a column and fill cells with data from another cell

Question

0.00/5 (No votes)

See more:

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

VB

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

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!

Posted 21-Nov-13 8:41am

Eowyne

Add a Solution

2 solutions

Solution 2

VB

' 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

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.

Posted 25-Nov-13 10:24am

Eowyne

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2013-11-23T06:26:00

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.

VB

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