locked
How do I correctly read boolean values from an Access database? RRS feed

  • Question

  • Hi all,

    I'm having a strange problem.  In my program's database, I have a series of YESNO columns that are set to true.  In my program, I've written some functions to get those values.  For example:

        Public Function IsLocked() As Boolean
            Queried_Profile = GetSetting(Application.CompanyName, Application.ProductName, "Queried_Profile")
            cnn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath & IDPass)
            cnn.Open()
            comm = New OleDb.OleDbCommand("SELECT [Is_Locked] FROM [User_Profiles] WHERE [User_Name]='" & Queried_Profile & "'", cnn)
            IsLocked = Convert.ToBoolean(Convert.ToInt32(comm.ExecuteScalar))
            cnn.Close()
            cnn.Dispose()
        End Function

    This is the latest version.  I've also tried Convert.ToBoolean(comm.ExecuteScalar) and IsLocked = comm.ExecuteScalar. In each case, the value always returns false, despite the fact that Access shows those values as True (-1 in Access).  How do I make it read and return the real value?

    Thanks!

    Jason

    Thursday, August 28, 2014 6:59 AM

Answers

  • Hello,

    Try the following and note the use of HasRows. You can skip the while loop if you are expected one row back and do a read in it's place

        Public Sub Demo()
    
            Using cn As New OleDb.OleDbConnection With {.ConnectionString = "TODO"}
    
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT [Is_Locked] FROM [User_Profiles] WHERE [User_Name]='" & Queried_Profile & "'"
                    }
    
                    cn.Open()
                    Dim Reader = cmd.ExecuteReader
    
                    If Reader.HasRows Then
                        While Reader.Read
                            Console.WriteLine(Reader.GetBoolean(0))
                        End While
                    End If
    
                End Using
    
            End Using
    
        End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Proposed as answer by Paul P Clement IV Thursday, August 28, 2014 12:09 PM
    • Marked as answer by Carl Cai Tuesday, September 9, 2014 6:30 AM
    Thursday, August 28, 2014 11:00 AM
  • ExecuteScalar would not be appropriate for this query because the assumption is that there is at least one row in the table that matches the criteria (which might not be the case). I would recommend working with Kevin's example, although I would use a parameter for your criteria instead of embedding a variable in the SQL statement, as in Cor's example.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    • Edited by Paul P Clement IV Thursday, August 28, 2014 11:57 PM
    • Marked as answer by Carl Cai Tuesday, September 9, 2014 6:30 AM
    Thursday, August 28, 2014 12:09 PM
  • What I do is set the column type to a Bit definition. The Bit column value = 1 is true, and 0 = false.

    http://msdn.microsoft.com/en-us/library/ms177603.aspx

    • Marked as answer by Carl Cai Tuesday, September 9, 2014 6:30 AM
    Thursday, August 28, 2014 12:11 PM

All replies

  • Maybe Queried_Profile was not found? I suggest checking this value. Is it valid? Also try this: ‘Dim r = comm.ExecuteScalar’, add a breakpoint, and show the type and value of r.


    • Edited by Viorel_MVP Thursday, August 28, 2014 7:56 AM
    Thursday, August 28, 2014 7:54 AM
  • Not tested but if it is wrong it are typos, the code is created from tested code.

    Imports System.Data.SqlClient
    Public Class Form1
        Private Property WhateverBoolean As Bool
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Try
                Using Con As New SqlConnection("Data Source=YourServer;Initial Catalog=YourTablre;Integrated Security=True")
                    Con.Open()
                    Using com As New SqlCommand("Select YourValue from WhateverTable where ID = @ID", Con)
                        com.Parameters.AddWithValue("@ID", 1)
                        Dim theObj = com.ExecuteScalar
                        If Not theObj Is Nothing AndAlso Not theObj Is DBNull.Value Then
                            WhateverBoolean = CBool(theObj)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    End Class


    Success
    Cor

    Thursday, August 28, 2014 8:45 AM
  • I see now it is Access, therefore chance everywhere Sql/Command to OleDBConnection and OleDBCommand and the import likewise that. And of course the connectionstring but I know you have that already

    For the rest it is the same.


    Success
    Cor


    Thursday, August 28, 2014 9:33 AM
  • Hello,

    Try the following and note the use of HasRows. You can skip the while loop if you are expected one row back and do a read in it's place

        Public Sub Demo()
    
            Using cn As New OleDb.OleDbConnection With {.ConnectionString = "TODO"}
    
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT [Is_Locked] FROM [User_Profiles] WHERE [User_Name]='" & Queried_Profile & "'"
                    }
    
                    cn.Open()
                    Dim Reader = cmd.ExecuteReader
    
                    If Reader.HasRows Then
                        While Reader.Read
                            Console.WriteLine(Reader.GetBoolean(0))
                        End While
                    End If
    
                End Using
    
            End Using
    
        End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Proposed as answer by Paul P Clement IV Thursday, August 28, 2014 12:09 PM
    • Marked as answer by Carl Cai Tuesday, September 9, 2014 6:30 AM
    Thursday, August 28, 2014 11:00 AM
  • ExecuteScalar would not be appropriate for this query because the assumption is that there is at least one row in the table that matches the criteria (which might not be the case). I would recommend working with Kevin's example, although I would use a parameter for your criteria instead of embedding a variable in the SQL statement, as in Cor's example.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    • Edited by Paul P Clement IV Thursday, August 28, 2014 11:57 PM
    • Marked as answer by Carl Cai Tuesday, September 9, 2014 6:30 AM
    Thursday, August 28, 2014 12:09 PM
  • What I do is set the column type to a Bit definition. The Bit column value = 1 is true, and 0 = false.

    http://msdn.microsoft.com/en-us/library/ms177603.aspx

    • Marked as answer by Carl Cai Tuesday, September 9, 2014 6:30 AM
    Thursday, August 28, 2014 12:11 PM
  • Jason, can you make those form objects have the access fields as the data source? I do that very often and it is really simple. Then if I need the value I just look at the form value.

    Bradford W Brown

    Thursday, August 28, 2014 3:20 PM
  • ExecuteScalar would not be appropriate for this query because the assumption is that there is at least one row in the table that matches the criteria (which might not be the case). I would recommend working with Kevin's example, although I would use a parameter for your criteria instead of embedding a variable in the SQL statement.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    You definitely did not look or investigated my code, that was which I showed how to avoid what you wrote in my code by using this syntax. I also took the time to use parameters instead of inline SQL syntax which you so much dislike because of injections. For me it is because parameters give cleaner and better code. 

    If Not theObj Is Nothing AndAlso Not theObj Is DBNull.Value Then


    Jason did not write, I'm a dummy, he asked how to do it correctly. By writing this kind of messages people can get the idea you want to put me and Jason down as dummies. I know from actions of you in past that it is not the case.

     


     

    Success
    Cor



    Thursday, August 28, 2014 10:33 PM
  • Jason did not write, I'm a dummy, he asked how to do it correctly. By writing this kind of messages people can get the idea you want to put me and Jason down as dummies. I know from actions of you in past that it is not the case.

     


     

    Success
    Cor



    I recommended Kevin's code because it was written for Access. You can certainly modify your code for Access as well.

    No one is thinking that anyone is a dummy. The OP can choose which answers are helpful to him. I will update my response and  reference your example for the use of a Command Parameter.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, August 28, 2014 11:57 PM
  • As it turns out, it was a problem leftover from my issues with the oledbdatareader, lol.  Instead of saving a name to the string value Queried_Profile, it was saving it as something to the effect of "System.Data.Reader"

    Jason

    Friday, August 29, 2014 1:45 AM
  • Hello,

    Try the following and note the use of HasRows. You can skip the while loop if you are expected one row back and do a read in it's place

        Public Sub Demo()
    
            Using cn As New OleDb.OleDbConnection With {.ConnectionString = "TODO"}
    
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT [Is_Locked] FROM [User_Profiles] WHERE [User_Name]='" & Queried_Profile & "'"
                    }
    
                    cn.Open()
                    Dim Reader = cmd.ExecuteReader
    
                    If Reader.HasRows Then
                        While Reader.Read
                            Console.WriteLine(Reader.GetBoolean(0))
                        End While
                    End If
    
                End Using
    
            End Using
    
        End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    By the way, you do not have to use HasRows here, since the next Read will give you the appropriate answer. And since you expect a single result (row), then you do not have to use While. So if you are interested in judicious (I think) alternative, and in order to folow the idea of disposable objects, then consider this too (assuming the column is not nullable):

    Using reader = cmd.ExecuteReader

        If reader.Read Then

            IsLocked = reader.GetBoolean(0)

        End If

    End Using

    Also treat the case when the row is not found for some reason. (Set IsLocked to False?)

    Friday, August 29, 2014 5:11 AM