-
Oct 12th, 2020, 03:46 PM
#1
Thread Starter
Member
[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.
-
Oct 12th, 2020, 04:26 PM
#2
Thread Starter
Member
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.
-
Oct 13th, 2020, 12:31 PM
#3
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Oct 13th, 2020, 12:58 PM
#4
Thread Starter
Member
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.
-
Oct 13th, 2020, 07:03 PM
#5
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Oct 14th, 2020, 07:52 AM
#6
Thread Starter
Member
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.,
-
Oct 14th, 2020, 08:26 AM
#7
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Oct 14th, 2020, 08:34 AM
#8
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.
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Oct 14th, 2020, 05:11 PM
#9
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|