Solved

Update Record VB 2005 - Access DB

Posted on 2006-11-10
29
978 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
[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
  • 11
  • 8
  • 7
  • +1
29 Comments
 
LVL 1

Author Comment

by:weight01
ID: 17917550
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
ID: 17917556
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
ID: 17917589
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 1

Author Comment

by:weight01
ID: 17917629
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
ID: 17917641
I think it has something to do with the fact that ComplaintID is the primary key?
0
 
LVL 6

Expert Comment

by:riyazthad
ID: 17917661
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
ID: 17917714
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
ID: 17917754
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
ID: 17917803
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
ID: 17917814
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
ID: 17917820
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
ID: 17917835
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
ID: 17917852
sorry i have allocated points incorrectly

how can rectify?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17917866
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17917868
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
ID: 17917890
Nightman.... or the page editor could just go ahead and do it :)
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17917895
It's good to be the King ...
0
 
LVL 1

Author Comment

by:weight01
ID: 17917929
Thanks all, sorry for the mix up
0
 
LVL 24

Expert Comment

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

Author Comment

by:weight01
ID: 17917951
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
ID: 17917952
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
ID: 17918005
Appologies, if you can reopen again... I will spilt
Thanks
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17918054
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
ID: 17918129
how many points do you deserve for giving me a hard time :)
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17918150
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
ID: 17918161
LOL :)
0
 
LVL 1

Author Comment

by:weight01
ID: 17918220
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
ID: 17918251
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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

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…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

623 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