March 23, 2002 at 5:31 am
hello again!!
im not sure whether i can acoomplish my problem using database or ASP ascript.
what is required is as follows
i have a field called serialno.it has to be unique FOR EACH DAY and should be auto generated everyday.
Whenevr i perform an insert using ASP,it shud be checked it its a new day and the serialno should be auto generated and incremented for that particular day.For the next day the serial number should again start from 1.
i am using SQL Server 2000 and ASP version 3.0.Pls help.
Thanks in advance.
March 29, 2002 at 2:29 am
this should get you started...
select isnull(max(id),0)+1
from tb
where convert(varchar,dtcol,112)=convert(varchar,getdate(),112)
--
-oj
March 29, 2002 at 8:37 am
I suggest you do the insert by calling a stored procedure from ASP. Your stored procedure will then select the record with the most recent serial number (as OJN suggests). then test the timestamp of that record against GETDATE() using the DATEDIFF keyword. DATEDIFF is only interested in whole days (it ignores the time portion) so if the Timestamp of your record is today as defined by GETDATE() then you'll have a positive number. Test for that and restart your serial number accumulation. You can then supply the values for the INSERT statement.
Here's an example of DATEDIFF
DECLARE @DATE1 DATETIME
SET @DATE1 = '20020328'
SELECT DATEDIFF(dd,@DATE1,GETDATE())
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
April 1, 2002 at 3:35 pm
I was working on a Sample I forgot to bring home, but the only way could think of is similar to an old trick in Access. Create a table with two columns
CREATE TABLE AutoNum (
[dateval] [int] NOT NULL,
[idval] [int] NOT NULL
)
Then use CONVERT(VARCHAR,GETDATE(),112) to populate dateval as YYYYMMDD and set idval = 1.
Now each create a stored procedure to get both vals from the table. It will need to perform a select (locking the table exclusively) then updates the value of idval = idval + 1. It then releases the table for the next val to be given out.
Now the procedure should also check the if dateval = CONVERT(VARCHAR,GETDATE(),112) and if not update the dateval to the new value and reset idval to 2 (it is going to keep 1 as its output) and thus it should take care of itself.
Just a note, you will have to lock the table to prevent possible conflicts which will slow processing down as the lock is granted and dropped.
As if you do not do any processing at night or want to try instead. On the set dateval to new day and idval to 2 do not put that in a stored procedure but in a job that runs at midnight every night to roll to next day val and instead reset idval to 1.
Hope this helps. If you need an example it will be a fews days beforee I can find time to finish.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 8, 2002 at 8:01 pm
I would definitely go the job route. Set up a job to reset the table once per day. Then it's a simple matter of grabbing today's serial number from the table before you give the page to the user.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply