Last post Oct 14, 2010 05:48 AM by vishakpb
Sep 30, 2010 06:38 AM|mamun22s|LINK
We have developed a financial solution. This solution is a web based asp.net using C#. we use oracle as database. 10+ user use the solution from different location. In this solution we need some serial number like sales entry. In each sales entry make a
sequential serial number like 1,2,3…Currently we use oracle sequencer to maintain the serial number. In our code we get the sequence number from oracle database then insert the sales information in the database. But the problem is if there is any error in
insert the insert method is role back and the sequence is missed. At the end of the day we get some of the number is not present in the database. Like if there is 1000 sales in the day, sequence goes to 1003. We need such a solution that solves my problem.
Please help us.
Sep 30, 2010 07:45 AM|cookie_powered|LINK
I know in SQL you could use somthing like:
SELECT MAX(CAST(Invoice_num AS int)) as highest_num FROM tbl_Name"
not sure if that is any use?
You would have to change your column from auto increment though or add additional column.
Sep 30, 2010 08:04 AM|mamun22s|LINK
Thanks cookie_powered for your quick reply.
It’s a problem using Max+1 sql because I alredy says I have 10+ user and they concurrently insert data. When more then one user concurrently inserts this sql return same data. Like I want to insert it will return say 101 in the same time another user also
get 101. But it will make an error on insert because of unique key.
Sep 30, 2010 09:10 AM|cookie_powered|LINK
In that case can't you insert with the max stament in a single execute? this would take care of the duplication issue?
Sep 30, 2010 09:31 AM|cookie_powered|LINK
This may be of help especially the WHERE NOT EXISTS, you could loop the stament.
Oct 04, 2010 02:52 AM|mamun22s|LINK
Can any one please solve my problem. Its urgent
Oct 11, 2010 02:32 AM|sirdneo|LINK
Max+1 is not a good techinque to generate serial numbers. You need to use Oracle's sequence to generate unique numbers. FOr details see this:-
Oct 14, 2010 05:48 AM|vishakpb|LINK
CREATE SEQUENCE "test_SEQ" MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1
START WITH 1 NOCACHE NOORDER NOCYCLE
CREATE OR REPLACE TRIGGER "test_TRG"
insert on "test_table"
for each row
SELECT test_SEQ.nextval INTO :NEW.test_id FROM dual;
ALTER TRIGGER "test_TRG" ENABLE
oracle auto increment