Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB10.0 database VB.NET , +
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 10:40am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
Dave Kreskowiak at 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 at 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.

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



Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 15 Mar 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100