Member Avatar for archelle

I really need some answer to resolve my problem. Here's the situation,
I wanted to create a student number using mysql database and vb.net in this form:

currentyear-(no. of student in record)

Student no. is auto generated.(the no. of student in record part)

Example:
I entered to school in the year 2006
I am student 141791
therefore my Student no. is 2006-141791.


Please let me know how to code it.
Tnx in advance.

  • 3 Contributors
  • 2 Replies
  • 247 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by M.Waqas Aslam

Dani AI

Generated

— the cleanest, most maintainable approach is to keep the year and the numeric sequence as separate, atomic columns in MySQL and build the printable student number when you query or in your VB.NET UI. That avoids duplication and makes searching, sorting and schema changes simple.

Example table (minimal):

CREATE TABLE students (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  year_enrolled YEAR NOT NULL
  -- other columns (name, dob, ...)
);

To produce the display value (e.g. 2006-141791) at query time, format it with SQL or in VB.NET so you can change presentation later without changing stored data:

SELECT CONCAT(year_enrolled, '-', LPAD(id, 6, '0')) AS student_no, *
FROM students
WHERE id = 141791;

If you want the numeric part to restart each year (so 2006-000001, 2006-000002, then 2007-000001), use a small counter table and an atomic update. This pattern is safe under concurrency:

CREATE TABLE year_counters (yr YEAR PRIMARY KEY, last INT UNSIGNED NOT NULL);

-- atomic increment (run on the same connection)
INSERT INTO year_counters (yr, last) VALUES (@yr, 1)
  ON DUPLICATE KEY UPDATE last = LAST_INSERT_ID(last + 1);
SELECT LAST_INSERT_ID();

A compact VB.NET flow (MySqlCommand) is: run the INSERT...ON DUPLICATE KEY UPDATE / SELECT LAST_INSERT_ID() on the same connection, read the returned sequence, then format:

Dim seq As Long = Convert.ToInt64(cmd.ExecuteScalar())
Dim studentNo As String = year.ToString() & "-" & seq.ToString("D6")

As and hinted, concatenation is the idea — but pick where to do it (SQL/view, a generated column, or in the app). Notes: prefer computed/generated columns (MySQL 5.7+) if you want the DB to expose the formatted value; add a UNIQUE index only if you must prevent duplicates; and always perform the sequence increment and retrieval on the same DB connection to avoid wrong LAST_INSERT_ID() values.

Recommended Answers

All 2 Replies

Member Avatar for ChrisPadgham

Presuming you hold year first enrolled in the Student table you are best to use a calculated field in a view that concatenates these to fields

YearEnrolled & '-' & StudentId AS StudentNo

Member Avatar for M.Waqas Aslam

i think u have to use the concatenation , of year and ur auto grnated number then insert it in db .

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.