Database not clearing - bringing back deleted items

Hi all,

Something really weird is happening, I have been testing my app daily for the past week and it has not shown any issue but today, and this evening something weird is happening.

Basically, I dropped all tables (like I have been doing all week) from my database tables, and rand the query...

select * from SITEMAPLINKS;

As expected it shows 0 results.

Now when I did this process and then added things back into the app I noticed the results were doubling (these were ones I had already tested). I then ran the above again and along with the expected results we also ones that were in there from a week ago.

My app is powered by a script that runs in the background - this is currently NOT running, yet just now while trying to find the issue I cleared the database by running the creation script that first of all drops the table, I then ran a delete command in the sql console again the results were as expected 0.

I then added into the app the link and then queried the database again and there were all the links again, but the backend script isn't running so there should be nothing there yet. The submit from the app goes into a separate table.

Any thoughts on what might be happening?

I've also noticed the auto increment id is just picking up where it left off after I've deleted all the data - if I clear it on row 20 for example, the next time I add something it will start at row 21

If in a query you extract something using the id in the WHERE clause there is a slight chance that this would happen. Ideally it should not. As for the auto increment problem this is what you can do:

SELECT MAX( column ) FROM table ;

Replace 'column' with the name of the auto incrementing and 'table' with the name of the table.


Replace 'number' with the result of the previous command plus one and replace 'table' with the table name.

If you deleted all the rows in the table, then you could run the 'alter table' command and set AUTO_INCREMENT to 0.

You could also do the following: DBCC CHECKIDENT ('databasename.dbo.tablename', RESEED, number) Set number to 0 or whatever you want.

That sounds like pretty insane behaviour, which usually means that there's something fundamental that you've missed. For instance, are you sure you're accessing the same database for all the queries? If you're using sqlite, perhaps you have a relative path and so you end up switching databases depending on which directory you're running your code from. Or perhaps you're not closing transactions and so some data is being written when you're not expecting it to be.