have u got the last number / max number just add 1
On Tue, Nov 24, 2009 at 2:14 PM, vinay kumar <[email protected]>wrote: > use the max method for stdno and stdyr and one to it in sql > > > On Sun, Nov 22, 2009 at 6:27 PM, Nuwan Silva <[email protected]> wrote: > >> so i tried and tried and got this... >> >> // --- Calculations >> Student Details >> Registration Number --- >> >> string stdyr = DateTime.Now.Year.ToString(); >> stdyr = stdyr.Substring(2); >> >> string stdno = how can i increment this for every insertion? >> >> string stdid = ("T" + stdyr + "-" + stdno); >> >> as said i need T09-001..... T09-0123..... T09-0299.... >> >> >> >> 2009/11/21 Raghupathi Kamuni <[email protected]> >> >> DECLARE >>> @RowCount INT >>> >>> SET @RowCount = 999 >>> SELECT RIGHT('00' + CAST(@RowCount AS varchar(3)), 3) -- Prints 999 >>> >>> SET @RowCount = 1000 >>> SELECT RIGHT('00' + CAST(@RowCount AS varchar(3)), 3) -- Prints 00* >>> >>> On Fri, Nov 20, 2009 at 6:33 PM, Nuwan Silva <[email protected]> wrote: >>> >>>> >>>> CREATE PROCEDURE crttdID(@RowCount int, @StuNum varchar OUTPUT) >>>> AS >>>> BEGIN >>>> DECLARE @Dateof_Admission datetime >>>> >>>> SET @RowCount = (SELECT COUNT(1) from Student_Details where >>>> datepart(year, Dateof_Admission)= datepart(year, @Dateof_Admission)) >>>> SET @StuNum = (select 'T' + right (DATEPART(yy, current_timestamp), 2)+ >>>> '-' + RIGHT('00' + CAST(@RowCount AS varchar(3)), 3)) >>>> END; >>>> >>>> >>>> a put this in the sql file and tested it but only "T" will print >>>> >>>> and one more thing i need to insert the StudentID to the Student_Details >>>> table without creating another table >>>> >>>> >>>> >>>> 2009/11/20 Raghupathi Kamuni <[email protected]> >>>> >>>> select >>>>> RIGHT('00' + CAST(@RowCount AS varchar(3)), 3) >>>>> >>>>> Maximum students allowed per year 999 ONLY !! >>>>> >>>>> >>>>> On Fri, Nov 20, 2009 at 9:37 AM, rbr <[email protected]> wrote: >>>>> >>>>>> So, as you can see, there are many ways to solve this issue. I am >>>>>> assuming you are trying to generate the Student_ID >>>>>> on insert? A stored procedure would be best if possible. >>>>>> >>>>>> In that case, I would do something very similar to what Cerebrus >>>>>> recommended: >>>>>> >>>>>> Begin >>>>>> >>>>>> DECLARE @RowCount int >>>>>> DECLARE @StuNum varchar >>>>>> >>>>>> SET @RowCount = (SELECT COUNT(1) from Student_Details where datepart >>>>>> (year, Dateof_Admission)= datepart(year, @Dateof_Admission)) >>>>>> >>>>>> SET @StuNum = (select 'T' + right(DATEPART(yy, current_timestamp), 2)+ >>>>>> '-' + RIGHT('00' + CAST(@RowCount AS varchar(3)), 3)) >>>>>> >>>>>> INSERT Student_Details >>>>>> ( >>>>>> Student_ID, >>>>>> Fname, >>>>>> Lname, >>>>>> Full_Name, -- >>>>>> Address, >>>>>> DOB, >>>>>> ContactNO, >>>>>> email, >>>>>> additional_detail, >>>>>> Course_ID, >>>>>> Dateof_Admission, >>>>>> Dateof_Graduation >>>>>> ) >>>>>> VALUES >>>>>> ( >>>>>> @StuNum, >>>>>> @Fname, >>>>>> @Lname, >>>>>> @Full_Name, -- >>>>>> @Address, >>>>>> @DOB, >>>>>> @ContactNO, >>>>>> @email, >>>>>> @additional_detail, >>>>>> @Course_ID, >>>>>> @Dateof_Admission, >>>>>> @Dateof_Graduation, >>>>>> ) >>>>>> >>>>>> END >>>>>> >>>>>> Let me know if this workd for you. I'm sure we can figure something >>>>>> out. >>>>>> >>>>>> rbr >>>>>> >>>>>> On Nov 18, 9:12 am, Cerebrus <[email protected]> wrote: >>>>>> > Possibly something like : >>>>>> > >>>>>> > --- >>>>>> > SELECT MAX(SUBSTRING(Student_ID, 5, 4)) >>>>>> > FROM Student_Details >>>>>> > WHERE SUBSTRING(Student_ID, 2, 2) = @TwoDigitYear >>>>>> > --- >>>>>> > >>>>>> > Example param value: @TwoDigitYear = '09' >>>>>> > >>>>>> > This would give you something like '0118'. You then know that the >>>>>> next >>>>>> > registration number for that year should be '0119' (You could also >>>>>> > return the value incremented by one in SQL itself.) >>>>>> > >>>>>> > On Nov 18, 6:36 pm, nu1silva <[email protected]> wrote: >>>>>> > >>>>>> > >>>>>> > >>>>>> > > ok here we go again.... :( >>>>>> > >>>>>> > > asked this before an got some great answers but i'm still not >>>>>> able to >>>>>> > > get it right :( >>>>>> > >>>>>> > > first of all, >>>>>> > > im doin the project on Visual Studio 2005 C#.NET and using MSSQL >>>>>> > > 2005. >>>>>> > >>>>>> > > the objective is to register a student and save data on the DB. >>>>>> > >>>>>> > > what i want to do is create a custom Registration number or the >>>>>> > > students that register for a course >>>>>> > > examples, >>>>>> > > T(year)-(student number) >>>>>> > > so itll be like >>> T09-0118, T09-0001, T09-1234 >>>>>> > >>>>>> > > how to get the input>> >>>>>> > >>>>>> > > T >> we must ad that >>>>>> > > 09 >> should get from date >>>>>> > > - >> we add >>>>>> > > 0001 >> (where can i get that???) >>>>>> > >>>>>> > > and it would be great if i can create the Registration Number >>>>>> before i >>>>>> > > insert it to the database. >>>>>> > >>>>>> > > im still learning and would appreciate the help :) >>>>>> > > Thanks- Hide quoted text - >>>>>> > >>>>>> > - Show quoted text - >>>>>> >>>>> >>>>> >>>> >>>> >>>> -- >>>> Thank You, >>>> Best Regards, >>>> >>>> Nuwan S. Silva >>>> 170, St. Anthony's Road, >>>> Moratumulla, Moratuwa, >>>> Sri Lanka. >>>> Mobile : +94 779 804 543 >>>> email : [email protected] >>>> >>> >>> >> >> >> -- >> Thank You, >> Best Regards, >> >> Nuwan S. Silva >> 170, St. Anthony's Road, >> Moratumulla, Moratuwa, >> Sri Lanka. >> Mobile : +94 779 804 543 >> email : [email protected] >> > >
