-
Oct 30th, 2020, 06:28 PM
#1
Thread Starter
Member
[RESOLVED] colour for buttons on datagrid to change if sql record exists
Hi All
Hope you are safe and well.
Within the load sub on a form, I create a datagrid and insert values staff_id, forename, surname and company. My first two columns are buttons for each row in the datagrid.
What I would like to do is once the datagrid has loaded (code working fine) I would like it to go through one row at a time and check if that staff_id has already been booked for this Saturday, if it has then the button for that row has a backcolour of red if not green.
I have the following code but I am a little stuck as it is not working
Code:
Dim i As Integer = 1
Do While i < DataGridView1.RowCount
con.Open()
Dim con2 As SqlConnection = New SqlConnection("Data Source=servername;Initial Catalog=Databasename;Integrated Security=True")
Dim cmd3 As SqlCommand = New SqlCommand("select * from register where staff_id = '" & Me.DataGridView1.Rows(i).Cells(2).Value & "' and shift_date = convert(date,'" & txt_saturday.Text & "',103)", con2)
Dim sda3 As SqlDataAdapter = New SqlDataAdapter(cmd3)
Dim dt3 As DataTable = New DataTable()
sda3.Fill(dt3)
con.Close()
If (dt3.Rows.Count > 0) Then
DataGridView1.Rows(i).Cells(0).Style.BackColor = Color.Red
else
DataGridView1.Rows(i).Cells(0).Style.BackColor = Color.Green
End If
i += 1
Loop
hope this makes sense, thank you in advance
Last edited by AMJADJ75; Oct 30th, 2020 at 06:44 PM.
-
Oct 30th, 2020, 10:01 PM
#2
Re: colour for buttons on datagrid to change if sql record exists
Lots to change there. Firstly, you should learn to use the loop most appropriate for the situation. A Do loop is definitely the wrong option here. The only way it could be more wrong is if you used Do Until.
When choosing a loop, For Each should be your first choice. If you have an enumerable list of items and you want to use each one in turn to perform some processing then For Each is the right choice and you're done. If that doesn't work then you should next consider a For loop. If you have some process in which you can use a loop counter through some regular range then For is the right choice and you're done. For instance, let's say that you're using two concurrent arrays so you cannot use a For Each loop. A For loop is the right option because the loop counter can be used to index both arrays. Only if you can't use a For loop should you consider a Do or While loop.
When processing the rows of a DataGridView, a For Each loop is generally the way to go, e.g.
vb.net Code:
For Each row As DataGridViewRow In myDataGridView.Rows
'Use row here.
Next
If you have a data-entry row at the bottom of the grid then you may want to avoid processing that, e.g.
vb.net Code:
For Each row As DataGridViewRow In myDataGridView.Rows
If Not row.IsNewRow Then
'Use row here.
End If
Next
or:
vb.net Code:
For Each row In myDataGridView.Rows.Cast(Of DataGridViewRow)().Where(Function(row) Not row.IsNewRow)
'Use row here.
Next
I notice that you're starting at an index of 1 rather than 0, which means that you're skipping the first row. Not sure why you would do that but, in that case, you might be better off with a For loop, e.g.
vb.net Code:
For i = 1 To myDataGridView.RowCount - 2 Step 1
Dim row = myDataGridView.Rows(i)
'Use row here.
Next
The (- 2) is to allow for the data-entry row at the bottom, so you can use (- 1) if there is no such row. The Step 1 is to make sure that no attempt is made to use an invalid index if there is no row at index 1. Specifying a positive Step value ensures that execution does not enter the loop if the initial value for the loop counter is less than the terminating value.
-
Oct 30th, 2020, 10:08 PM
#3
Re: colour for buttons on datagrid to change if sql record exists
Having said all that, you should not be using the grid at all in this and you should also not be querying the database repeatedly. If you were going to do it this way, you should first gather all the data you need from the DataTable bound to the grid, query the database once and then do the rest of the processing locally. You don't even need to do that though. You could modify the original query to include an extra column that contains the number of related records. You can then colour your buttons based on a value stored within the same bound item, instead of having to go outside for that information. That means no loop at all, as you can just handle the appropriate grid event for the row or cell and format based on the item bound to that row. Show us your original query and I'll show you how to do all this with a single query and grid events.
-
Oct 31st, 2020, 12:14 PM
#4
Thread Starter
Member
Re: colour for buttons on datagrid to change if sql record exists
Hi "JM"
Sometimes we are placed in a situation where we just want to get the job done rather than think of optimal coding and also not everyone aspires to be a developer and fully learn programming concepts. You have a problem in which IT can help and you basically seek to streamline that process using an application. How that is programmed and if it is the optimum code in my situation is mainly irrelevant. It is more about the user experience and getting the job done rather than whats behind the application. Not to say I don't give that any thought, ie minimising the number of forms, presentation etc but as far as coding goes, I do not aspire or wish to develop my career further as a developer and do not have the luxury of a lot of time to learn and then provide a solution, hence why simply looking for a solution and applying it is the method I use.
I am not dismissing what you have wrote, in this case a working example and then the detailed explanation would have been better at my level and am sure a lot of other peoples level who have no understanding of the concepts you put forward.
Not sure if I am going to get the help now ha! but below is the code you requested. Basically for me, I need a list of users on a particular shift and a mechanism for the user to select a person and "add" them to this weeks Saturday or sunday shift. The button colours was just to show the user they have already added them to the required shift (I can do this without the button colouring, but wanted a little ease for the user). Hnece the use of a datagrid and then the first two columns being buttons (one for sat and another for sunday)
Code:
con.Open()
cmd = con.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select staff_id, forename, surname, company from vW_Shopfloor_ID2Names where value_stream = '" & A1_MainMenu.cmbo_value_stream.Text & "' and shift = '" & A1_MainMenu.cmbo_shift.Text & "' ORDER BY Forename, ID_VAR"
Dim rdr As SqlDataReader = cmd.ExecuteReader
Dim dt As New DataTable
dt.Load(rdr)
DataGridView1.DataSource = dt
con.Close()
Dim i As Integer = 1
Do While i < DataGridView1.RowCount
con.Open()
Dim con2 As SqlConnection = New SqlConnection("Data Source=servername;Initial Catalog=databasename;Integrated Security=True")
Dim cmd3 As SqlCommand = New SqlCommand("select * from shopfloor_register where staff_id = '" & Me.DataGridView1.Rows(i).Cells(3).Value & "' and shift_date = convert(date,'" & txt_saturday.Text & "',103)", con2)
Dim sda3 As SqlDataAdapter = New SqlDataAdapter(cmd3)
Dim dt3 As DataTable = New DataTable()
sda3.Fill(dt3)
con.Close()
If (dt3.Rows.Count > 0) Then
DataGridView1.Rows(i).Cells(0).Style.BackColor = Color.Red
Else DataGridView1.Rows(i).Cells(0).Style.BackColor = Color.green
End If
i += 1
Loop
sorry if the above riled you a little, this was not my intention, I just wanted to explain the situation I am in and I am sure many others are (please don't shoot me!!)
Thank you for your time.
-
Oct 31st, 2020, 08:28 PM
#5
Re: colour for buttons on datagrid to change if sql record exists
Originally Posted by AMJADJ75
Hi "JM"
Sometimes we are placed in a situation where we just want to get the job done rather than think of optimal coding and also not everyone aspires to be a developer and fully learn programming concepts. You have a problem in which IT can help and you basically seek to streamline that process using an application. How that is programmed and if it is the optimum code in my situation is mainly irrelevant. It is more about the user experience and getting the job done rather than whats behind the application. Not to say I don't give that any thought, ie minimising the number of forms, presentation etc but as far as coding goes, I do not aspire or wish to develop my career further as a developer and do not have the luxury of a lot of time to learn and then provide a solution, hence why simply looking for a solution and applying it is the method I use.
I am not dismissing what you have wrote, in this case a working example and then the detailed explanation would have been better at my level and am sure a lot of other peoples level who have no understanding of the concepts you put forward.
Not sure if I am going to get the help now ha! but below is the code you requested. Basically for me, I need a list of users on a particular shift and a mechanism for the user to select a person and "add" them to this weeks Saturday or sunday shift. The button colours was just to show the user they have already added them to the required shift (I can do this without the button colouring, but wanted a little ease for the user). Hnece the use of a datagrid and then the first two columns being buttons (one for sat and another for sunday)
Code:
con.Open()
cmd = con.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select staff_id, forename, surname, company from vW_Shopfloor_ID2Names where value_stream = '" & A1_MainMenu.cmbo_value_stream.Text & "' and shift = '" & A1_MainMenu.cmbo_shift.Text & "' ORDER BY Forename, ID_VAR"
Dim rdr As SqlDataReader = cmd.ExecuteReader
Dim dt As New DataTable
dt.Load(rdr)
DataGridView1.DataSource = dt
con.Close()
Dim i As Integer = 1
Do While i < DataGridView1.RowCount
con.Open()
Dim con2 As SqlConnection = New SqlConnection("Data Source=servername;Initial Catalog=databasename;Integrated Security=True")
Dim cmd3 As SqlCommand = New SqlCommand("select * from shopfloor_register where staff_id = '" & Me.DataGridView1.Rows(i).Cells(3).Value & "' and shift_date = convert(date,'" & txt_saturday.Text & "',103)", con2)
Dim sda3 As SqlDataAdapter = New SqlDataAdapter(cmd3)
Dim dt3 As DataTable = New DataTable()
sda3.Fill(dt3)
con.Close()
If (dt3.Rows.Count > 0) Then
DataGridView1.Rows(i).Cells(0).Style.BackColor = Color.Red
Else DataGridView1.Rows(i).Cells(0).Style.BackColor = Color.green
End If
i += 1
Loop
sorry if the above riled you a little, this was not my intention, I just wanted to explain the situation I am in and I am sure many others are (please don't shoot me!!)
Thank you for your time.
I’m not going to attempt to read that in depth, but I can guarantee you that is not the optimal method or anywhere vaguely near the optimal method...
You’ll probably be able to do a lot of the work in your SQL Statement
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Oct 31st, 2020, 11:12 PM
#6
Re: colour for buttons on datagrid to change if sql record exists
If you want to change the color of the buttons, perhaps you should be changing the color of the buttons rather than the back color of the cell.
-tg
-
Oct 31st, 2020, 11:55 PM
#7
Re: colour for buttons on datagrid to change if sql record exists
Originally Posted by techgnome
If you want to change the color of the buttons, perhaps you should be changing the color of the buttons rather than the back color of the cell.
-tg
There are no Button controls though. A DataGridViewButtonColumn contains no Button controls. It just contains a rendering of a Button in each cell.
-
Nov 1st, 2020, 12:57 AM
#8
Re: colour for buttons on datagrid to change if sql record exists
It's worth noting that, if you want the BackColor of a DataGridViewButtonCell to show as more than an outline, you have to set the FlatStyle to either Flat or Popup. Standard or System will obscure the cell background behind the Button rendering.
-
Nov 1st, 2020, 02:13 AM
#9
Re: colour for buttons on datagrid to change if sql record exists
@AMJADJ75
everything you need to go threw the rows/cells is in Post#2
perhaps a small sample will make it more clear
load your Data then check the Values
Code:
Public Class Form1
Dim tb As DataTable = New DataTable
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
tb.Columns.Add("ID")
tb.Columns.Add("QtyOut")
tb.Columns.Add("QtyIn")
tb.Columns.Add("Stock")
tb.Columns.Add("Balance")
tb.Columns.Add("someText")
'start Balance = 400
tb.Rows.Add("1", "20", "230", "400", "455", "Order some")
tb.Rows.Add("2", "6", "210", "300", "32", "in House")
tb.Rows.Add("3", "5", "220", "25", "12", "")
tb.Rows.Add("4", "4", "126", "1233", "800", "Order some")
DataGridView1.DataSource = tb
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
ApplyFormatting1()
ApplyFormatting2()
End Sub
Private Sub ApplyFormatting1()
For Each row As DataGridViewRow In DataGridView1.Rows
'check the Value in Cell 5
Select Case CStr(row.Cells(5).Value)
Case Is = "in House"
row.Cells(5).Style.BackColor = Color.Aquamarine
Case Is = "Order some"
row.Cells(5).Style.BackColor = Color.Pink
End Select
Next
End Sub
Private Sub ApplyFormatting2()
For Each row As DataGridViewRow In DataGridView1.Rows
'check the Value in Cell 2
Select Case CInt(row.Cells(2).Value)
Case Is = 220
row.Cells(2).Style.BackColor = Color.Aquamarine
Case Is = 126
row.Cells(2).Style.BackColor = Color.Pink
End Select
Next
End Sub
End Class
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Nov 1st, 2020, 06:00 AM
#10
Thread Starter
Member
Re: colour for buttons on datagrid to change if sql record exists
Originally Posted by .paul.
I’m not going to attempt to read that in depth, but I can guarantee you that is not the optimal method or anywhere vaguely near the optimal method...
You’ll probably be able to do a lot of the work in your SQL Statement
Had you taken the time out to mate, you would have realised that no one said it was the optimal method! pointless replying to something if you haven't read it all!
-
Nov 1st, 2020, 06:03 AM
#11
Thread Starter
Member
Re: colour for buttons on datagrid to change if sql record exists
Hi ChrisE, that's great thank you. What I wasn't doing initially was bringing the data into the datagrid, hence why going through the grid and running a sql query at each row. I will check how I can bring the required data that needs checking into the grid and then run the above code to meet my needs.
Thank you
-
Nov 1st, 2020, 07:17 AM
#12
Thread Starter
Member
Re: colour for buttons on datagrid to change if sql record exists
*** update *** This has now been resolved, for anyone looking at this post in future....
--- I added the fields I needed to check into the sql query which created and populated the datagrid (therefore no need to run a query for each row)
--- Ensured the datagrid button property was set to pop-up (otherwise it just coloured the cell behind the button as per JMChil's advice above)
--- Then used the below query to fill in the buttons as required.
Thank you to chrisE for the code and thanks for JMChil for the advice on the buttons (and apologies for my rant, I hope it was taken in the way it was intended)
Thank you once again guys - much appreciated
Code:
'after populating the datagrid I used this
For Each row As DataGridViewRow In DataGridView1.Rows
'check the Value in Cell 7
Select Case CStr(row.Cells(7).Value)
Case Is = "AVAILABLE"
row.Cells(1).Style.BackColor = Color.Green
row.Cells(1).Style.ForeColor = Color.Green
Case Is = "ADDED"
row.Cells(1).Style.BackColor = Color.Red
row.Cells(1).Style.ForeColor = Color.Red
End Select
Next
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
|