How to enable multiple buttons based on sql db data in a cell

Question

My project is coming along nicely thanks in part to all of your help! I have come across another bump in the road. I need to enable (up to 8) buttons based on whether or not a cell in a sql db has a 'y', 'n' or is null. I can get it to work for one button, but not 2 or more. Before I show you my code, let me give a bit more detail: My database stores info about students and grades (an numerous other things, but they don't pertain to this question). Each student must complete all eight(8) classes to graduate. In the db, I have columns labeled for each of the eight classes. If they took the class, it's a 'y' in the row. If they failed the class, it's a 'n'. If they haven't taken it yet or it's in progress, the cell has null when I view it in sql server management studio because they neither completed it nor failed it, so no data is entered yet.

I have eight(8) buttons with labels for each class. When a studentId is entered by the user and the db searched, I want the buttons enabled based on the database values. I got one to work, but that's it and I can't figure out why. Would greatly appreciate someone pointing out what is wrong in my code and point me in the correct direction.

    Try
        Using connection As New SqlConnection("Data Source=?;Initial Catalog=?;Persist Security Info=True;User ID=?;Password=?")
            connection.Open()
            Dim dt As New DataTable
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter
            ds.Tables.Add(dt)
            da = New SqlDataAdapter("select g1, g2, af1, af2, af3, pp1, pp2, pp3 from student_info where studentId = '" & stunumtxtbox.Text & "'", connection)
            Dim count = da.Fill(dt)
            If count = 1 Then
                G1Button.Enabled = True
            End If
            If count = 2 Then
                G2Button.Enabled = True
            End If
            If count = 3 Then
                AF1Button.Enabled = True
            End If
            If count = 4 Then
                AF2Button.Enabled = True
            End If
            If count = 5 Then
                AF3Button.Enabled = True
            End If
            If count = 6 Then
                PP1Button.Enabled = True
            End If
            If count = 7 Then
                PP2Button.Enabled = True
            End If
            If count = 8 Then
                PP3Button.Enabled = True
            End If
        End Using
    Catch se As SqlException
        MsgBox(se.Message)
    Finally
    End Try

Thank you in advance!

`1.` What does this mean? "buttons based on whether or not a cell in a sql db", the "cell" `2.` Isn't it better to have at least 2 form interfaces? So you may flip them based on the criteria instead of going each control? — bonCodigo, Feb 22 '14 at 09:10
I tried to ask how do i enable multiple buttons (button.enabled = true) by searching a sql database and if the value I want exists in a cell, in a row, then enabled the button or buttons. — , Feb 22 '14 at 09:13
Just taking a simple scenario: sql table has 5 fields each referring to a specific button's enable value. Assuming your student id is the key to validate, and once the validation is done, you may fetch that table sql entirely and `loop` through each of the value until the end. Like how you loop an `array`. Then change your `if` condition to support all buttons. [Read](http://stackoverflow.com/questions/1370543/fill-an-array-or-arraylist-from-sqldatareader) — bonCodigo, Feb 22 '14 at 09:24
instead of evaluating count as an integer, evaluate if the related column (g1, g2...) is not null or whatever the default value is in dt. In selecting them, they will always be present, so trying to use a count of columns (?) wont work. — Ňɏssa Pøngjǣrdenlarp, Feb 22 '14 at 14:02

Bjørn-Roger Kringsjå · Accepted Answer · 2014-02-23T13:14:58.980

You can use LINQ to achieve this. The following code will enable the button if the table contains a row where the specified field/column has the value y or n. If not, the button is disabled.

Dim g1 As List(Of String) = (From r As DataRow In dt Where r.Field(Of String)("g1") = "y" Or r.Field(Of String)("g1") = "n" Select r.Field(Of String)("g1")).ToList()
Dim g2 As List(Of String) = (From r As DataRow In dt Where r.Field(Of String)("g2") = "y" Or r.Field(Of String)("g2") = "n" Select r.Field(Of String)("g2")).ToList()
Dim af1 As List(Of String) = (From r As DataRow In dt Where r.Field(Of String)("af1") = "y" Or r.Field(Of String)("af1") = "n" Select r.Field(Of String)("af1")).ToList()
Dim af2 As List(Of String) = (From r As DataRow In dt Where r.Field(Of String)("af2") = "y" Or r.Field(Of String)("af2") = "n" Select r.Field(Of String)("af2")).ToList()
Dim af3 As List(Of String) = (From r As DataRow In dt Where r.Field(Of String)("af3") = "y" Or r.Field(Of String)("af3") = "n" Select r.Field(Of String)("af3")).ToList()
Dim pp1 As List(Of String) = (From r As DataRow In dt Where r.Field(Of String)("pp1") = "y" Or r.Field(Of String)("pp1") = "n" Select r.Field(Of String)("pp1")).ToList()
Dim pp2 As List(Of String) = (From r As DataRow In dt Where r.Field(Of String)("pp2") = "y" Or r.Field(Of String)("pp2") = "n" Select r.Field(Of String)("pp2")).ToList()
Dim pp3 As List(Of String) = (From r As DataRow In dt Where r.Field(Of String)("pp3") = "y" Or r.Field(Of String)("pp3") = "n" Select r.Field(Of String)("pp3")).ToList()

Me.G1Button.BackColor = If(g1.Contains("y"), Color.Green, If(g1.Contains("n"), Color.Red, SystemColors.Control))
Me.G1Button.Enabled = (g1.Count > 0)

Me.G2Button.BackColor = If(g2.Contains("y"), Color.Green, If(g2.Contains("n"), Color.Red, SystemColors.Control))
Me.G2Button.Enabled = (g2.Count > 0)

Me.AF1Button.BackColor = If(af1.Contains("y"), Color.Green, If(af1.Contains("n"), Color.Red, SystemColors.Control))
Me.AF1Button.Enabled = (af1.Count > 0)

Me.AF2Button.BackColor = If(af2.Contains("y"), Color.Green, If(af2.Contains("n"), Color.Red, SystemColors.Control))
Me.AF2Button.Enabled = (af2.Count > 0)

Me.AF3Button.BackColor = If(af3.Contains("y"), Color.Green, If(af3.Contains("n"), Color.Red, SystemColors.Control))
Me.AF3Button.Enabled = (af3.Count > 0)

Me.PP1Button.BackColor = If(pp1.Contains("y"), Color.Green, If(pp1.Contains("n"), Color.Red, SystemColors.Control))
Me.PP1Button.Enabled = (pp1.Count > 0)

Me.PP2Button.BackColor = If(pp2.Contains("y"), Color.Green, If(pp2.Contains("n"), Color.Red, SystemColors.Control))
Me.PP2Button.Enabled = (pp2.Count > 0)

Me.PP3Button.BackColor = If(pp3.Contains("y"), Color.Green, If(pp3.Contains("n"), Color.Red, SystemColors.Control))
Me.PP3Button.Enabled = (pp3.Count > 0)

I don't want to enable the buttons based on whether or not a value is in the cell, I want to enabled based on the specific value in the cell - for example, if they completed G1, G2 and AF1 classes, but not the others, I only want G1, G2 and AF1 buttons to be enabled. The cell could have 'y' or 'n' or be _null_. — , Feb 22 '14 at 18:31
@ Bjorn, But how do I account for specifics? For instance, the code you provided does work, but it's only checking that the field isn't null. I need to also check to see if the field has a 'y' in it or a 'n' in it. How would I account for that? I want to enable the buttons if the field is not null AND if it has a 'y' then i'm going to have the button color changed to green and if it has an 'n' then change the button to enable still, but also the color to red. — , Feb 22 '14 at 21:08

How to enable multiple buttons based on sql db data in a cell

1 Answers1