Link to home
Start Free TrialLog in
Avatar of weight01
weight01Flag for United Kingdom of Great Britain and Northern Ireland

asked on

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


Avatar of weight01
weight01
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

sorry
sql string is:

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

thanks
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?
SOLUTION
Avatar of Nightman
Nightman
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
I think it has something to do with the fact that ComplaintID is the primary key?
Avatar of riyazthad
riyazthad

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
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
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
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.
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.
sorry i have allocated points incorrectly

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