Results 1 to 12 of 12

Thread: [RESOLVED] colour for buttons on datagrid to change if sql record exists

  1. #1

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

    Resolved [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.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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:
    1. For Each row As DataGridViewRow In myDataGridView.Rows
    2.     'Use row here.
    3. 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:
    1. For Each row As DataGridViewRow In myDataGridView.Rows
    2.     If Not row.IsNewRow Then
    3.         'Use row here.
    4.     End If
    5. Next
    or:
    vb.net Code:
    1. For Each row In myDataGridView.Rows.Cast(Of DataGridViewRow)().Where(Function(row) Not row.IsNewRow)
    2.     'Use row here.
    3. 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:
    1. For i = 1 To myDataGridView.RowCount - 2 Step 1
    2.     Dim row = myDataGridView.Rows(i)
    3.  
    4.     'Use row here.
    5. 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.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  4. #4

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

    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.

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

    Re: colour for buttons on datagrid to change if sql record exists

    Quote Originally Posted by AMJADJ75 View Post
    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

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: colour for buttons on datagrid to change if sql record exists

    Quote Originally Posted by techgnome View Post
    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.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    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.

  10. #10

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

    Re: colour for buttons on datagrid to change if sql record exists

    Quote Originally Posted by .paul. View Post
    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!

  11. #11

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

    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

  12. #12

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

    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
  •  



Click Here to Expand Forum to Full Width