Creating a custom registration number...

[nu1silva]

hi all,

im developing a Student Management software and im just going through
the registration of a student.

i need to assign the registration number --> T(year)-(# of
registration for the year) so it'll be like
T09-001 or T09-089 or T09-243 an so on....

i get the input of the year and can count the number from the
database.

how can i get the registration number?

the sample o the Student Database...

CREATE TABLE Student_Details
(
Student_ID varchar(10) NOT NULL UNIQUE, -- example ( T09-001 )
Fname varchar(255),
Lname varchar(255),
Full_Name varchar(255), --
Address varchar(255),
DOB datetime,
ContactNO varchar(15),
email varchar(25),
additional_detail varchar(255),
Course_ID varchar(10),
Dateof_Admission datetime,
--Dateof_Graduation datetime,
PRIMARY KEY(Student_ID)
)


help!!! :(

[Nuwan Silva]

yes i figured that thing i just want to know how i will be able to do that?

2009/11/17 nu1silva <nu1s...@gmail.com>

--
Thank You,
Best Regards,

Nuwan S. Silva
170, St. Anthony's Road,
Moratumulla, Moratuwa,
Sri Lanka.
Mobile  : +94 779 804 543
email    : nu1s...@gmail.com

[Raghupathi Kamuni]

May be you have to maintain the Registration numbers in another table say....

CREATE

TABLE [dbo].[Registration](
[Year] [int] NOT NULL,
[RegistrationNumber] [int] NOT NULL
)

Based on the year, you have to autoincrement RegistrationNumber.

While inserting Student_Details generate the Student_ID from RegistrationNumber

Hope that helps !
 

[B☼gus Excepti☼n]

And perhaps a SP to put them together when inserting?

[Nuwan Silva]

yeah that might be a good idea.

but,

You could always use String.Format("T:{0}-{1:3}",Date.Now.Year.ToString

().Substring(2,2)

, SomeSQLRowcountFunction(student_details) )

this i what i wanted maybe i can work with something like this.

thanks guys :) sry if you couldn't understand what i wanted anyway i got a clue how to do it thanks :)

2009/11/18 Raghupathi Kamuni <raghu...@gmail.com>

[nu1silva]

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

[Cerebrus]

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.)

[rbr]

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

> > Thanks- Hide quoted text -
>
> - Show quoted text -

[Raghupathi Kamuni]

select

RIGHT('00' + CAST(@RowCount AS varchar(3)), 3)

 

Maximum students allowed per year 999 ONLY !!

[Nuwan Silva]

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 <raghu...@gmail.com>

[Raghupathi Kamuni]

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*

[Nuwan Silva]

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 <raghu...@gmail.com>

[vinay kumar]

use the max method for stdno and stdyr and one to it in sql 

[Budi Sentosa]

have u got the last number / max number

just add 1

[Nuwan Silva]

theres no max have to increment at least till 500 thats all.

2009/11/24 Budi Sentosa <b.budi....@gmail.com>

[Budi Sentosa]

At first the row is 0
When the row is 0. U have to set the number to T09-001

When the second student register
U take the max number in db. Which u got number 1

U just add 1. So the number become 2 = T09-002

And so on

On Nov 25, 2009 12:32 PM, "Nuwan Silva" <nu1s...@gmail.com> wrote:

theres no max have to increment at least till 500 thats all.

2009/11/24 Budi Sentosa <b.budi....@gmail.com>

> > have u got the last number / max number > > just add 1 > > > On Tue, Nov 24, 2009 at 2:14 PM, v...

-- Thank You, Best Regards, Nuwan S. Silva 170, St. Anthony's Road, Moratumulla, Moratuwa, Sri ...

[Peter Smith]

On Wed, Nov 25, 2009 at 1:58 AM, Budi Sentosa <b.budi....@gmail.com> wrote:

At first the row is 0
When the row is 0. U have to set the number to T09-001

When the second student register
U take the max number in db. Which u got number 1

U just add 1. So the number become 2 = T09-002

And so on

Until someone drops out, and this simple solution decides to delete the record.