Results 1 to 9 of 9

Thread: [RESOLVED] Grouping / add empy row following end of a "group" on data grid

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2020
    Location
    UNITED KINGDOM
    Posts
    59

    Resolved [RESOLVED] Grouping / add empy row following end of a "group" on data grid

    Hi All

    Apologies if this has already been solved, did have a search through various pages on this forum but didn't find an answer.

    I have added a datagrid into my form which gets its data from the following code:

    Code:
     
    con.Open()
            cmd = con.CreateCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "Select works_order, component, comp_desc, batch_no from tbl_batches where works_order = '" & txt_search.Text & "' order by component"
            cmd.ExecuteNonQuery()
            Dim rdr As SqlDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(rdr)
            DataGridView1.DataSource = dt
            con.Close()
    This works fine and data is displayed correctly in the grid. User will always search for one works order, however this works order can have multiple components and each of the components can have multiple batches, therefore you may get component 1 appearing 10 times (10 different batches), component 2 appearing x6 etc. At the moment, I have to manually see where the end of one component is and the beginning of the next.

    Is there a way to either colour the start of a new component row or place an empty row after one component ends? Whatever is easiest really, no preference. I am learning coding so still at beginner level!

    Thank you in advance.

  2. #2

    Thread Starter
    Member
    Join Date
    Oct 2020
    Location
    UNITED KINGDOM
    Posts
    59

    Re: Grouping / add empy row following end of a "group" on data grid

    Update - I found the below code and manipulated and I believe I am nearly there but not quite (which is not good enough!)
    My default row colour is black and for test purposes I want the first change in component to be white smoke then revert to black again when another component appears in a row.
    Running the below code, the first row is black, the second row changes to white smoke because it is a different component code, however after that all the rows are white smoke even though there are about 4 different component code changes... any ideas?


    Code:
            Dim j As String = -1
            Dim colr As Color
    
            For i = 1 To DataGridView1.RowCount - 1
                If DataGridView1.Rows(i).Cells(1).Value <> j Then
                    j =
                DataGridView1.Rows(i).Cells(1).Value
                    Select Case colr
                        Case Color.WhiteSmoke : colr = Color.Black
                        Case Color.Black : colr = Color.WhiteSmoke
                    End Select
                End If
                DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.WhiteSmoke
            Next
    I did try changing the last line to read DataGridView1.Rows(i).DefaultCellStyle.BackColor = colr but this changes the remaining rows to whitesmoke too.
    Last edited by AMJADJ75; Oct 12th, 2020 at 04:32 PM.

  3. #3
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Grouping / add empy row following end of a "group" on data grid

    Code:
    DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.WhiteSmoke
    DataGridView1.Rows(i+1).DefaultCellStyle.BackColor = Color.Black

  4. #4

    Thread Starter
    Member
    Join Date
    Oct 2020
    Location
    UNITED KINGDOM
    Posts
    59

    Re: Grouping / add empy row following end of a "group" on data grid

    Hi Paul

    Thanks for replying. Do those lines go above "next" and therefore replacing that single line?
    If thats the only code i need then in my limited knowledge won't that just give me alternate row colours?

    Ideally what I would like is my background to be black as default then at every change of component, the first row for that component to be a different colour, although as above i'm happy if the first row for a component alternates.

    Thank you
    Last edited by AMJADJ75; Oct 13th, 2020 at 01:29 PM.

  5. #5
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Grouping / add empy row following end of a "group" on data grid

    Code:
    Dim j As Object = -1
    
    For i = 1 To DataGridView1.RowCount - 1
        If DataGridView1.Rows(i).Cells(1).Value <> j Then
            j = DataGridView1.Rows(i).Cells(1).Value
            DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.WhiteSmoke
        Else
             DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.Black
        End If
    Next

  6. #6

    Thread Starter
    Member
    Join Date
    Oct 2020
    Location
    UNITED KINGDOM
    Posts
    59

    Re: Grouping / add empy row following end of a "group" on data grid

    Hi Paul

    This works great and as requested, thank you very much. I just have 2 points if I may:

    1) I have changed the datagrid manipulating your code so that all "headers" i.e. start of a new component no are one colour back and forecolour and detail lines are another. However the forst row is being ignored in the above code and therefore this is displayed as the "detail" colour. As we know the first row will always be a start of a new component, how can we make this the colour which is shows when its <> j

    2) Is it possible and this is a nice to have so if its time consuming, tell me to make my way out !! its not that easy on the eye to work out by colour headers and details, is it possible to insert a blank row previous to when a new component row appears? this will just group and seprate nicely and be easy on the eye. So in short place a new line when one the component details for one component end and the next line is a new component.

    Really appreciate your time and effort to reply, not only is it fixing but helping me increase my knowledge.,

  7. #7
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Grouping / add empy row following end of a "group" on data grid

    Code:
    Dim j As Object = -1
    
    DataGridView1.Rows(0).DefaultCellStyle.BackColor = Color.WhiteSmoke
    
    For i = 1 To DataGridView1.RowCount - 1
        If DataGridView1.Rows(i).Cells(1).Value <> j Then
            j = DataGridView1.Rows(i).Cells(1).Value
            DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.WhiteSmoke
        Else
             DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.Black
        End If
    Next
    For inserting empty rows...

    Code:
    Dim j As Object = -1
    
    DataGridView1.Rows(0).DefaultCellStyle.BackColor = Color.WhiteSmoke
    
    For i = 1 To DataGridView1.RowCount - 1
        If DataGridView1.Rows(i).Cells(1).Value <> j Then
            j = DataGridView1.Rows(i).Cells(1).Value
            DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.WhiteSmoke
            DataGridView1.Rows.Insert(i, nothing)
        Else
             DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.Black
        End If
    Next

  8. #8
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Grouping / add empy row following end of a "group" on data grid

    Change the ‘for i’ block for a do while block, as inserting rows in the loop will ruin the loop counting...

    Code:
    Dim j As Object = -1
    Dim i as Integer = 1
    
    DataGridView1.Rows(0).DefaultCellStyle.BackColor = Color.WhiteSmoke
    
    Do While  i < To DataGridView1.RowCount - 1
        If DataGridView1.Rows(i).Cells(1).Value <> j Then
            j = DataGridView1.Rows(i).Cells(1).Value
            DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.WhiteSmoke
            DataGridView1.Rows.Insert(i, nothing)
        Else
             DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.Black
        End If
        I+= 1
    Loop
    Last edited by .paul.; Oct 14th, 2020 at 09:20 AM.

  9. #9

    Thread Starter
    Member
    Join Date
    Oct 2020
    Location
    UNITED KINGDOM
    Posts
    59

    Re: Grouping / add empy row following end of a "group" on data grid

    Hey Paul

    I can't thank you enough mate, again you not only helped me solve my issue but increased my coding knowledge.

    I had to change your code slightly as I hadn't explained properly in the first place and having a looked at the output I realised what I wanted the data grid to look like hence changing the code, but I would have got nowhere without your help so thank you....

    This is the revised code I used... might help someone in future along with your code...

    Code:
    Dim i As Integer = 1
            Dim j As Object
    
            Do While i < DataGridView1.RowCount - 1
                j = DataGridView1.Rows(i + -1).Cells(3).Value
                If DataGridView1.Rows(i).Cells(3).Value = j Then
                    DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.Azure
                    DataGridView1.Rows(i).DefaultCellStyle.ForeColor = Color.Black
                Else
                    DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.Black
                    DataGridView1.Rows(i).DefaultCellStyle.ForeColor = Color.FromArgb(255, 175, 14)
                End If
                i += 1
            Loop
    I was trying to be clever and manipulate further by removing the repeated cell values (component number and component description but got stuck on resetting the value of N to the row number of O below....

    Code:
            Dim N As Integer = 1
            Dim O As Object
            O = DataGridView1.Rows(N - 1).Cells(3).Value
    
            Do While N < DataGridView1.RowCount - 1
    
                If DataGridView1.Rows(N).Cells(3).Value = O Then
                    DataGridView1.Rows(N).Cells(3).Value = ""
                    N = N
                    O += 1
                Else
                    N = 'I got stuck here as I didn't know how to make this the row number of O
                End If
    
            Loop
    Thank you once again mate the datagrid looks a lot more presentable, I cant thank you enough.... take care

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width