weight01
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.In dex > -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.In dex).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
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.In
Dim connection As New OleDbConnection("Provider=
Dim sql As String = String.Empty
sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='" & dgComplaints.Item(1, dgComplaints.CurrentRow.In
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
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.In dex).Value
Otherwise, what is the value of gComplaints.Item(1, dgComplaints.CurrentRow.In dex).Value in this statement?
Try this:
sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID=" & dgComplaints.Item(1, dgComplaints.CurrentRow.In
Otherwise, what is the value of gComplaints.Item(1, dgComplaints.CurrentRow.In
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.In dex).Value is 1 - this is an auto number created by the primary key.
thanks
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.In
thanks
ASKER
I think it has something to do with the fact that ComplaintID is the primary key?
change line
sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='" & dgComplaints.Item(1, dgComplaints.CurrentRow.In dex).Value & "'"
TO
sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='" & Convert.ToInt32(dgComplain ts.Item(1, dgComplaints.CurrentRow.In dex).Value ) & "'"
Thad
sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='" & dgComplaints.Item(1, dgComplaints.CurrentRow.In
TO
sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='" & Convert.ToInt32(dgComplain
Thad
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sql = "UPDATE tblComplaints SET IsClosed = -1 WHERE ComplaintID=" & dgComplaints.Item(0, dgComplaints.CurrentRow.In dex).Value
WORKS
Thanks nightman (and all)
Could come explain why, and what would be the value for false ..... please (i am a newbie)
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
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.
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.
ASKER
sorry i have allocated points incorrectly
how can rectify?
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.
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 ...
ASKER
Thanks all, sorry for the mix up
oooh.... so now I get no points? Shoot... you CAN split them :)
ASKER
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.
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.
ASKER
Appologies, if you can reopen again... I will spilt
Thanks
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. :)
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. :)
ASKER
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 :(
and 60% for me for getting it right.
Pity we can't give you any for being a good sport :(
LOL :)
ASKER
Thanks anyway, hope point split is OK.
I read the books but always end up on https://www.experts-exchange.com
Cheers
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.
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.
ASKER
sql string is:
sql = "UPDATE tblComplaints SET IsClosed = True WHERE ComplaintID='" & dgComplaints.Item(0, dgComplaints.CurrentRow.In
thanks