Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using Checkbox to Update Access DB with VB 2005

Posted on 2006-10-26
4
Medium Priority
?
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

688 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