views:

417

answers:

2

Hi all,

I have a simple problem that reads an Excel file (using interop) and fills an MSSQL database file with some data extracted from it. That's fine so far. I have a Shops table with the following fields:

  • ID: int, auto-generated, auto-sync: on insert
  • Name: string
  • Settlement: string
  • County: string
  • Address: string

I read the excel file, and then create a new Shops object and set the Name, Settlement, County and Address properties and I call Shops.InsertOnSubmit() with the new Shops object.

After this I have to reset the database (at least, the table), for which the easiest way I found was to call the DeleteDatabase() method and then call CreateDatabase() again. The problem is, that after the first reset, when I try to fill the table again, I get the exception: The database generated a key that is already in use.

Additionally, from that point on, I'm unable to use that database file, because DatabaseExists() returns FALSE, but when I call the CreateDatabase() method, it throws an exception, that the database already exists (although the data files don't exist).

What am I doing wrong? Thank you very much in advance!

+2  A: 

It sounds like you are re-using the data-context beyond what is wise. Try disposing and re-creating the data-context after deleting the database.

I suspect the problem is that the identity manager is still tracking objects (destroying and recreating the database is such an edge-case that I think we can forgive it for not resetting itself here).

Marc Gravell
Thank you very much, I disposed the data context after deleting the database and then reinstantiated and recreated it, so it is working now. Thank you so very much!One more thing: how can I 'release' the databases I created so far? MSSQL won't allow me to create databases with that name...
ShdNx
I don't know, I'm afraid.
Marc Gravell
Thanks. I had similar problem because I left ASP.Net Development Server running while re-deploying database. I must remember to shutdown the Development Server before redeploying.
Dave
+1  A: 

I encountered this error. I had a log table, with an identity. I was truncating the log, while my application was running. What happened was the DB would start the identity column over again when I truncated, however the data context I was using to log still had objects it was tracking with the same key.

Anthony D