[RESOLVED] VB.Net - MS Access - Check Boxes

Hi - a question for you all for my own knowledge and learning.

I have a form which sends and retrieves data to/from an Access database. The form comprises a couple of Check Boxes, among other things. I found that when saving/retrieving data from the DB, i received the following error:

'An unhandled exception of type 'System.ComponentModel.InvalidEnumArgumentException' occurred in System.Windows.Forms.dll

Additional information: The value of argument 'value' (-1) is invalid for Enum type 'CheckState'.'

The offending code:

Code:

---

Imports System.Data.OleDb   
  Private Sub btnRetrieveData_Click(sender As Object, e As EventArgs) Handles btnRetrieveData.Click
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider$ = "Provider=MICROSOFT.ACE.OLEDB.12.0;"
        Dim dbSource$ = "Data Source = C:\Desktop\Access.accdb"
        Dim DS As New DataSet
        Dim DA As New OleDb.OleDbDataAdapter       
        con.ConnectionString = dbProvider & dbSource
        con.Open()
        DA = New OleDbDataAdapter(Select * From tb, con)
        DA.Fill(DS, "FirstTable")
        ckPplFFTD.CheckState = DS.Tables("FirstTable").Rows(inc).Item(5)
        ckPplSEND.CheckState = DS.Tables("FirstTable").Rows(inc).Item(7)
        con.Close()
  End Sub

---

Ok... so... after a bit of head scratching, i figured Access' Check Box integer values were different to VB.Net's Check Box values. So... i changed my code to the following and the form worked:

Code:

---

       
        If DS.Tables("FirstTable").Rows(inc).Item(5) = -1 Then
        ckPplFFTD.CheckState = DS.Tables("FirstTable").Rows(inc).Item(5) + 2
        Else
        ckPplFFTD.CheckState = DS.Tables("FirstTable").Rows(inc).Item(5)
        End If

        If DS.Tables("FirstTable").Rows(inc).Item(7) = -1 Then
        ckPplSEND.CheckState = DS.Tables("FirstTable").Rows(inc).Item(7) + 2
        Else
        ckPplSEND.CheckState = DS.Tables("FirstTable").Rows(inc).Item(7)
        End If

---

My question: Is this the correct way of doing things?? It seems a little clunky and i cant help but think i'm not doing things as they should be done.

Thanks in advance.

GTD

set your table data type to boolean then use the .Value property after your item and set the checked property instead of the check state would be a more conventional method :)

The Access data type to represent a Boolean is `Yes/No`. Yes corresponds to True and No corresponds to False, as you'd expect. In .NET, the CheckBox property that represents a Boolean is Checked, NOT CheckState. I see so many people using CheckState without ever reading anything about it to see what it is really for.

You use CheckState ONLY if you want a tri-state CheckBox, i.e. one that can be checked, not checked or in an indeterminate state. You would use that to represent a nullable Boolean. If you have a yes/No column in Access that supports NULLs then you would use the CheckState, i.e. Yes corresponds to Checked, No corresponds to Unchecked and NULL corresponds to Indeterminate.

So, you should NEVER be assigning values from an Access field directly to the CheckState property. If the field is not nullable then it should contain a Boolean and that should be assigned to the Checked property.

Code:

---

myCheckBox.Checked = CBool(myDataRow("YesNoColumn"))

---

If it is nullable then it should still contain Booleans so you first test whether it does and then which it is and assign the appropriate CheckState value.

Code:

---

myCheckBox.CheckState = If(myDataRow.IsNull("YesNoColumn"),
                          CheckState.Indeterminate,
                          If(CBool(myDataRow("YesNoColumn")),
                              CheckState.Checked,
                              CheckState.Unchecked))

---

Quote:

---

Originally Posted by jmcilhinney

You use CheckState ONLY if you want a tri-state CheckBox, i.e. one that can be checked, not checked or in an indeterminate state. You would use that to represent a nullable Boolean. If you have a yes/No column in Access that supports NULLs then you would use the CheckState, i.e. Yes corresponds to Checked, No corresponds to Unchecked and NULL corresponds to Indeterminate.

---

I am using tri-state... so i'm on the right track -- thanks for responding :).

GTD