I will try to explain this as easily as I can.
My one table has Indexing messed up, or so it seems.
I have ran into this issue within the last few weeks and not sure what is causing this.
I currently have 815 INDEXED lines within one SQL database. There are "holes" if you will where some index numbers were assigned but the record deleted for various reasons. I know those index numbers are never used again and will show as a gap, if you will, in the numerical listing. This is not my concern.
FURTHER detail to clarify....
My concern is within the last week when I want to add a new row (index) I initially receive an error that there is no row at position 815 (currently the last record is at index 815). If I exit out of that message box and create the record anyways and save it the record saves fine and takes the next available index number.
I then import expenses, via a second form within the program, that looks at a primary key field and writes to the record as required... this has ceased recently. When I try to import expenses and save the data to the proper record I now receive the error "An item with the same key has already been added."
Using VB debug it shows it is trying to create a record at index 812 when that index is already used. The record at Index 812 is the last record that actually "works." 3 records have been added since, thus why currently there is now Index 815 in the database.
Is there a proper repair for the SQL indexing that I can use? I tried a few items but it does not seem to resolve the issue. I used the MSSMS Reorganize as well as Rebuild tools but that did not resolve the issue.
Is it possible something is amiss in the program? Nothing has been changed in this program for sometime (6 months maybe) and worked fine up to a couple of weeks ago. The ONLY change was upgrading from VS 2015 to VS 2017 and recompiled using the 2017 compiler. Could that potentially cause something to go awry?
I did run "DBCC checkdb" and the following returned (not including everything just the last few lines:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'PETS_Database'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.