| Author |
Topic |
|
JARON
Starting Member
9 Posts |
 Posted - 2010-01-06 : 07:17:20
|
| Hello all,I have a column within a table set to integer and identity, the problem goes with whenever i have to delete a row and add up a new one i want the same id number to be shown.so there would be a gap between the ID numbers, i want them to be in consecutive order, any idea how would it be done?ex: ID Name 1 Edy2 Gary3 Evelyne4 Daisy5 Isacif i deleted row 4 and 5 from the table then adding a new one will give meID Name 1 Edy2 Gary3 Evelyne6 Anthonyi want anthony to have the id number 5.Any clue? Thanks in advance.Ronald |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-06 : 07:18:37
|
| thats how id values are generated. But didnt understand why you need to be concerned about gaps. You can always retrieve data in order you want by ordering on id value |
 |
|
|
JARON
Starting Member
9 Posts |
Posted - 2010-01-06 : 07:22:03
|
| Not worried so much about gaps, but is there any way to have it all consecutive numbers.Can it be done by c# coding?Ronald |
|
|
|
JARON
Starting Member
9 Posts |
Posted - 2010-01-06 : 07:23:26
|
| The thing i am using c#. and in the datagridview i want all the records to have a consecutive id numbers |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-06 : 07:24:08
|
| yup.its possible. but for that you need to manually retrieve maxid value from table and assign maxvalue+1 as next value for id field. |
|
|
|
JARON
Starting Member
9 Posts |
Posted - 2010-01-06 : 07:27:20
|
| Yeah i guess it is the case.Thanks in advance |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-06 : 07:29:29
|
quote:
Originally posted by JARON
Yeah i guess it is the case.Thanks in advance
but if you want consecutive numbers in grid cant you make use windowing functions like row_number() (i assume you use sql 2005 or above) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-06 : 07:32:33
|
quote:
Originally posted by JARONif i deleted row 4 and 5 from the table then adding a new one will give meID Name 1 Edy2 Gary3 Evelyne6 Anthonyi want anthony to have the id number 5.
Sure you want Anthony to have id 5 and not 4 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
JARON
Starting Member
9 Posts |
Posted - 2010-01-06 : 07:36:12
|
quote:
Originally posted by visakh16
quote:
Originally posted by JARON
Yeah i guess it is the case.Thanks in advance
but if you want consecutive numbers in grid cant you make use windowing functions like row_number() (i assume you use sql 2005 or above)
Yeah i guess doing it with sql2005 would be a better choice. Because whithin the code it will have problems later on. so i guess having a good base within the database is better.Can you help me with the function if possible? |
|
|
|
JARON
Starting Member
9 Posts |
Posted - 2010-01-06 : 07:37:12
|
quote:
Originally posted by webfred
quote:
Originally posted by JARONif i deleted row 4 and 5 from the table then adding a new one will give meID Name 1 Edy2 Gary3 Evelyne6 Anthonyi want anthony to have the id number 5.
Sure you want Anthony to have id 5 and not 4
No, you're never too old to Yak'n'Roll if you're too young to die.
Yeah yeah sure it 4, sorry was in a hurry.I wanted an answer heheh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-06 : 07:37:20
|
quote:
Originally posted by JARON
quote:
Originally posted by visakh16
quote:
Originally posted by JARON
Yeah i guess it is the case.Thanks in advance
but if you want consecutive numbers in grid cant you make use windowing functions like row_number() (i assume you use sql 2005 or above)
Yeah i guess doing it with sql2005 would be a better choice. Because whithin the code it will have problems later on. so i guess having a good base within the database is better.Can you help me with the function if possible?
yup just do likeSELECT ROW_NUMBER() OVER (ORDER BY ID) AS Row_No,ID,NameFROM Yourtable |
|
|
|
JARON
Starting Member
9 Posts |
Posted - 2010-01-06 : 07:52:08
|
quote:
Originally posted by visakh16
quote:
Originally posted by JARON
quote:
Originally posted by visakh16
quote:
Originally posted by JARON
Yeah i guess it is the case.Thanks in advance
but if you want consecutive numbers in grid cant you make use windowing functions like row_number() (i assume you use sql 2005 or above)
Yeah i guess doing it with sql2005 would be a better choice. Because whithin the code it will have problems later on. so i guess having a good base within the database is better.Can you help me with the function if possible?
yup just do likeSELECT ROW_NUMBER() OVER (ORDER BY ID) AS Row_No,ID,NameFROM Yourtable
Oh thank you so much , i really appreciate it. I used it in a stored procedure and use it in my code and it works fine with me. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-06 : 07:59:03
|
But consider that the generated Row_No is only for display purpose and has no direct relation to the data in your table.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
JARON
Starting Member
9 Posts |
Posted - 2010-01-06 : 08:38:21
|
quote:
Originally posted by webfred
But consider that the generated Row_No is only for display purpose and has no direct relation to the data in your table.
No, you're never too old to Yak'n'Roll if you're too young to die.
Yeah i am aware of that. thanks |
|
|
|
|
Indentity Problem