auto number field

[jrosh]

i created a table with a files with auto number in mysql. i added 15 raws of data
i deleted some added data (say 6-15)

when i add data again, auto number starts with 16. how can i make it 6?

(i hope u get the point)

[roswell67]

show us your code, so we can help =).

If you don't want to share code, try reseting your auto-numbering before you add data again.

[jrosh]

show us your code, so we can help =).

If you don't want to share code, try reseting your auto-numbering before you add data again.

how can i do that.. but if the auto number goes to the initial value(i mean 0) it will not going to help in here anyway....

i tested it through mysql console...using normal sql syntax.
i want to be sure about how to do this before startin...:-)

[roswell67]

I have not worked with mysql before, so i dunno.

But I don't perfectly understand what you're trying to say, so if you would like to receive help. Show us some code or anything that could explain what your trying to do and what the problem exactly is.

[jrosh]

i dnt knw wat code to show!!!

here is my insert code....

c.insertDetails("insert into room(jobNumber,drawingnumber,Component,Tiles,Color,Chair,dTable) values('"+jno.getText()+"','"+dno.getText()+"','"+component.getText()+"','"+Tiles.getSelectedItem()+"','"+Color.getSelectedItem()+"','"+chair.getSelectedItem()+"','dtable.getSelectedItem()')");

insertdetail <my method that connect the database..(with a sql query as the argument)

values are taken from textboxes in user interface(a form)

thats it. i add 16 records... auto number fileds fills upto 1-16
then i deleted records from 6-16 through mysql console....

delete from room where indexnumber > 5;

I insert values again with previous insertDetails method..

the database has numbers as 1 2 3 4 5 17 18...... in indexnumber filed which i set as autonumber when creating the table.

i want to have that indexnumbers proceed as 1 2 3 4 5 6... when i re enter the records

is dat clear enough...
thanx in advance 4 helping....

[masijade]

I'm sorry, but, this is a MySQL question, not a Java question. Go to www.mysql.org and find, and ask at, the forums there.

If you want to solve it with Java, the only thing you can do is to read all the keys, finding the missing numbers (i.e. 1 and 3 are used but 2 is not) and fill those values, as well as the continuing indexes (as that will not show what the last one used was) manually.

The MySQL documentation (of which there is a lot) may give some indication of how to reset the counter, though.

[roswell67]

hmm, too bad am not good with mysql.

But The problem may be, is that when you delete the records, the pointer for the next field to add is still at 16 and all you need to do, is insert in field 6 for example. probably you need to google that.
"how to manually insert in a field" or " how to override a field " that could give you an idea about the auto numbering in my opinion.

Sorry, tried my best =).

[jrosh]

hmm, too bad am not good with mysql.

But The problem may be, is that when you delete the records, the pointer for the next field to add is still at 16 and all you need to do, is insert in field 6 for example. probably you need to google that.
"how to manually insert in a field" or " how to override a field " that could give you an idea about the auto numbering in my opinion.

Sorry, tried my best =).

ok thankx a lot..

[masijade]

But The problem may be, is that when you delete the records, the pointer for the next field to add is still at 16

Exactly.

and all you need to do, is insert in field 6 for example.

Yes, as already said, you can manually insert into (or update) this field, but that does not reset the counter.

probably you need to google that.
"how to manually insert in a field" or " how to override a field " that could give you an idea about the auto numbering in my opinion.

No, I'm sorry, but his best bet, as already said, is to read the manual (again if need be) first, then to ask (using a forum search there first) at the forums (and/or a mailing list) at www.mysql.org

[jrosh]

I'm sorry, but, this is a MySQL question, not a Java question. Go to www.mysql.org and find, and ask at, the forums there.

If you want to solve it with Java, the only thing you can do is to read all the keys, finding the missing numbers (i.e. 1 and 3 are used but 2 is not) and fill those values, as well as the continuing indexes (as that will not show what the last one used was) manually.

The MySQL documentation (of which there is a lot) may give some indication of how to reset the counter, though.

ok i'll c 4 mysql then.. i was tryin to solve it though java.. it seems little aukwrd no? thankx any way

[masijade]

ok i'll c 4 mysql then.. i was tryin to solve it though java.. it seems little aukwrd no? thankx any way

The "solution" may be executing a specific SQL statement. In which case you can still use Java to execute it (even automatically within the life of an application), but Java cannot solve it.

Edit: Then again, the answer may be that you can't. That you would have to "recreate" the counter and repopulate the table with new values.

[roswell67]

hehe masijade, sorry when i was writing my post, didn't see yours til after i posted =).