Solved

Using Checkbox to Update Access DB with VB 2005

Posted on 2006-10-26
4
222 Views
Last Modified: 2010-04-23
Hi

I have an Access DB with a field called Office2003 - Data Type Yes\No.

I have a form in VB 2005 with a Checkbox called CBOffice2003.

When this is checked on the form I would like the DB to be updated.  I have tried as you can see below but I recieve the error:

Data type mismatch in criteria expression.
from
command.ExecuteNonQuery()

Private Sub SaveRecord()

        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\ITAssets.mdb")

        Dim sql As String = String.Empty
        Dim sql1 As String = String.Empty

        If _assetID = 0 Then

            sql = "INSERT INTO tblAssets (EFCRef,Type,Manufacturer,[User],Department,[Model],SerialNo)" & "VALUES('" & cboEFCRef.Text & "', '" & cboType.Text & "','" & cboManufacturer.Text & "','" & cboUser.Text & "','" & cboDepartment.Text & "','" & cboModel.Text & "','" & txtSerialNo.Text & "')"
            sql1 = "UPDATE tblEFCTAGS SET IsUsed = True WHERE EFCTAG='" & cboEFCRef.Text & "'"
        Else

            sql = "UPDATE tblAssets SET EFCRef ='" & cboEFCRef.Text & "'," & "Type='" & cboType.Text & "', Manufacturer='" & cboManufacturer.Text & "',[User]='" & cboUser.Text & "',Department='" & cboDepartment.Text & "',[Model]='" & cboModel.Text & "',SerialNo='" & txtSerialNo.Text & "',Office2003='" & CBOffice2003.Checked & "' WHERE assetID=" & _assetID
            sql1 = "UPDATE tblEFCTAGS SET IsUsed = True WHERE EFCTAG='" & cboEFCRef.Text & "'"

        End If

        conn.Open()

        Dim command As New OleDbCommand(sql, conn)
        Dim command1 As New OleDbCommand(sql1, conn)
        command.ExecuteNonQuery()
        command1.ExecuteNonQuery()


        conn.Close()

        Me.Close()

    End Sub

Thanks
0
Comment
Question by:weight01
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 17811536
"Type" is a reserved word in Access.  Try sticking it in square brackets.

Roger
0
 
LVL 1

Author Comment

by:weight01
ID: 17811803
No did not work

Everything worked fine until I added the checkbox - CBOffice2003.

If I remove - ,Office2003='" & CBOffice2003.Checked & "' from the UPDATE string it works fine.
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 17812061
Sorry, missed that.  CBOffice2003.Checked returns a Boolean, not a string, so it does not need single quotes around it.

Roger
0
 
LVL 1

Author Comment

by:weight01
ID: 17812112
That worked fine Roger.

Your help is appreciated.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now