Update Record VB 2005 - Access DB

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


LVL 1
weight01Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jeff CertainConnect With a Mentor Commented:
As mentioned before, try SET IsClosed = 0 or 1.... TRUE/FALSE may not cast implictly to YES/NO
0
 
weight01Author Commented:
sorry
sql string is:

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

thanks
0
 
NightmanCTOCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
NightmanConnect With a Mentor CTOCommented:
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
 
weight01Author Commented:
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
 
weight01Author Commented:
I think it has something to do with the fact that ComplaintID is the primary key?
0
 
riyazthadCommented:
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
 
weight01Author Commented:
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
 
weight01Author Commented:
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
 
riyazthadCommented:
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
 
Jeff CertainCommented:
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
 
NightmanCTOCommented:
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
 
weight01Author Commented:
sorry i have allocated points incorrectly

how can rectify?
0
 
Jeff CertainCommented:
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
 
NightmanCTOCommented:
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
 
Jeff CertainCommented:
Nightman.... or the page editor could just go ahead and do it :)
0
 
NightmanCTOCommented:
It's good to be the King ...
0
 
weight01Author Commented:
Thanks all, sorry for the mix up
0
 
Jeff CertainCommented:
oooh.... so now I get no points? Shoot... you CAN split them :)
0
 
weight01Author Commented:
Sorry Chaosian did not know you could, I am sure I will have another Q or two shortly
0
 
NightmanCTOCommented:
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
 
weight01Author Commented:
Appologies, if you can reopen again... I will spilt
Thanks
0
 
Jeff CertainCommented:
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
 
weight01Author Commented:
how many points do you deserve for giving me a hard time :)
0
 
NightmanCTOCommented:
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
 
Jeff CertainCommented:
LOL :)
0
 
weight01Author Commented:
Thanks anyway, hope point split is OK.
I read the books but always end up on http://www.experts-exchange.com
Cheers
0
 
Jeff CertainCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.