asked on

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,

Bill Prew

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

Rabeea Qabaha

ASKER

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

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

it_saige

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rabeea Qabaha

ASKER

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]