asked on

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

athapa

Try this

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

xPert_Umer

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

billyboy71

ASKER

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

athapa

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()

billyboy71

ASKER

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

billyboy71

ASKER

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

ASKER CERTIFIED SOLUTION

Computer101

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial