how to insert checkbox state into Access database

Hi,

In my Access 2003 database, I have a table "tblBeverages" and in the table there is field called "juices" which is a checkbox (true/false) boolean. I am creating a windows application that will insert the value of the checkbox into the Access database.

But I get Datatype mismatch when I try to do it.

Any ideas?  

Thanks

Peter
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim sqlconn1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Administrator\Documents\Desktop\dbBeverages.mdb")
        Try
            sqlconn1.Open()
            Dim ssqlconn1 As String
            ssqlconn1 = "INSERT INTO tblBeverages (juice, Remarks) VALUES (@juice, @remarks)"
            Dim ssqlcmd As New OleDb.OleDbCommand(ssqlconn1, sqlconn1)
            ssqlcmd.Parameters.AddWithValue("@remarks", Me.txtRemarksED.Text)
            ssqlcmd.Parameters.AddWithValue("@juice", Me.CheckBox4.Checked)
            ssqlcmd.ExecuteNonQuery()
        Catch ex As OleDb.OleDbException
            MsgBox(ex.Message.ToString)
        End Try
 
    End Sub

Open in new window

billyboy71Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

athapaCEO / CTOCommented:
Try this

ssqlcmd.Parameters.AddWithValue("@juice", IIF(Me.CheckBox4.Checked,-1,0))
0
xPert_UmerCommented:
I believe these need to be inserted as a 1 (for yes or true) and 0 (for no or false). since it will be a numeric value inserted.
i think you should add like this


and when you get result get back... cast this 1 to true and 0 to False


ssqlcmd.Parameters.AddWithValue("@juice", IIF(Me.CheckBox4.Checked,1,0))

Open in new window

0
billyboy71Author Commented:
Hi,

But when I press the button , I still get the error "Data type mismatch in criteria expression"

I get this error even after I tried using the 2 suggestions above.

Peter
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

athapaCEO / CTOCommented:
Check the inner exceptions. That may reveal which datatype is having problem.

Try putting the whole statement in the ssqlconn1 (without parameters) and see if that works

            ssqlconn1 = "INSERT INTO tblBeverages (juice, Remarks) VALUES (0, 'test')"
            Dim ssqlcmd As New OleDb.OleDbCommand(ssqlconn1, sqlconn1)
            ssqlcmd.ExecuteNonQuery()

0
billyboy71Author Commented:
Hi,

The suggestion of using constant values as mentioned above works just fine. I was able to check the "juice" checkbox.  But I can't seem to do it with parameter values.

Peter
0
billyboy71Author Commented:
Hi

OK, I used a different method this time to get it right.  I used variables instead of parameter values.

I declared my variables first.  Just after Public Class Form statement
 Dim varJuice As String
    Dim varRemarks As String
    Dim varDate As Date

Then for the method of the button, I used the following snippet

Thanks

Peter




Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim sqlconn1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Administrator\Documents\Desktop\dbBeverages.mdb")
        varJuice = Me.CheckBox4.Checked
        varRemarks = Me.txtRemarksED.Text
        varDate = Me.txtDate.Text
        Try
            sqlconn1.Open()
            Dim ssqlconn1 As String
            ssqlconn1 = "INSERT INTO tblBeverages (date1, juice, Remarks) VALUES (#" & varDate & "#, " & varJuice & ",'" & varRemarks & "')"
            Dim ssqlcmd As New OleDb.OleDbCommand(ssqlconn1, sqlconn1)
            'ssqlcmd.Parameters.AddWithValue("@remarks", Me.txtRemarksED.Text)
            'ssqlcmd.Parameters.AddWithValue("juice", Me.CheckBox4.Checked)
            ssqlcmd.ExecuteNonQuery()
        Catch ex As OleDb.OleDbException
            MsgBox(ex.Message.ToString)
        End Try
    End Sub

Open in new window

0
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.