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]
