Other SQL ID Problem

[Sanity]

Thought this would be the best place to ask.

My items database ID keep going out of order, dose anyone know a way to fix them, i need the whole row to be sorted and not just the ID's.

And anyway to prevent this from happening? all am doing is editing the database and when i scroll down the IDs and messed up from like
401
402
2073
2074

and so on.

 

[Ardbeg]

The Ruby Item DB is like this.
All over the place, some duplicate IDs and some missing completely.

On an Ad Hoc basis you could add this to the query that lists the table.
ORDER BY ID
I right click on the table items, choose Pane, then SQL. This gives me an area at the top that displays the query.
Add it below the final line.

A more permanent option.
I copied all my data out into a spreadsheet then sorted it on ID, correcting any errors.
You could also simply recreate 1 to xxx and copy that down the ID column.

Make a backup of the database.
Clear the old table.
Once happy with your newly repaired table, paste it back to the now empty table.
I then made the ID into a Non Null, Unique Ascending Key.

 

[Sanity]

Pretty much done the same, made a access database file and when ever it messes up i just import it. but means me having to keep updating the access file along with the sql database, pain but meh.

Why do the ID's do that? When ever i scroll down the table, then close it and re open it, if i go back to were i was they are messed up. Tried setting it as a Identity column but that didn't work.

 

[Ardbeg]

Don't know why it does it. The mob table is the same.
If you make the ID field into a unique ascending Key, it will stop it, or find the query the SQL 200x uses to display the table and add the ORDER BY ID line into it. It wll sort it correctly every time then.
Be careful with the table though, it's easy to screw up player items by shifting the numbers up/down incorrectly.

 

[jasper]

sounds like there isnt an index on that field, if any,, look at this. Not sure what version of SQL people are using nowadays,,

http://www.codeproject.com/Articles/39006/Overview-of-SQL-Server-2005-2008-Table-Indexing-Pa

and then create it (para 7.1)

7.1 Creating an Index using by SQL Server Table Designer

 

[Sanity]

Managed to fix this by doing the following.

Open table in Design View
Untick Allow NULL for ID
Make sure ID is Int DataType
Then set primary Key for ID.

For me all IDs are fixed, and so far they not been changed. Hope it helps.

 

[Ardbeg]

Managed to fix this by doing the following.

Open table in Design View
Untick Allow NULL for ID
Make sure ID is Int DataType
Then set primary Key for ID.

For me all IDs are fixed, and so far they not been changed. Hope it helps.

Good work.
It's a constant problem with these SQL tables.
Duplicates are a real pain to deal with too when it won't let you delete them.