[VB.net/MS Access] Database not updating after removing row.

Question

4.00/5 (1 vote)

See more:

, +

Alright, I am having the hardest time figuring this out. I have done research on fixing this for a couple days and I am stuck. It is time to ask for help.

In my project I am trying to delete a row out of the database and I read how to do that from many different sources telling me to use Ole objects and all that. I have something that works in a way: the row deletes, but when I run the program it doesn't save when I do it this way.

    Public Function ForceDelete()
    ForceDelete = Nothing

    ' Validate
    frmDatabase.Validate()

    ' Make sure no more edits can be made
    frmDatabase.bsMagicCards.EndEdit()

    ' Delete Entry
    frmDatabase.RDPCD_CardsDataSet.Tables("tblMagicCards").Rows(frmDatabase.dgCardList.CurrentRow.Index).Delete()

    ' Accept changes
    frmDatabase.RDPCD_CardsDataSet.Tables("tblMagicCards").AcceptChanges()

    ' Update the table
    frmDatabase.TblMagicCardsTableAdapter.Update(frmDatabase.RDPCD_CardsDataSet.tblMagicCards)

    ' Accept any changes
    frmDatabase.RDPCD_CardsDataSet.Tables("tblMagicCards").AcceptChanges()

    ' Turn off edits
    blnEdits = False
    DisableAll()
End Function

I know that when it is done this way, the changes are accepted before the update and therefor it won't save it into the database that way, but when I do it this way:

Public Function ForceDelete()
    ForceDelete = Nothing

    ' Validate
    frmDatabase.Validate()

    ' Make sure no more edits can be made
    frmDatabase.bsMagicCards.EndEdit()

    ' Delete Entry
    frmDatabase.RDPCD_CardsDataSet.Tables("tblMagicCards").Rows(frmDatabase.dgCardList.CurrentRow.Index).Delete()

    ' Update the table
    frmDatabase.TblMagicCardsTableAdapter.Update(frmDatabase.RDPCD_CardsDataSet.tblMagicCards)

    ' Accept any changes
    frmDatabase.RDPCD_CardsDataSet.Tables("tblMagicCards").AcceptChanges()

    ' Turn off edits
    blnEdits = False
    DisableAll()
End Function

Then I get this error:
OleDbException was unhandled
Data type mismatch in criteria expression.

So, I would appreciate any help I can get on this! Thanks in advance!

Posted 9-Mar-11 9:40am

RadethDart

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2011-03-10T18:29:00

Solution 1

I suggest reading the documentation on AcceptChanges and Update. Update writes the dirty records in the DataSet/DataTable back to the database. AcceptChanges tells the DataSet/DataTable that the changes that were made are now the current state of the records. The two operations are completely different.

Call Update, then AcceptChanges.

Now, as for the error, it's impossible to tell what the problem is because we know nothing of your database schema, nor the SQL statement that the DataAdapter is using. From the message, you've got a problem with the database expecting one data type for whatever column has a problem and your code is supplying a different data type, maybe in the SQL statement, or in any OleDbParameter objects you've created.

Posted 10-Mar-11 18:29pm

Dave Kreskowiak

Comments

Dave Kreskowiak 11-Mar-11 18:29pm

Yeah, because most of that statement is illegal. More specifically, the expression "? = 1" is not possible. All you would normally need is just the "(ID = ?)" part. The rest can be removed IF your ID column uniquely identified every row in the table.

Dave Kreskowiak 12-Mar-11 9:50am

Yeah that would be the SQL statement. But, if that is correct and how you use it depends on the rest of your code. It looks like your using the data designer generated stuff. In which case, the Delete command would use a designer generated Sql DELETE statement that is based on the original Sql SELECT statement that was used to grab the records from the database. You would have to go back into the data designer and supply this simplified DELETE statement to the Delete command for the TableAdapter used for this dataset. If that's the case, then I would suspect you'd also have to modify the UPDATE and INSERT statements as well.

This is why I hate using the designer generated stuff and prefer to write my own database code.