theres no max have to increment at least till 500 thats all.
2009/11/24 Budi Sentosa <[email protected]> > 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] >>> >> >> > -- Thank You, Best Regards, Nuwan S. Silva 170, St. Anthony's Road, Moratumulla, Moratuwa, Sri Lanka. Mobile : +94 779 804 543 email : [email protected]
