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 -
