none
Change button color depends on the data from the database RRS feed

  • Question

  • Hello all,

    i want to change the backcolor for buttons depends on information from database,
    i have form has multiple buttons .

    I have a database table for shops (stores),There is a column in this table that contains the shop number and a column for its status : (if rented or empty)

    in the model Each shop has a button and this button named as the number of the store number in the database
    How can I do for loop to Retrieve store data from the database
    and  changes the color of the button depends on the data from the database if it is empty or rented.

    this is what i tried  but i'm getting this error :

    Object reference not set to an instance of an object. ( on change color code ) -> btn.BackColor = Color.LightPink

    this is my code :

    Dim pagingAdapter As SqlDataAdapter
            Dim pagingDS As DataSet
            Dim dt As New DataTable
    
            Dim sqlstring As String = "Select * FROM shops where location='G2'"
    
            If SQL.conn.State = ConnectionState.Open Then
                SQL.conn.Close()
            End If
            SQL.conn.Open()
    
            pagingAdapter = New SqlDataAdapter(sqlstring, SQL.conn)
            pagingDS = New DataSet()
            pagingAdapter.Fill(pagingDS, "shops")
            dt = pagingDS.Tables("shops")
            MsgBox(dt.Rows.Count)
    
            For Each row As DataRow In dt.Rows
                Dim id As Integer = row("shop_id")
                Dim state As String = row("status")
                Dim btn As Button = TryCast(Me.Controls("Button" & id), Button)
    
                If state = "rented" Then
                    btn.BackColor = Color.LightPink
                ElseIf state = "empty" Then
                    btn.BackColor = Color.LightGreen
                End If
            Next
            SQL.conn.Close()


    Friday, November 3, 2017 5:21 PM

Answers

  • Hi

    Here is some code that may help.

    This example uses 3 buttons and a DataGridView (to display data), but your case would need whatever you are doing as far as Buttons are concerned.

    I have also included code to change Button colours on user Check/UnCheck any of the CheckBoxes in the DataGrid\view. Ignore those event handlers if noit appropriate for your case.

    Code

    'Form1 with 3 Buttons (or however many rows
    ' you have in dt)
    'and a blank DataGridView1 for display
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim dt As New DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With dt
                .Columns.Add("Shop ID", GetType(Integer))
                .Columns.Add("Shop Name", GetType(String))
                .Columns.Add("Status", GetType(Boolean))
    
                .Rows.Add(1, "High Street", True)
                .Rows.Add(2, "Low Street", True)
                .Rows.Add(3, "Middle Street", False)
    
            End With
            DataGridView1.DataSource = dt
    
            SetButtons()
        End Sub
        Sub SetButtons()
            For Each row As DataRow In dt.Rows
                Dim state As String = row("status").ToString
                Dim btn As Button = TryCast(Controls("Button" & row.Item("Shop ID").ToString), Button)
                If state = "True" Then
                    btn.BackColor = Color.Green
                Else
                    btn.BackColor = Color.Red
                End If
            Next
        End Sub
        ' these two event handlers only if you want
        ' the buttons toupdate on checkbox changed
        Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
            DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
        End Sub
        Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
            SetButtons()
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    • Marked as answer by Rabeea Qabha Friday, November 3, 2017 7:59 PM
    Friday, November 3, 2017 6:24 PM

All replies

  • Hi

    Here is some code that may help.

    This example uses 3 buttons and a DataGridView (to display data), but your case would need whatever you are doing as far as Buttons are concerned.

    I have also included code to change Button colours on user Check/UnCheck any of the CheckBoxes in the DataGrid\view. Ignore those event handlers if noit appropriate for your case.

    Code

    'Form1 with 3 Buttons (or however many rows
    ' you have in dt)
    'and a blank DataGridView1 for display
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim dt As New DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With dt
                .Columns.Add("Shop ID", GetType(Integer))
                .Columns.Add("Shop Name", GetType(String))
                .Columns.Add("Status", GetType(Boolean))
    
                .Rows.Add(1, "High Street", True)
                .Rows.Add(2, "Low Street", True)
                .Rows.Add(3, "Middle Street", False)
    
            End With
            DataGridView1.DataSource = dt
    
            SetButtons()
        End Sub
        Sub SetButtons()
            For Each row As DataRow In dt.Rows
                Dim state As String = row("status").ToString
                Dim btn As Button = TryCast(Controls("Button" & row.Item("Shop ID").ToString), Button)
                If state = "True" Then
                    btn.BackColor = Color.Green
                Else
                    btn.BackColor = Color.Red
                End If
            Next
        End Sub
        ' these two event handlers only if you want
        ' the buttons toupdate on checkbox changed
        Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
            DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
        End Sub
        Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
            SetButtons()
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    • Marked as answer by Rabeea Qabha Friday, November 3, 2017 7:59 PM
    Friday, November 3, 2017 6:24 PM
  • Thank you man, you saved me.

    its work now thank you.

    Dim pagingAdapter As SqlDataAdapter
            Dim pagingDS As DataSet
            Dim dt As New DataTable
    
            Dim sqlstring As String = "Select * FROM shops where location=N'مجمع 02'"
            'فحص الاتصال بقاعدة البيانات
            If SQL.conn.State = ConnectionState.Open Then
                SQL.conn.Close()
            End If
            SQL.conn.Open()
    
            pagingAdapter = New SqlDataAdapter(sqlstring, SQL.conn)
            pagingDS = New DataSet()
            pagingAdapter.Fill(pagingDS, "shops")
            dt = pagingDS.Tables("shops")
            MsgBox(dt.Rows.Count)
            Try
                For Each row As DataRow In dt.Rows
                    Dim state As String = row("status").ToString
                    Dim btn As Button = TryCast(Panel1.Controls("Button" & row.Item("Shop_id").ToString), Button)
                    If state = "فارغ" Then
                        btn.BackColor = Color.Green
                    Else
                        btn.BackColor = Color.Red
                    End If
                Next
            Catch ex As Exception
    
            End Try
    
            SQL.conn.Close()
    

    Friday, November 3, 2017 7:59 PM