Solved

Using Checkbox to Update Access DB with VB 2005

Posted on 2006-10-26
4
240 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
No Data for DropDown List 2 28
vb.net 1 month apart 11 35
VB.NET String Settings and Temp Folder Question 3 52
VB.NET Application Installation with sqlserver 8 27
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of convertingā€¦
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦

808 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