• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 85
  • Last Modified:

Change button color depends on the data from the database

Hello all,

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

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 : Yes or No (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.

thank you,

picture
0
Rabeea Qabaha
Asked:
Rabeea Qabaha
  • 2
  • 2
2 Solutions
 
Bill PrewCommented:
How are the buttons named, does their name have the Shop number in it, or is it just the text on the button itself?

Is there a one to one relationship between the rows in the table of Shops, and the buttons.  Or are there more or less in either?

Did you program this so far, so can we assume that you have a connection to the database, and know how to select the rows from the table involved?


»bp
1
 
Rabeea QabahaAuthor Commented:
Hello @Bill Prew ,

yes the data base connected and the picture above is a ready form,

How are the buttons named, does their name have the Shop number in it, or is it just the text on the button itself?
the button text  and the button name is the same in the database column like : shop_id = 22 there is button called  button22

Is there a one to one relationship between the rows in the table of Shops, and the buttons.  Or are there more or less in either?
yes the relationship is one to one, each row in the table have one button in the form.

Did you program this so far, so can we assume that you have a connection to the database, and know how to select the rows from the table involved?
yes I already done this but its a long way and need a lots of code, so i know that this can be done by for loop but i cant do it .

this is my code, but i have many forms and this is a long way to do it.

 Dim mylist1 As New List(Of Boolean)
    Private Sub sss()

        If SQL.conn.State = ConnectionState.Open Then
            SQL.conn.Close()
        End If
        SQL.conn.Open()
        Dim cmd As New SqlCommand()
        cmd.Connection = SQL.conn
        cmd.CommandText = "select * from shops where floor=@floor and location=@location order by shop_id"
        cmd.Parameters.AddWithValue("floor", "1")
        cmd.Parameters.AddWithValue("location", "G2")
        Dim dr As SqlDataReader = cmd.ExecuteReader
        While dr.Read
            Dim status As Boolean = dr.Item(4)
            mylist1.Add(String.Format(status))
        End While
        dr.Close()
        SQL.conn.Close()

    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        sss()
        MsgBox(mylist1.Item(0))
        MsgBox(mylist1.Item(mylist1.Count - 1))

        For i As Integer = 0 To mylist1.Count - 1
            If mylist1.Item(0) = False Then
                Button21.BackColor = Color.Green
            End If
            If mylist1.Item(1) = False Then
                Button22.BackColor = Color.Green
            End If
            If mylist1.Item(2) = False Then
                Button23.BackColor = Color.Green
            End If
            If mylist1.Item(3) = False Then
                Button24.BackColor = Color.Green
            End If
            If mylist1.Item(4) = False Then
                Button25.BackColor = Color.Green
            End If
            If mylist1.Item(5) = False Then
                Button26.BackColor = Color.Green
            End If
            If mylist1.Item(6) = False Then
                Button27.BackColor = Color.Green
            End If
            If mylist1.Item(7) = False Then
                Button28.BackColor = Color.Green
            End If
            If mylist1.Item(8) = False Then
                Button29.BackColor = Color.Green
            End If
        Next
    End Sub

Open in new window

0
 
Bill PrewCommented:
Not sure your level of expertise.  But the basic Idea would be to loop over the rows of the table, get the flag value, locate the corresponding button, and change it's color.

There are a number of ways to access data in .Net, one approach is detailed here:


As you get each row, get the button number, and the status value to variables.

Then you need to get a reference to the button from the button number.  You can build a reference from a text version of the button name doing something like:

Dim txtButtonName = "button" & shop_id
Dim objButton As Button = TryCast(Me.Controls("txtButtonName"), Button)
If objButton IsNot Nothing Then
    If shop_status = "Yes" Then
        objButton.BackColor = Green
    Else
        objButton.BackColor = Red
    End If
End If

Open in new window


You will need to experiment with that I'm sure, depending on language, etc, but it gives you some idea.


»bp
1
 
it_saigeDeveloperCommented:
Another alternative is to create a list of controls on the form for the buttons, you could then access the button(s) in the list based on your query results to change their colors; e.g. -
Imports System.Runtime.CompilerServices

Public Class Form1
    Private shops As Dictionary(Of Integer, Button) = New Dictionary(Of Integer, Button)

    Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
        For Each [button] In Controls.GetChildren(GetType(Button)).Where(Function(b) b.Name.StartsWith("button", StringComparison.OrdinalIgnoreCase))
            Dim shopNumber = [button].Name.ToUpper().Split(New String() {"BUTTON"}, StringSplitOptions.RemoveEmptyEntries)
            Dim result As Integer = 0
            If shopNumber.Length > 0 AndAlso Integer.TryParse(shopNumber(0), result) Then
                [button].BackColor = Color.Red
                shops.Add(result, [button])
            End If
        Next
    End Sub

    Private Sub sss()
        Dim [button] As Button = Nothing
        If Sql.conn.State = ConnectionState.Open Then
            Sql.conn.Close()
        End If
        Sql.conn.Open()
        Dim cmd As New SqlCommand()
        cmd.Connection = Sql.conn
        cmd.CommandText = "select * from shops where floor=@floor and location=@location order by shop_id"
        cmd.Parameters.AddWithValue("floor", "1")
        cmd.Parameters.AddWithValue("location", "G2")
        Dim dr As SqlDataReader = cmd.ExecuteReader
        While dr.Read
            Dim status As Boolean = dr.Item(4)
            Dim shopNumber As Integer = dr.Item("shop_id")
            If status AndAlso shops.TryGetValue(shopNumber, [button]) Then
                [button].BackColor = Color.Green
            End If
        End While
        dr.Close()
        Sql.conn.Close()

    End Sub
End Class

Module Extensions
    <Extension>
    Public Function GetChildren(source As Control.ControlCollection, Optional ByVal filter As Type = Nothing) As IEnumerable(Of Control)
        Dim children As List(Of Control) = New List(Of Control)
        For Each child As Control In source
            If child.GetType() Is If(filter IsNot Nothing, filter, GetType(Control)) Then children.Add(child)
            If child.HasChildren() Then children.AddRange(child.Controls.GetChildren(filter))
        Next
        Return children
    End Function
End Module

Open in new window


-saige-
0
 
Rabeea QabahaAuthor Commented:
thank you, its work now

the final code is :
[code]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()
[/code]
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now