Solved

Update Record VB 2005 - Access DB

Posted on 2006-11-10
29
973 Views
Last Modified: 2008-01-09
Hi

I am trying to update a record in an Access DB called IsClosed to true with the following code:

Private Sub CloseRecord()

        If dgComplaints.CurrentRow.Index > -1 Then

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

            Dim sql As String = String.Empty

            sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='" & dgComplaints.Item(1, dgComplaints.CurrentRow.Index).Value & "'"

            connection.Open()

            Dim command As New OleDbCommand(sql, connection)
            Command.ExecuteNonQuery()

            connection.Close()

            RetrieveComplaints()

        Else

            MessageBox.Show("Please select a row for deleting.", _
            "No Row Selected", MessageBoxButtons.OK, _
            MessageBoxIcon.Information)

        End If
    End Sub

I am getting the the following error:

Data type mismatch in criteria expression

On - Command.ExecuteNonQuery().

ComplaintID is the Primary Key in the DB.

Thanks


0
Comment
Question by:weight01
  • 11
  • 8
  • 7
  • +1
29 Comments
 
LVL 1

Author Comment

by:weight01
Comment Utility
sorry
sql string is:

  sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='" & dgComplaints.Item(0, dgComplaints.CurrentRow.Index).Value & "'"

thanks
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
I'm not sure if Access will implicitly convert string to numeric (I assume that ComplaintID is numeric)
Try this:

 sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID=" & dgComplaints.Item(1, dgComplaints.CurrentRow.Index).Value

Otherwise, what is the value of gComplaints.Item(1, dgComplaints.CurrentRow.Index).Value in this statement?
0
 
LVL 29

Assisted Solution

by:Nightman
Nightman earned 300 total points
Comment Utility
Sorry, posting while you were.

sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID=" & dgComplaints.Item(0, dgComplaints.CurrentRow.Index).Value

What is the value of dgComplaints.Item(0, dgComplaints.CurrentRow.Index).Value?

And have you tried

sql = "UPDATE tblComplaints SET IsClosed = 1 WHERE ComplaintID=" & dgComplaints.Item(0, dgComplaints.CurrentRow.Index).Value
or
sql = "UPDATE tblComplaints SET IsClosed = -1 WHERE ComplaintID=" & dgComplaints.Item(0, dgComplaints.CurrentRow.Index).Value
0
 
LVL 1

Author Comment

by:weight01
Comment Utility
Sorry should have been more clear

the data type in the db for IsClosed is Yes/No

I have tried your suggestion but that does not work.

The value of dgComplaints.Item(0, dgComplaints.CurrentRow.Index).Value is 1 - this is an auto number created by the primary key.

thanks
0
 
LVL 1

Author Comment

by:weight01
Comment Utility
I think it has something to do with the fact that ComplaintID is the primary key?
0
 
LVL 6

Expert Comment

by:riyazthad
Comment Utility
change line

sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='" & dgComplaints.Item(1, dgComplaints.CurrentRow.Index).Value & "'"

TO

sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='" & Convert.ToInt32(dgComplaints.Item(1, dgComplaints.CurrentRow.Index).Value) & "'"

Thad
0
 
LVL 1

Author Comment

by:weight01
Comment Utility
riyazthad  - that did not work

this is a copy of the results from: Dim sql As String = String.Empty on debug

sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='1'"

Everything looks good but still get Data type mismatch in criteria expression error.
0
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 200 total points
Comment Utility
As mentioned before, try SET IsClosed = 0 or 1.... TRUE/FALSE may not cast implictly to YES/NO
0
 
LVL 1

Author Comment

by:weight01
Comment Utility
sql = "UPDATE tblComplaints SET IsClosed = -1 WHERE ComplaintID=" & dgComplaints.Item(0, dgComplaints.CurrentRow.Index).Value

WORKS

Thanks nightman (and all)

Could come explain why, and what would be the value for false ..... please (i am a newbie)
0
 
LVL 6

Expert Comment

by:riyazthad
Comment Utility
What is happening running directly from ACCESS thru Query window

sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='1'"

try this

sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID=1"
(Without single quote)

Thad
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
In general, 0 is false and anything else is true. To be pendantic, at the bit level 0 is false and 1 is true.... but very little software really uses a bit to store data, since a 32-bit operating system can't easily store anything but multiples of 32 btits.
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
True can't be implicitly converted to a boolean. It looks like it can, but Access does not allow it. -1 for True, 0 for false.

Access sees this as a string (even though in the interface it displays it as a string for you). Whenever you have a type mismatch, this is the likely cause - you are trying to insert something of type A into a column of type b (or variable of type b) and the language you are using can't do the conversion.
0
 
LVL 1

Author Comment

by:weight01
Comment Utility
sorry i have allocated points incorrectly

how can rectify?
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
Ummmm.... as much as I appreciate the sentiment, this one really, really needs to be split. Nightman had the 1/-1 mentioned well before I did.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Expert Comment

by:Nightman
Comment Utility
Post a question in Community Support (http://www.experts-exchange.com/Community_Support/) asking the moderators to re-open the question and then you can re-accept.

I am glad that we have been able to help.
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
Nightman.... or the page editor could just go ahead and do it :)
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
It's good to be the King ...
0
 
LVL 1

Author Comment

by:weight01
Comment Utility
Thanks all, sorry for the mix up
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
oooh.... so now I get no points? Shoot... you CAN split them :)
0
 
LVL 1

Author Comment

by:weight01
Comment Utility
Sorry Chaosian did not know you could, I am sure I will have another Q or two shortly
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
LOL
weight01 - thanks for that, but I also would have split the points. Even though my answer was the first correct one, Chaosian also assisted, and pointed you back to my answer. I would have split 60-40.
0
 
LVL 1

Author Comment

by:weight01
Comment Utility
Appologies, if you can reopen again... I will spilt
Thanks
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
Actually, I was just giving you a hard time.

I have no problem leaving Nightman with the points.

However... since I've given you a hard time, I'll reopen the question and you can do whatever you want with the points. Instead of accepting, there's a link right above the comment box that allows you to split the points if you feel that more than one of us actually helped. :)
0
 
LVL 1

Author Comment

by:weight01
Comment Utility
how many points do you deserve for giving me a hard time :)
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
LOL - 20% for bringing some humour, 20% for technical assistance ;)

and 60% for me for getting it right.

Pity we can't give you any for being a good sport :(
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
LOL :)
0
 
LVL 1

Author Comment

by:weight01
Comment Utility
Thanks anyway, hope point split is OK.
I read the books but always end up on http://www.experts-exchange.com
Cheers
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
Glad we could be of help.

And, yes, this is a fabulous resource. (I'm a volunteer, like all page editors, so you *know* they're not paying me to say that LOL)

If we've really been of help, I'd encourage you to leave a note of thanks to the folks that run this site -- just drop it in the Community Support topic area, and they'll get it.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

17 Experts available now in Live!

Get 1:1 Help Now