Solved

Update Record VB 2005 - Access DB

Posted on 2006-11-10
29
977 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

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…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 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