Cycle through numbers in a database

[mrbungle]

I'm not sure how to tackle this problem. I have an app that has a range of numbers, like D100100 to D100200. Right now, it's all done with a text file, and what needs to happen is each document needs it's own number in sequence. They are DUI Citations. So, each citation needs it's own citation number. And right now, everything works fine- sort of.

The data from the citations are saved to MySQL. An officer can open the program, and either create a new citation (that needs it's own number next in the sequence) or open a previous citation from a case number (in case they need to make a correction and re-print the citation).

If you enter a case number and pull in an older citation thats in the database, it also pulls in the citation number- which is needed. Everything is fine there.

But when the citation that was pulled in is closed, my app currently takes it's citation number and saves it.

If you were currently on citation #100 for example, you would bring it (the citation form) up and it would advance to 101. BUT, when you wanted to go back and look at an old citation- say #75, then closed the program, the next time you opened it, it would open on citation #76, NOT #101 like it should. Thats bad.

So I'm wondering if cycling through these numbers in MySQL would be a better solution than using a text file. My logic is then you could close the form WITHOUT cycling through the next number needed. I'm not sure how to handle this. I've tried a few different things, and am really getting confused.

Any suggestions or comments?

[ndeniche]

Well, you could create a function in MYSQL that would return the next number in the database. That way, when you open a new citation, it will load the next number. If you look back to an old citation,once you close it and open a new one, just call the function and the next number should show up.

[mrbungle]

Yeah, that sounds good, and I thought of that. Does anyone have some sample code they could provide to cycle through the numbers? I'm not sure where to start.

[mrbungle]

Let me add more information- two text fields in the app- txtCiteStart.Text and txtCiteEnd.Text. In the DB, two columns- citestart and citeend. No problems adding those two sets of numbers to the DB. Not sure where to go from there... :(

[Reverend Jim]

In SQL, to select the largest value from a set of values you use the MAX function as in

select MAX(citation) from myTable

[mrbungle]

Perfect- thanks.