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]
>
