Checkbox not working

[K3JAE]

END goal:

Read in the value of DETENTION_OVERRIDE_01 from database, if Det01 = "True" then place a check in the appropriate checkbox.

Using the following code, even though Det01 comes back properly (shows as being True), the Case Select shows it as false and no check is entered. What am I missing?

    Private Sub Detention01()
        Dim cmd As New SqlCommand
        Dim reader As SqlDataReader

        Try
            cmd.CommandText = "SELECT DETENTION_OVERRIDE_01
                                 FROM 
                                      LOAD_INFO_TABLE 
                                WHERE 
                                      PETS_LOAD_NUMBER= '" & tbPETSLoadNumber.Text & "'"

            cmd.CommandType = CommandType.Text
            cmd.Connection = New SqlConnection()
            cmd.Connection.ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
            cmd.Connection.Open()

            Try
                reader = cmd.ExecuteReader()
                Dim Det01 As String

                With reader
                    .Read()

                    Det01 = .GetString(0)

                    Select Case True
                        Case Det01 = "True"
                            ccb01DetOverride.Checked = True
                        Case Else
                            ccb01DetOverride.Checked = False
                    End Select

                    Return
                End With

                reader.Close()

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        cmd.Connection.Close()
        Detention90()
    End Sub

[bmwpete]

In your select case you are testing for a Boolean true value, but Dec01 is a string, there are a number of ways to do this one would be,

Select Case Dec01
       Case = "True"
              ccb01DetOverride.Checked = True
       Case Else
             ccb01DetOverride.Checked = False
End Select

[Shaggy Hiker]

Turn Option Strict ON for the project, fix the resulting errors, then try it. As it is, you are comparing a string to a Boolean, since "True" is a string, while True is a Boolean. How does a string convert to a Boolean? I'm not even sure which rules VB follows for that. In general, ANY string should equate to True, but maybe not.

In any case, the Select Case and all around it is unnecessary. There's too much implicit conversion going on and you're using a DataReader when you don't need one.

ExecuteScalar returns the first value from the first record. You only want one value, and you only want the first record, so ExecuteScalar is ideal for you, as it not only works, it's faster than a DataReader. However, it returns an object, which could be True, False, or Null for the query you have (it will be Null if there is no match).

So, you could write that in this fashion:

Using cn As New SqlConnection(yourConnectionStringHere)
 Using cmd as SqlClient.SqlCommand = cn.CreateCommand
   Try
     cmd.CommandText = "SELECT DETENTION_OVERRIDE_01 FROM LOAD_INFO_TABLE WHERE  PETS_LOAD_NUMBER= @p1"
     cmd.Parameters.AddWithValuie("@p1",tbPETSLoadNumber.Text)
     cn.Open
     Dim obj = cmd.ExecuteScalar
     If obj IsNot Nothing Then
       ccb01DetOverride.Checked = CBool(obj)
     Else
       'It wasn't found, so it's up to you what to do here.
     End If
   Catch ex As Exception
       MessageBox.Show(ex.Message)
   End Try
 End Using
End Using

A few points about that approach:

1) The Using block will correctly clean up both the Connection and the Command objects, even if an exception is thrown, so you don't have to worry about closing or disposing anything.

2) I changed the query to use a parameter. Concatenating user entered text directly into a query is a bad idea, because it leaves you open to SQL injection attacks. Parameters format the entry correctly for the type, and prevent SQL injection.

3) This still may not quite work. It isn't clear what type is actually in the field. If it is a Boolean, then CBool should handle it correctly. If it is the string "True" or "False", then it probably will not, but you'll know soon enough.

4) I knew I'd be too slow, so I addressed a few other issues, such as the parameters and the Using, just to add value.

[K3JAE]

Option Strict is and has been ON

Used your suggested code.
cmd.Parameters.AddWithValuie("@p1",tbPETSLoadNumber.Text) had a syntax error.. you had AddWithValuie - corrected. No biggie, just noting.

Received: "Conversion from string "True" to type 'Boolean' is not valid - because when I check the box it writes to the database as "True" obviously unchecked it writes False. Not sure why it is using True/False instead of 0/1.

[Shaggy Hiker]

What's the data type for the field in the DB? You may just be seeing "True" and "False", depending on how you are looking at it. If the data type is a character type, then you have strings in there. If you have bit or int, then you are just seeing those represented. Access does that, though I don't remember SQL Server doing that.

Anyways, if you have actual strings, then you'd have to do a bit more of a conversion. Still, this page:

https://www.oreilly.com/library/view...0928/re29.html

suggests that "True" and "False" would work just fine with CBool. You might try CBool(obj.ToString), since obj itself is an object.

[K3JAE]

The data type in the DB, is set to nvarchar(10)


The following code, which is very similar and basically gives the same intended function as my original non-working code, works as it should. Again the datatype for DRIVER_COUNT is set to nvarchar(5).

    Private Sub LoadResp()
        Dim cmd As New SqlCommand
        Dim reader As SqlDataReader

        Try
            cmd.CommandText = "SELECT
                                DRIVER_COUNT 
                               FROM 
                                LOAD_INFO_TABLE 
                               WHERE
                                PETS_LOAD_NUMBER=@p1"

            cmd.Parameters.AddWithValue("@p1", tbPETSLoadNumber.Text)

            cmd.CommandType = CommandType.Text
            cmd.Connection = New SqlConnection()
            cmd.Connection.ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
            cmd.Connection.Open()

            Try
                reader = cmd.ExecuteReader()
                Dim Driver As String

                With reader
                    .Read()

                    Driver = .GetString(0)

                    Select Case True
                        Case Driver = "SLSC"
                            rbSLSC.Checked = True
                            rbSLSC.BackColor = Color.Yellow
                            rbSLDC.BackColor = Color.Transparent
                            rbDLDC.BackColor = Color.Transparent

                        Case Driver = "SLDC"
                            rbSLDC.Checked = True
                            rbSLSC.BackColor = Color.Transparent
                            rbSLDC.BackColor = Color.Yellow
                            rbDLDC.BackColor = Color.Transparent

                        Case Driver = "DLDC"
                            rbDLDC.Checked = True
                            rbSLSC.BackColor = Color.Transparent
                            rbSLDC.BackColor = Color.Transparent
                            rbDLDC.BackColor = Color.Yellow
                        Case Else
                            Exit Select
                    End Select

                    Return

                End With

                reader.Close()

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        cmd.Connection.Close()
    End Sub

[K3JAE]

The data type for DETENTION_OVERRIDE_01 is set to nvarchar(10)


The following code, which is very similar gives the desired result, as I wish the non-working code to do. Probably not the best coding (I'm not a very proficient coder as you may have guessed )

The datatype for DRIVER_COUNT is set to nvarchar(5) for the code below:

    Private Sub LoadResp()
        Dim cmd As New SqlCommand
        Dim reader As SqlDataReader

        Try
            cmd.CommandText = "SELECT
                                DRIVER_COUNT 
                               FROM 
                                LOAD_INFO_TABLE 
                               WHERE
                                PETS_LOAD_NUMBER=@p1"

            cmd.Parameters.AddWithValue("@p1", tbPETSLoadNumber.Text)

            cmd.CommandType = CommandType.Text
            cmd.Connection = New SqlConnection()
            cmd.Connection.ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
            cmd.Connection.Open()

            Try
                reader = cmd.ExecuteReader()
                Dim Driver As String

                With reader
                    .Read()

                    Driver = .GetString(0)

                    Select Case True
                        Case Driver = "SLSC"
                            rbSLSC.Checked = True
                            rbSLSC.BackColor = Color.Yellow
                            rbSLDC.BackColor = Color.Transparent
                            rbDLDC.BackColor = Color.Transparent

                        Case Driver = "SLDC"
                            rbSLDC.Checked = True
                            rbSLSC.BackColor = Color.Transparent
                            rbSLDC.BackColor = Color.Yellow
                            rbDLDC.BackColor = Color.Transparent

                        Case Driver = "DLDC"
                            rbDLDC.Checked = True
                            rbSLSC.BackColor = Color.Transparent
                            rbSLDC.BackColor = Color.Transparent
                            rbDLDC.BackColor = Color.Yellow
                        Case Else
                            Exit Select
                    End Select

                    Return

                End With

                reader.Close()

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        cmd.Connection.Close()
    End Sub

[jmcilhinney]

This is silly code:

                    Select Case True
                        Case Det01 = "True"
                            ccb01DetOverride.Checked = True
                        Case Else
                            ccb01DetOverride.Checked = False
                    End Select

That's not what a Select Case is for. That would properly be written as:

If Det01 = "True" Then
    ccb01DetOverride.Checked = True
Else
    ccb01DetOverride.Checked = False
End If

or, even more simply:

ccb01DetOverride.Checked = (Det01 = "True")

You can easily set a breakpoint on the appropriate line and check the value of Det01 right as it's being used. A possible reason that it may not work as expected is that there is actually a space at the end of the text. Of course, if you want to store Boolean values in SQL Server then you should be using the bit data type rather than nvarchar if at all possible, but there may be a valid reason that that is not possible.

[Shaggy Hiker]

So, why are you still using the DataReader? You don't need it, since you only want the one value, and ExecuteScalar is faster and doesn't require any cleanup. Sure, you have to do some conversion, and CBool may not be the way, but you are doing conversions with the DataReader, too, you're just converting to string. The change for my code to convert to a string would look like this:

 If obj IsNot Nothing Then
       Dim yourString = obj.ToString
       ccb01DetOverride.Checked = (yourString = "True")
     Else
       'It wasn't found, so it's up to you what to do here.
     End If

However, I'll also note that the code you have has an error in it. You call read on the datareader, but you don't look at the return from that. Read will return False if your query doesn't return any rows. If Read returns False, then the next line will crash. You need to check for that.

[K3JAE]

Question on your code...
You have if obj IsNot Nothing.....

There will be a True or a False in this field. "Nothing," to my understanding, would be a null value?
So if there is a 0 (I did change the Data field to be a bit field from nvarchar yet it still writes False for 0 (which I assume is normal). A Zero (0) does not qualifies as "not nothing" correct?

As for using the data reader, that code I sent was a separate sub. I tried to figure out how to implement your originally suggested code into that Sub but I am not certain how that would be implemented since there will be an actual string that needs to be compared and the appropriate checkbox would then be checked.

[K3JAE]

You can easily set a breakpoint on the appropriate line and check the value of Det01 right as it's being used. A possible reason that it may not work as expected is that there is actually a space at the end of the text. Of course, if you want to store Boolean values in SQL Server then you should be using the bit data type rather than nvarchar if at all possible, but there may be a valid reason that that is not possible.

If you read carefully above in my original comments, the Det01 was indeed bringing me back my expected result. There are no spaces or other anomolies that would have resulted in my case select reading wrong...

[Shaggy Hiker]

I'm not sure why or how you are still seeing True/False if the field is a bit field. SQL Server Management Studio won't show it that way, though I think that Access might. Access would show bit fields as True/False for an Access database, so if you are looking at SQL Server via an Access link, then perhaps it will show True/False for a SQL Server bit field. I don't deal with it that way, so I don't know.

However, if you are using a bit field for the Boolean, then CBool will work. The code I showed would technically return Null if no record matches the WHERE clause, but in most cases, with the code I showed, obj would end up as Nothing if the query returns Null. There is one data type where obj would end up as DBNull, but I can never remember which datatype it is. Certainly a string will end up as Nothing, so a check for IsNot Nothing works for string types. If it's really a bit field now, then it would return 1, 0, or Null. Obj will not be Nothing if it is 1 or 0. I'm pretty sure it will be Nothing if the query returns no data, but I could be wrong about that, because there is one case where that check for Nothing does not work, and it may be a bit field.

The difference between a datareader and ExecuteScalar is so small that you'd never see the difference in performance unless you performed the query hundreds of times in rapid succession, and if you did that, then the datareader would end up being better anyways. Therefore, it's almost an academic question. You do need to clean up a datareader, and they can get in the way (you can't run a different query on the same connection if the datareader is still open), but the performance difference is almost more a matter of style rather than substance. Still, it's good form to have the lightest touch on the database that you need, and this is the ONE place where ExecuteScalar is the right way to go (you rarely need just one value from one record).

However, you do need to realize that the datareader may have no rows. If Read() returns false, then there won't be a row, so any attempt to use it will crash the program, and that will cost a noticeable amount of time. So, use whichever you feel more comfortable with, just be aware of your options, and the way the datareader behaves. Their pretty nice when they work...and pretty thoroughly annoying the rest of the time. I use them a whole lot, I just can't say that I love them.

[K3JAE]

Very good and thanks for that information. Still learning how to manipulate SQL inside VB - trust me my original code was way, way worse than where it is now. Every tidbit of information I get from the better coders is implemented to clean up my original code. This seems to be a never ending process. Not complaining, just learning and hopefully writing more efficient code in the end.

To Follow up on the original issue:
I implemented your code, changed the datafield in MSSMS to bit. I do not use Access for anything.

I did attempt to do a database edit to the column to change all False to 0 and True to 1... however the DB said it did it's thing, reported success, but nothing in the fields changed. They still read True/False. I'm investigating now whether VisualBasic has the correct perameters set for the field... I get confused whether I should use 'checked', 'CheckedState' etc. I need to be able to change that box as required and write to the db, then at later time, when I review a particular record that box would show checked if I made that change. By default the box will be unchecked and it must be manually checked if appropriate.

The code appears to be working yet I am still getting, in some records, the previous error "Conversion from string "True" to type 'Boolean' is not valid. If the field was successfully converted to a true BIT value then this should not be occurring correct?

I notice on the records where the checkbox is supposed to show checked it flashes checked then goes away? No errors. What would cause this to happen?

[Shaggy Hiker]

Yeah, correct. If you are getting that error, then the code has a string and it trying to convert it to a Boolean. You SHOULD know exactly where....but you may not. In the more recent version of VS, I find that I am often taken to the wrong line, and the actual exception is on the line prior to the one I'm shown...which is annoying. In general, I like the features added to the newer versions, though I feel they took a step backwards in some areas.

Are you saying that you see the checkbox momentarily appears checked, then goes right back to unchecked? If that's happening, my first suspicion would be that you are handling the CheckedChanged event and the act of checking the checkbox is triggering a side effect. However, that doesn't really fit, because in that case you shouldn't even get to see the check because the system shouldn't have a chance to draw the control again before it gets unchecked. There are things that could allow it a chance to draw, then immediately switch, such as DoEvents, or doing work in threads, but it certainly isn't normal behavior. If you are seeing it changing, then seeing it changing back, then the act of changing is almost certainly triggering something that is undoing the change. In other words, code is being run to undo the change, and you can find that code...it just might not be all that easy to track down the cause.

[K3JAE]

Correct, I am seeing the checkboxes check then uncheck as quickly as it is checked. You may be correct that another piece of code may be voiding out the checkbox. I'll review my code a bit further via debug and watch and see what may be triggering the unchecking of the box. This is a bit curious but it is not an unreasonable suggestion. I may need to put in a "if not loadform" statement in front of the checkchanged event. This code is to only be checked only at initial form load of the record. Then I do have a checkchanged event that updates if it sees the check.

UPDATE - adding the "if not loadform" in the checkchanged sub and placing the "LoadForm = false" in it's proper place (had it prior to running the sub instead of afterwards, thereby causing the checkchanged sub to fire) resolved the issue. NOW the box shows the check when it is supposed to. Thank you Shaggy Hiker for both the assist as well as the excellent details to help me further understand. You have been GREAT!!