Invalid Cast Exception, or bit versus boolean

[gwboolean]

I recently changed from using an access database to using a SQL database (applause please) and have consequently been working at changing my code to fit the requirements for the SQL database.

I am using the code below to load the data to a form. although the control names and table column names are different this is identical to what I was successfully using with the Access database. However, I get the error illustrated below with the Boolean columns using the SQL database I am now using.

I am still going through the literature, but have not yet been able to find what I am looking for. I am aware that SQL bit datatype is equivalent to the Access Boolean datatype but the SQL values are -1, 0, 1 (3 state) instead of Access values of 0, 1 (two state). I have tested the three states and have found that the error is thrown only when the value is 0 (Null).

I know that I could always go back and make sure that the bit fields are not null, but I would prefer to be able to use all three states. So, how do I make my code for the bit fields work for all three states?

        Public Sub LoadData()
            With frmProcess
                .lblProcessID.DataBindings.Add(New Binding("Text", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.intProcessID"))
                .lblChangeID.DataBindings.Add(New Binding("Text", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.intChangeID"))
                .lblRevision.DataBindings.Add(New Binding("Text", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.chrRevision"))
                .txtTitle.DataBindings.Add(New Binding("Text", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.chrTitle"))
                .txtDescription.DataBindings.Add(New Binding("Text", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.chrDescription"))
                .txtProcessType.DataBindings.Add(New Binding("Text", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.chrProcessType"))
                .txtOwner.DataBindings.Add(New Binding("Text", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.chrOwner"))
                .txtWhere.DataBindings.Add(New Binding("Text", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.chrWhere"))
                .chkQualify.DataBindings.Add(New Binding("Checked", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.blnQualify"))
                .radObsolete.DataBindings.Add(New Binding("Checked", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.blnObsolete"))
                .radActive.DataBindings.Add(New Binding("Checked", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.blnActive"))
                .radEffective.DataBindings.Add(New Binding("Checked", frmProcess._MasterBase_1_0DataSet, "tblProcessMaster.blnEffective"))
            End With
        End Sub

[gwboolean]

Sorry about the bad image. But I think anyone can see the issue without me providing a better image.

[jmcilhinney]

I am aware that SQL bit datatype is equivalent to the Access Boolean datatype but the SQL values are -1, 0, 1 (3 state) instead of Access values of 0, 1 (two state). I have tested the three states and have found that the error is thrown only when the value is 0 (Null).

That's not right. For a SQL Server bit column, 1 corresponds to True and 0 corresponds to False. NULL is represented in exactly the same way as it is for any other data type, which means NULL as a literal in SQL code and DBNull.Value in ADO.NET code. That's basically exactly the same as an Access Yes/No column. I'm not sure where you got those numerical values from but they don't correspond to anything I'm aware of, including the TriState and CheckState enumerations.

The SQL Server data type isn't really relevant because once you're in VB you're using VB types, so you're using Booleans. If you want to bind Boolean data to a CheckBox then you can only bind to the Checked property if the data is not nullable. That's because the Checked property is type Boolean so it can only be True or False. There is no value that can represent NULL. I've never actually tried it myself but I believe that, if the data is nullable, you need to set the ThreeState property of the control to True and bind to the CheckState property. CheckState has three possible values: Checked, Unchecked and Indeterminate, which should correspond to True, False and NULL respectively.

[jmcilhinney]

OK, just tested and you cannot bind a Boolean column directly to the CheckState property. It probably requires handling events of the Binding, which I am testing now.

[jmcilhinney]

I just tested handling the Format and Parse events of the relevant binding and it worked as expected. Format is raised when data is pushed from the data source to the control and Parse is raised when data is pushed from the control to the data source. I created a new WinForms project and add a ComboBox, a CheckBox, three RadioButtons and a Button. I then added the following code:

Public Class Form1
 
    Private table As New DataTable
    Private WithEvents NullableBooleanBinding As Binding
 
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        With table.Columns
            .Add("Id", GetType(Integer))
            .Add("Name", GetType(String))
            .Add("NullableFlag", GetType(Boolean))
            .Add("First", GetType(Boolean))
            .Add("Second", GetType(Boolean))
            .Add("Third", GetType(Boolean))
        End With
 
        With table.Rows
            .Add(1, "Peter", True, True, False, False)
            .Add(2, "Paul", False, False, True, False)
            .Add(3, "Mary", DBNull.Value, False, False, True)
        End With
 
        With ComboBox1
            .ValueMember = "Id"
            .DisplayMember = "Name"
            .DataSource = table
        End With
 
        NullableBooleanBinding = New Binding("CheckState", table, "NullableFlag")
        CheckBox1.DataBindings.Add(NullableBooleanBinding)
        RadioButton1.DataBindings.Add("Checked", table, "First")
        RadioButton2.DataBindings.Add("Checked", table, "Second")
        RadioButton3.DataBindings.Add("Checked", table, "Third")
    End Sub
 
    Private Sub NullableBooleanBinding_Format(sender As Object, e As ConvertEventArgs) Handles NullableBooleanBinding.Format
        'Convert from a nullable Boolean to a CheckState.
        If e.Value Is DBNull.Value Then
            e.Value = CheckState.Indeterminate
        ElseIf CBool(e.Value) Then
            e.Value = CheckState.Checked
        Else
            e.Value = CheckState.Unchecked
        End If
    End Sub
 
    Private Sub NullableBooleanBinding_Parse(sender As Object, e As ConvertEventArgs) Handles NullableBooleanBinding.Parse
        'Convert from a CheckState to a nullable Boolean.
        Select Case DirectCast(e.Value, CheckState)
            Case CheckState.Checked
                e.Value = True
            Case CheckState.Unchecked
                e.Value = False
            Case CheckState.Indeterminate
                e.Value = DBNull.Value
        End Select
    End Sub
 
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        For Each row As DataRow In table.Rows
            MessageBox.Show(String.Join(Environment.NewLine, row.ItemArray))
        Next
    End Sub
 
End Class

The data displayed correctly and I was able to edit and click the Button and the correct changes were displayed.

[gwboolean]

The material that I read, from a couple of sources described the SQL bit datatype as -1, 0, 1. You can parse it anyway you want, but that is exactly the same thing (equal, not equivalent) to the CheckState you describe. Of course that has nothing to do with the issue.

So it appears that you are telling me that in VB, when using a CheckBox (and I assume it applies to a Radio button as well) is that it will handle only true and false. That makes sense and fits what I have already observed. If that is the case, and based on the outcome of your test, would not the best thing to do would be to just default a bit datatype field to True or False and eliminate the issue of a Null value?

[gwboolean]

Or, I could try what you just posted. That makes sense.

[jmcilhinney]

So it appears that you are telling me that in VB, when using a CheckBox (and I assume it applies to a Radio button as well) is that it will handle only true and false.

No, that's not what I'm telling you. A CheckBox control can handle two states or three. If you want to handle two, which is the most common, then you set the ThreeState property to False, which is the default, and then use the Checked property to represent your value. The Checked property can be either True or False. If you want to handle three then you set the ThreeState property to True and you use the CheckState property to represent your value. The CheckState property can be either Checked, Unchecked or Indeterminate.

The same does not apply to a RadioButton as an indeterminate state would make no sense in that case. RadioButtons are supposed to be used to select one option from a number of options. That means that any one option is either selected or not selected at any particular time. There can be no indeterminate state in that case. RadioButtons are really just a different representation of the same thing as a ComboBox is used for. Obviously you always know whether or not an item is selected in a ComboBox so the same is true of RadioButtons.

[jmcilhinney]

The material that I read, from a couple of sources described the SQL bit datatype as -1, 0, 1.

I seriously have no idea what you have been reading but here's the official documentation:

https://docs.microsoft.com/en-us/sql...t-transact-sql

It does say that that applies to SQL Server 2008 or later so I don't know if things were different prior to that but I wouldn't have thought so.

[gwboolean]

Last thing first. Not going to bother arguing over -1, 0, 1 and whether the information I read is correct or incorrect. I don't care and it isn't relevant to the issue at hand.

OK, I now get what you are saying about the difference between a text box and a Radiobutton. So the Checkbox can handle three states (actually, I went in and already tested that), but the Radiobutton cannot, (which also matches what I tested).

The bottom line, as I understanding it from the tests I have run and looking at the two databases and your input, is that if I was still using my Access database and did not check the Boolean datatype then the default is then false and that is how it would be read by the CheckBox or Radiobutton controls. When using the SQL database, the default value in a bit datatype is Null and while the CheckBox properties can be altered to work with that the Radiobutton cannot.

It seems to me that the best, or at least most efficient and reliable approach to dealing with this is at the time of creating a new row in a table to just set any bit datatypes to False or True.

This brings me to an unrelated question. I learned the hard way that setting properties for datatypes in Access are not recognized in VB. Is this also true for SQL?

[jmcilhinney]

It seems to me that you have confused yourself with how data is stored by each database.

http://www.ascassociates.biz/boolean...to-sql-server/

It doesn't really matter though because you're not using Access. Access is an application for managing Jet databases (MDB files) and the newer Access format (ACCDB files). You're not using Access any more than you're using SQL Server Management Studio. Your app is the one that's providing the UI and you just need to treat the data as True, False and DBNull.value because that's what ADO.NET uses. What happens under the hood isn't really something that you should need to worry about.

When you design your database, you should design it appropriately for the data you want to store. How you will build a UI to display it is something that you should worry about later. Regardless of the data type for a column, it should be nullable if it makes sense for a record to have no value in that column and it should not be nullable if it doesn't make sense for a field in that column to not have a value. That should be your only consideration. For instance, if you had a Person table and a 'bit' column that only applied to one sex then that column should be nullable and you'd use NULL in records for the other sex.

ADO.NET provides the properties that ADO.NET provides. If you want to know what those properties are, read about ADO.NET. With regards to Access, a number of the options you can set for a column are about how Access treats that data, e.g. the format used when displaying a number as text. There's nothing in ADO.NET about how numbers should be formatted for display and nor should there be. That's a decision for the application doing the displaying. SQL Server is more of a "proper" database than Access so it doesn't really go in for that. Even if you're using SSMS, it's up to you to format the data if you want it formatted.

[gwboolean]

Quite possible JM, I have, on occasion, have misinterpreted things before, as has everyone else.

I do understand about Access and it's role. However, the system is Access, just as SQL is the system. How could you misinterpret my reference to the systems as indicating that the systems are the actual databases? You spend way too much time on meaningless and incorrect details JM.

You are however correct about what happens under the hood, but I am interested in what happens under the hood and not understanding that can have unintended consequences at some point in time. Your advice about database design is noted and will be followed.

By the way, thanks for the link, that was very good.

[Shaggy Hiker]

But what JM was saying is correct, and a common misconception. Access is NOT the system. Access isn't even necessary to create or use an mdb database. You don't need to even have Access on the system, which shows that Access can't very well be the system. Access is often thought to be the database, because people frequently say "an Access database". Heck, I say it, too. That doesn't make it correct, it's just a common shorthand convention. Access is just a tool for displaying and manipulating certain types of databases. You can display and manipulate those types of databases without Access, and there are good reasons to do so, but Access sure is convenient.

So, what you were seeing was the way that Access was taking the data from the database and displaying it. If you didn't use Access, then you wouldn't have seen the data the way that Access chose to display it. You could, in fact, display it some other way of your own choosing. That's what trips people up, because they think that Access is showing an honest representation of the underlying data. It isn't. It's manipulating the underlying data to show it in a way that is consistent within Access, but nothing more than that. So, saying that Access shows it as X, doesn't mean that the data IS X. If you want to show it as Access does, then you can do that, but in this case the problem is that you do NOT want to show it as Access does. In fact, you want to represent the data in some controls following a specific criteria...which I can't say I'm completely clear on, because I'm not sure what you would find ideal. If you wanted to turn True/False/Null into True/False, then you can certainly do that in the SQL such that your query returned those two values only, or you could set a default in the database and make the column not nullable, which would also result in a binary choice rather than a trinary choice, but it isn't clear that you actually want just a binary choice. If you do, then you likely DO want to change the DB design, as that is going to be a better solution, ultimately, than turning a trinary into a binary in the query.

[gwboolean]

There is no misconception. The misconception lies in the usage of the word system. When you are discussing, say an mdb database you are discussing a process (or possibly a component), not a system. When you are discussing Access, you are discussing a system, not a process. System is just an umbrella term that describes process(es) and all of the associated elements and components that might be related to and makes up the system. There is much more to it than that, but that is enough for this irrelevant argument we are having.

Access is indeed a system. It consists of a specific type of database (a process), probably some other processes, components and/or elements. I will concede that it is not technically correct to say Access when one is referring to the database, but since Access is associated with only a single database type (at least as far as I know), then the usage should be perfectly acceptable. OK, enough of my high horse on terminology. If one is not careful terminology turns into jargonism and all of us are coming very close to crossing that line.

OK, so let me explain what I see and what I understand (maybe). In the database used by the Access system I have a set of fields, several of which are of datatype Boolean. There are only two states for this datatype, 0/1, or False/True. In the database used by the SQL system, instead of the datatype Boolean there is the datatype bit, which incorporates -1/0/1 or False/undetermined/True.

So what occurred is that I have begun using the database resident to the SQL system instead of the database resident to the Access system. In my code, as seen way up near the top, I used a line of code that because of the tristate nature of a bit datatype (trinary choice), as apposed to the bi-state nature of Boolean datatype (binary choice) I previously used, threw an exception when the column value was Null.

I went back and looked at the design features of the Access system database and noticed that it's Boolean datatype defaults to False, while the SQL system database bit datatype defaults to Null (as I understand it). I do not believe this is entirely correct, as I do not believe it works quite that way. My belief is that in the dataset, when a new record is added with no data in the record, what one gets is False as the default using an Access system database and Null when using an SQL system database.

It was readily apparent to me that there are two ways to deal with this issue. One would be to ensure that any bit datatype, when a record is created, will either contain True or False, instead of Null. The other would be to adjust the code, or the properties of the control (in this case a textbox and radiobutton) to be able to handle a Null value without throwing an exception. The first I can easily do, and already have, the other I have some idea after the input provided by JM, but am not entirely clear on the process.

After looking at both approaches it seems to me that the first approach provides the most efficient and reliable means of addressing the issue. But on that I could be entirely wrong. There is value to be had from having the capability of dealing with a trinary choice, instead of just a binary choice, but for what I am doing I do not at this time know what that would be.

Back to what you are saying. I understand that as all I would need to do to ensure that the bit values are binary instead of trinary, i.e. that a new record would not be Null, would be to go back to the database design and uncheck the Allow Nulls property? Is that correct?

[jmcilhinney]

How could you misinterpret my reference to the systems as indicating that the systems are the actual databases? You spend way too much time on meaningless and incorrect details JM.

We only know what you tell us. Given that there have been plenty of occasions where people don't know the difference, how could you expect us to be able to read your mind? There have been occasions in the past where providing the explanation I did here early on could have saved everyone plenty of time so, on balance, I'd rather do that unnecessarily than not do it when it might be necessary.

It was readily apparent to me that there are two ways to deal with this issue. One would be to ensure that any bit datatype, when a record is created, will either contain True or False, instead of Null. The other would be to adjust the code, or the properties of the control (in this case a textbox and radiobutton) to be able to handle a Null value without throwing an exception. The first I can easily do, and already have, the other I have some idea after the input provided by JM, but am not entirely clear on the process.

After looking at both approaches it seems to me that the first approach provides the most efficient and reliable means of addressing the issue. But on that I could be entirely wrong. There is value to be had from having the capability of dealing with a trinary choice, instead of just a binary choice, but for what I am doing I do not at this time know what that would be.

It has - or, at least, it should have - nothing to do with efficiency and reliability. The sole consideration should be whether it is appropriate or not for that field to contain no value.

I understand that as all I would need to do to ensure that the bit values are binary instead of trinary, i.e. that a new record would not be Null, would be to go back to the database design and uncheck the Allow Nulls property? Is that correct?

That is all you have to do to ensure that the database will not accept NULL values. That won't stop your application trying to save NULL values though. You would still have to write your code to ensure that that field always had a value. If you were to use a CheckBox with its ThreeState property set to False as the only means to set that field then that would take care of that.

[gwboolean]

We only know what you tell us. Given that there have been plenty of occasions where people don't know the difference, how could you expect us to be able to read your mind?

Sigh.

Perhaps you should learn what a system is as apposed to a component of a system and/or what a process is and how it fits into a system JM. Then you would never be confused. You know, if we stuck to the problem at hand and stayed away from lectures on esoteric crap, much of which you do not know or properly understand, this would all go better for all of us.

It has - or, at least, it should have - nothing to do with efficiency and reliability. The sole consideration should be whether it is appropriate or not for that field to contain no value.

Yes JM, it does have to do with efficiency and reliability. Everything that is ever designed for use (including software) absolutely HAS to consider efficiency and reliability. Consideration of appropriateness is merely one of many considerations. A given in any user requirements ever devised is universally understood to have efficiency and reliability as requirements.

That is all you have to do to ensure that the database will not accept NULL values. That won't stop your application trying to save NULL values though. You would still have to write your code to ensure that that field always had a value. If you were to use a CheckBox with its ThreeState property set to False as the only means to set that field then that would take care of that.

Yes JM, that is what I thought and said. However, thank you for finally clearly stating about the ThreeState property of the CheckBox. From your previous inputs it was hard to get that with all of the confused lecture that surrounded it.

[jmcilhinney]

Yes JM, that is what I thought and said.

You seem to think that you said a number of things that you didn't actually say.

However, thank you for finally clearly stating about the ThreeState property of the CheckBox. From your previous inputs it was hard to get that with all of the confused lecture that surrounded it.

And clearly it was hard to use the Help menu in VS to learn about it for yourself too. How is it that I'm chastised for not properly explaining a simple property and yet I'm supposed to inherently know what you do and don't know about things that aren't so mind-numbingly basic? I'm done with this.

[gwboolean]

You seem to think that you said a number of things that you didn't actually say.

That is possible JM. And you have a propensity to lecture others on a number of things you know either nothing or very little about. Are we done trading insults now?

And clearly it was hard to use the Help menu in VS to learn about it for yourself too. How is it that I'm chastised for not properly explaining a simple property and yet I'm supposed to inherently know what you do and don't know about things that aren't so mind-numbingly basic?

And clearly you have no desire to actually assist someone, just to lecture them and play games. No one chastised you for not properly explaining a simple property, you were being chastised for creeching and lecturing about things that have no relevance or meaning to the subject at hand. If you don't like to be chastised then stick to what you know about and desist with lectures.

No, you are not expected to inherently know what I know or don't know about things that are mind numbingly basic. However, asking for clarification might be a better approach to find out then lecturing and misinforming about things that are mind numbingly basic.

[quote]

I'm done with this.

[/quote

Thanks you.

[Shaggy Hiker]

Perhaps you should learn what a system is as apposed to a component of a system and/or what a process is and how it fits into a system JM. Then you would never be confused.

That's YOUR definition. In the decades I've been working with both types of databases, I've never heard it described as you have now described it. This is like Humpty Dumpty from Alice in Wonderland: "When I use a word, it means what I choose it to mean, neither more nor less." But you're going to have to share that meaning with us if you expect us to share that definition.

Anyways, SQL isn't a system by your definition either. Technically, it's the query language, but as it appears that you mean the SQL Server database, then it is the database itself. The analog to Access for the SQL Server DB would be SQL Server Management Studio. The analog in Access for what you are calling SQL would be the JET database or the accdb databate (which is probably the wrong name). So, even if we accept your definition you are thinking about the rolls a bit wrong.

That 1, 0, -1 is a puzzle to me, too, because that's not what the SQL Server DB holds in a bit field. However, if you want to set a default for a field such that it acts similar to Access, then you can do that in the field definition. That way, if the code doesn't supply any value for the field, it will get the default, whatever you set the default to be.

I was also suggesting a third approach for populating the controls. Even if a returned field is Null, you can set it to something else in the query, so your query can return True/False even if the value is True/False/Null. There are a couple ways to do this in t-SQL (the flavor of SQL used in SQL Server, and slightly different from the flavor used in Access). One option is the IFNULL() method, as seen in this link:

https://www.w3schools.com/sql/sql_isnull.asp

A more versatile method would be the CASE WHEN statement. Oddly, I didn't find quite the same reference for that one, but this shows the use of the CASE statement:

https://stackoverflow.com/questions/...cify-when-null

[gwboolean]

Not my definition Shag. That is and has been an accepted engineering definition for a very, very long time. It is and has been applied across most scientific and engineering disciplines for at least decades. I am not a historian, but I do know my business and what was my profession. Now I admit my description might not be the best, as I am not the most articulate person in the world. But I can assure that process and systems have very definite meanings and usage across every science and engineering profession I know of.

I do not know enough about SQL to have any idea what it would fit into, I was merely making an assumption. However, I was actually referring to SQL Server which probably is a system, but again I do not know enough about it to say for sure. I would assume that the SQL database is not stand alone i.e. it does not exist by itself without a means to manage it and interact with it. But again, I could be wrong about that. I do know enough about Access to state with a surety that Access is a system that has a component database that it is used to manage and operate. However, I am willing to concede that it could be the other way around and the database might be the system with the Access being the component, or in the case of the SQL database, SQL server might be the component. This is a relatively new area for me and I have not, and probably will not, go deep enough to see how all of the parts work together to determine what is a component, element and/or process and exactly what the relationships are that constitute a system. It is quite possible that none of these elements actually even constitute a system. But at any rate, you might want to look up the definition of system and go from there. The other term is process, but I don't even want to go there.

One thing I know for a certainty is what should have been nothing more than a rather simple question, that was obviously not particularly clear, and could have been cleared up with a few questions to get to the meaning of the questions (if not understood) and finally a response as what was the proper approach and usage. Instead, it was turned into a lecture about terminology, and finally degenerated into a discussion of irrelevant details and recriminations. I am certainly sorry for the part I played in that.

That 1, 0, -1 thing was what I recent read in some material I was Perusing while trying to understand the structure and meaning of the database and how it works. I admit that much of what I was reading was off the internet and might well have been subject to error. I do know of the existence of the tristate and accepted that without question. Certainly, if the database is capable of three different values (Null would count as a value) then it would most assuredly be tristate. But again, I can only go by what I have so far read and that has not been very much.

This whole thing started because I was interested in the possibility of working and using what I thought was a tristate system (misuse of the word, but what the hell). While I actually have no usage for that I thought it worthwhile to explore some possibilities. Otherwise I would have just Set a True or False value to the field when I created a new row.

I will most certainly check out the information you provided from 3shools (I have used them before for other things). That sounds like a winner. I especially want to look up the Case When statement. I had not even though of going that way. One frequently gets into a path for an approach and becomes blind to other approaches. Anyway, thanks for the information and especially thanks for no lecture.

[kpmc]

Figured I would try to add something constructive to this nonconstructive conversation.

Ive run into this a while back ago, and the DataBinding method does have a way to handled NULL values as Indeterminate. Ive not tested this but I believe it looks something like this

   CheckBox1.DataBindings.Add("CheckedState", dt, "someBit", True, DataSourceUpdateMode.OnValidation, CheckState.Indeterminate)

[jmcilhinney]

Figured I would try to add something constructive to this nonconstructive conversation.

Ive run into this a while back ago, and the DataBinding method does have a way to handled NULL values as Indeterminate. Ive not tested this but I believe it looks something like this

   CheckBox1.DataBindings.Add("CheckedState", dt, "someBit", True, DataSourceUpdateMode.OnValidation, CheckState.Indeterminate)

That doesn't work. As I said back in post #4, you can't bind a Boolean column to a CheckState property. An InvalidaCastException is thrown. That's why explicit conversions are required for the non-NULL values, as I provided in post #5. It's almost like those posts were constructive.

[kpmc]

That doesn't work. As I said back in post #4, you can't bind a Boolean column to a CheckState property. An InvalidaCastException is thrown. That's why explicit conversions are required for the non-NULL values, as I provided in post #5. It's almost like those posts were constructive.

I dont have a MSSQL Server available right now, but this does work on a TINYINT(1) Type col in MYSQL, and also works on a quick test in Access "YES/NO" type which I thought was a bit type.

Imports System.Data.OleDb
Public Class AccessTest
    Dim dt As New DataTable
    Dim bs As New BindingSource
    Private Sub AccessConn_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                        Data Source=C:\DATA\AccessDB.accdb;")
            Using DA As New OleDbDataAdapter("SELECT * FROM T1", conn)
                DA.Fill(dt)
                bs.DataSource = dt
                DataGridView1.DataSource = bs
                If bs.Count <= 0 Then
                    bs.AddNew()
                End If
                BindingNavigator1.BindingSource = bs
            End Using
        End Using
        TextBox1.DataBindings.Add("text", bs, "test")
        CheckBox1.DataBindings.Add("CheckState", bs, "Bit", True, DataSourceUpdateMode.OnValidation, CheckState.Indeterminate)
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                        Data Source=C:\DATA\AccessDB.accdb;")
            Using DA As New OleDbDataAdapter("SELECT * FROM T1", conn)
                Dim cb As New OleDbCommandBuilder(DA)
                bs.EndEdit()
                cb.QuotePrefix = "["
                cb.QuoteSuffix = "]"
                DA.Update(dt)
                dt.Rows.Clear()
                DA.Fill(dt)
            End Using
        End Using
    End Sub

End Class

[jmcilhinney]

I dont have a MSSQL Server available right now, but this does work on a TINYINT(1) Type col in MYSQL, and also works on a quick test in Access "YES/NO" type which I thought was a bit type.

Imports System.Data.OleDb
Public Class AccessTest
    Dim dt As New DataTable
    Dim bs As New BindingSource
    Private Sub AccessConn_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                        Data Source=C:\DATA\AccessDB.accdb;")
            Using DA As New OleDbDataAdapter("SELECT * FROM T1", conn)
                DA.Fill(dt)
                bs.DataSource = dt
                DataGridView1.DataSource = bs
                If bs.Count <= 0 Then
                    bs.AddNew()
                End If
                BindingNavigator1.BindingSource = bs
            End Using
        End Using
        TextBox1.DataBindings.Add("text", bs, "test")
        CheckBox1.DataBindings.Add("CheckState", bs, "Bit", True, DataSourceUpdateMode.OnValidation, CheckState.Indeterminate)
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                        Data Source=C:\DATA\AccessDB.accdb;")
            Using DA As New OleDbDataAdapter("SELECT * FROM T1", conn)
                Dim cb As New OleDbCommandBuilder(DA)
                bs.EndEdit()
                cb.QuotePrefix = "["
                cb.QuoteSuffix = "]"
                DA.Update(dt)
                dt.Rows.Clear()
                DA.Fill(dt)
            End Using
        End Using
    End Sub

End Class

Ah, I see the difference now. When I tried that I specified False for the fourth argument, to indicate that formatting was not enabled. I wouldn't have thought that that would make a difference in this case but obviously it does. I stand corrected.

[kpmc]

No worries, jmc.

So, have you found me a job over there yet? Trying to find a cozy govnt sponsor