DELETE statement is deleting more records than it should...

Hi there... I'm using an Embedded FB DB as the backend for my .NET app, and it has solved a number of problems I had with SQLite (my app is heavily multi-threaded, so SQLite didn't work out too well), but I seem to have run into an odd issue with FB.

I have a thread that deletes items from a table when they're "done". I'm using the same SQL I was using with SQLite (which worked fine) but with FB it appears to be deleting all records from the table that begin with the text of the item that's "done"

For example, I have three items, Test1, Test2, Test3. Test1 runs its course and the thread catches it and proceeds to remove it from the table. The SQL is as follows:

"DELETE FROM tblTasks WHERE Name='Test1'"

Now... I've done concurrent DB checks to confirm what's happening, and basically, instead of deleting "Test1", it deletes Test1, Test2, and Test3.

At first I thought the DELETE command was somehow deleting all records regardless of what Name I specified, but it ends up that it's a matter of a wildcard somehow being applied to the Name that I provide.

I tested this by renaming my items to completely unrealted names. Test1, Check1, Sample1.

I then ran the same exact code and it only deleted the one item instead of all of them.


So... why is it deleting all rows that have a Name starting with the text I'm specifying? I'm not using a wildcard character, so what can I do to stop thig behavior?



WATYF

I'm pretty sure that this is something in your code either messing around with the statement or the parameters to the statement.

I very much doubt that there is something wrong in FB with such a simple DELETE statement (in fact I have used very similar delete statements and all of them deleted what they should)

Try this:
get IBSQL ( from www.ibobjects.com) or any other free SQL app ... run your query and see what it deletes.
If it deletes what it should that means that you have a problem in your code ...

Good luck.

Here's the code... I can't imagine there's a problem in it... the only thing I can think of is that FB sees the ' character as some kind of wildcard.

sTsk = "Test"
cmd = db.CreateCommand
cmd.CommandText = "DELETE FROM tblTasks WHERE TskNm='" & sTsk & "'"
cmd.ExecuteNonQuery()
cmd = Nothing

And my example above was typed out wrong... the items would be Test, Test1, Test2. If I try to delete "Test", it deletes them all.



WATYF

What I'm saying is, I did run just that query, and that's what it did. I checked the table, saw that there were three records, ran that one SQL statment, and then there were no records.


WATYF

btw, there are no parameters for this statement... a single SQLCommand is created, I set the command text, and execute the query. The only variable being passed to the code is the name of the item that it's supposed to delete.


WATYF

Well... I figured it out. When trying to convert my previous tables (Created in SQLite) to FB tables, I picked the wrong data type.

I was using data type BLOB SUB_TYPE TEXT for the "Name" field, so something about that data type caused it to DELETE anything that started with "Test".

Anyway... I switched the data type for that field to VARCHAR and the problem went away.



WATYF