Really you don't need DR to be stored as it's a user convenience. That is you can add DR when extracting the value. You also don't need to check calculate the next number as SQlite can do this on your behalf.
To further elaborate if DrawingID is defined using INTEGER PRIMARY KEY the column is then an alias of the normally hidden rowid (if not a WITHOUT ROWID table (rarely used)).
When inserting a row and you don't sepcify a value for the column then a unique value is supplied. This is typically 1 greater than the highest with the first value as 1.
- However, there is no guarantee that the unique value will be 1 greater or that if when (if) you reached the maximum number of 9223372036854775807 (basically impossible due to storage device capacity) that a lower (but still unique) number will be assigned.
Using an alias of the rowid will be more efficient not only as the value stored is shorter (maximum of 8 bytes), it is stored in the most efficient (up to twice as fast as other indexes) index. Of course you then don't have the additional inefficiency of trying to replicate this inbuilt behaviour by So I need to split the string, convert it to an Integer and add +1. Then rebuild the ID and return it to the OnSave function.
As an example consider the following :-
/* Cleanup in case existing Environment exists */
DROP TABLE IF EXISTS Drawing;
/* The suggested Schema */
CREATE TABLE IF NOT EXISTS Drawing (DrawingID INTEGER PRIMARY KEY, Title TEXT, ProjectNumber INTEGER);
/* Add various drawings */
INSERT INTO Drawing (Title,ProjectNumber) VALUES
('Room1',1),('Room2',1),('Room1',3),('Room3',3),('Room3',1),('Room2',3),('Room1',2);
/* Extracting Project, Drawing Title and Drawing ID */
SELECT 'Project - '||ProjectNumber AS ProjectNumber, Title, 'DR'||printf('%07d',DrawingID) AS DrawingID FROM Drawing ORDER BY ProjectNumber,DrawingId;
/* Cleanup testing Environment */
DROP TABLE IF EXISTS Drawing;
The above results in :-
The actual data in the table being :-
- sorted as above i.e. extracted using
SELECT * FROM Drawing ORDER BY ProjectNumber,DrawingId;) :-
You may wish to have a read of
- SQL As Understood By SQLite - CREATE TABLE - ROWIDs and the INTEGER PRIMARY KEY
- SQLite Autoincrement
- (Noting that althogh this is a link to AUTOINCREMENT it explains why AUTOINCREMENT is best not used in addition to adding information)