Delete Record Arrange Records

[Ali Rizwan]

Hi all,
I have a database and i have deleted a record.
Suppose
I have records 1,2,3,4,5,6,7
And i delete no 5 now my record will appear 1,2,3,4,6,7
But i want to rearrange them by 1,2,3,4,5,6
Thanx

[code937]

just arrange them in number order then go back to the beginning, start from 1 and re-number them from 1 to x until you get to the end of the records. Are you using a data control with a db? .. if so ill chuck up an example

[Ali Rizwan]

just arrange them in number order then go back to the beginning, start from 1 and re-number them from 1 to x until you get to the end of the records. Are you using a data control with a db? .. if so ill chuck up an example

Thanx
But can you give me an example how can i do this .
I m using vb6 and ADODC
thanx

[code937]

Assuming a couple of things ...

 
'Delete the record you want then ...
dim cc as integer
dim SQL as string
 
SQL = "SELECT * FROM tablename ORDER BY fieldname ASC"
datdata.recordsource = SQL
datdata.refresh
 
for i = 1 to datdata.recordset.recordcount
cc = cc + 1
datdata.recordset.edit
datdata.recordset!fieldname = cc
datdata.recordset.update
datdata.recordset.movenext
next i
 
 

[jrtox]

Hello,

That will do changing, but be sure that it wont affect the other records.
In long Run, when your Database has may Records and do Re-numbered, you've got what i mean.

[Ali Rizwan]

Hello,

That will do changing, but be sure that it wont affect the other records.
In long Run, when your Database has may Records and do Re-numbered, you've got what i mean.

It would effect on other records.
Because my app is a school DataBase system. And all the regestration forms are saved withe the originals but when i delete then it wil clash with the regestration form enteries.
I m doing this because my database assigns the rollno and regno automatically by using this code.

ADATA.Refresh
txtREG.Text = ADATA.Recordset.RecordCount + 1
ADATA1.Refresh
txtROLL.Text = ADATA1.Recordset.RecordCount + 1

[Ali Rizwan]

Hello,

That will do changing, but be sure that it wont affect the other records.
In long Run, when your Database has may Records and do Re-numbered, you've got what i mean.

It would effect on other records.
Because my app is a school DataBase system. And all the regestration forms are saved withe the originals but when i delete then it wil clash with the regestration form enteries.
I m doing this because my database assigns the rollno and regno automatically by using this code.

    ADATA.Refresh
    txtREG.Text = ADATA.Recordset.RecordCount + 1
    ADATA1.Refresh
    txtROLL.Text = ADATA1.Recordset.RecordCount + 1

Have you any other code to assigning the roll and reg.

suppose i have records
1,2,3,4,5,6
if i delete a student which is rolled 5 .
By using my code when we add a student after deleting it will assign 6 to the new student but there is also a student rolled 6. Now it will make duplicate items and occurs error because roll is primary.
Can you solve this problem.
Thanx

[jrtox]

It would effect on other records.
Because my app is a school DataBase system. And all the regestration forms are saved withe the originals but when i delete then it wil clash with the regestration form enteries.
I m doing this because my database assigns the rollno and regno automatically by using this code.

ADATA.Refresh
txtREG.Text = ADATA.Recordset.RecordCount + 1
ADATA1.Refresh
txtROLL.Text = ADATA1.Recordset.RecordCount + 1

Well, Its up to you.

You delete the primary then it affect the foreign key and the records connected to the primary key.

[Ali Rizwan]

Well, Its up to you.

You delete the primary then it affect the foreign key and the records connected to the primary key.

The question is How can i assign roll automatically with no logicall error.
Plz help.

[jrtox]

The question is How can i assign roll automatically with no logicall error.
Plz help.

Please let me know your tables, and what is the relation between your "Roll" and "Regno".?

[Ali Rizwan]

Please let me know your tables, and what is the relation between your "Roll" and "Regno".?

Ok
forget about reg no.
I have a table named one (class one). in this table all students studying in class one are kept. Columns are RollNo, Name, Father;s Name.
Roll no has primary key.So that it cannot be duplicate.
I want to assign rollno of new student automatically.
Thanx

[jrtox]

Ok
forget about reg no.
I have a table named one (class one). in this table all students studying in class one are kept. Columns are RollNo, Name, Father;s Name.
Roll no has primary key.So that it cannot be duplicate.
I want to assign rollno of new student automatically.
Thanx

Your Rollno is your primary key, right?
ley say you have Records like

RollNo Name Address
1 Ervin Philipines
2 Jay-ar Philipines
3 Peter Philipines
4 Paul Philipines

WHERE RollNo is the Primary key.

Now, you delete Record RollNo(3) Name(Peter) Paul(Philipines)

Now the Records will be

RollNo Name Address
1 Ervin Philipines
2 Jay-ar Philipines
4 Paul Philipines


for that, you cant change RollNo(4) to RollNo(3) because its a primary key.

in short you cant Re-number the primary key...

if you delete all that record then the next record will be RollNo(5).


This is just my opinion for now, i dont if its really is.


And one thing, re-numbering for me is not advisable.
Why? let say you have thousand of records, then i delete record number 2 and you do renumbering by used of Looping, then it will do Loop thousand times.
the question is, IS your System are Strong enough to Loop thousand times?

Regards
Ervin

[Ali Rizwan]

Why? let say you have thousand of records, then i delete record number 2 and you do renumbering by used of Looping, then it will do Loop thousand times.
the question is, IS your System are Strong enough to Loop thousand times?

Regards
Ervin

Mine is but the person to whom for i m makeing is not too musch powerfull so that it ll be loop for thousand times.
If i move to the last record and then add 1 to the last record to make new entry it ll make sense but sometimes database sort the files
1
10
2
3
4
5
6
7
8
9
and if do then it will make entry with Sr 10 which is already made.
How can i avoid such error.
One thing more if i use a code which finds the biggest number in the data and then made entry by adding 1 in the entry.
Hows the idea.
If yes then plz tell me how can i found the biggest no.
Thanx
Ervin

[QVeen72]

Hi,


use this query:

Select Max(Val(RollNo)) As MaxRollNo From MyTable

Provided, RollNo does not contain any varchar value..

REgards
Veena

[jrtox]

Mine is but the person to whom for i m makeing is not too musch powerfull so that it ll be loop for thousand times.
If i move to the last record and then add 1 to the last record to make new entry it ll make sense but sometimes database sort the files
1
10
2
3
4
5
6
7
8
9
and if do then it will make entry with Sr 10 which is already made.
How can i avoid such error.
One thing more if i use a code which finds the biggest number in the data and then made entry by adding 1 in the entry.
Hows the idea.
If yes then plz tell me how can i found the biggest no.
Thanx
Ervin

To arrange:
Select RollNo, Name, FathersName From MyTable ORDER BY RollNo ASC

To choose the biggest number:
just read post reply of QVeen

Regards
Ervin

[Ali Rizwan]

Hi,


use this query:

Select Max(Val(RollNo)) As MaxRollNo From MyTable

Provided, RollNo does not contain any varchar value..

REgards
Veena

Hello,
Thanks Veena