how to generate unique ID based on combobox selection?

[pandeysk_13]

hello world,

i am trying this from last 2 days but unsuccessful .

i am trying to make a project in visual basic 2010 which is connected with ms access 2010 database. the database having 4 tables i.e. tblsession, tblcourse, tbllevel, tblstudentregistration.

tblsession data:

column name column data

sessionID -- 01

sessionname -- 2012

tblcourse data:

courseID courseName

01 --- Ab

02 --- Ac

03 --- Ad

tbllevel data:

levelID LevelName

01 --- I

02 --- II

03 --- III

tblstudentregistration column:

studentID StudentName and so on.......................

i have a form in visual basic 2010 which is having

comboboxsession - the data comes form tblsession

comboboxcourse - the data comes form tblcourse

comboboxlevel - the data comes form tblcourse

txtstudentID - here i want to generate an unique ID for all students so that i can save all students data in one table.

it should be like this:

if i select 2012 in comboboxsession and if i select Ac in comboboxcourse and if i select II in comboboxlevel then

the txtstudentID must generate and unique ID like this "01020200001" where the first "01" should form sessionID second "02" from courseID third "02" from levelID and last "00001" must be generated from the Top ID in this pattern in the tblstudentregistration table. it should auto increment every time i click ADD button and selecting all comboboxes.

if i change the comboboxes selection like this

comboboxsession = 2012

comboboxcourse = Ad

comboboxlevel = III then

txtstudentID must show studentID like "201030300001" and if this ID already exist then it should show "2012030300002"

this is the thing i want.

please provide my some logical suggestion and if possible a code.

kindly help me for this.

thanks in advance

[Doogledude123]

Dim studentID as Long

Private Sub StdntIDGen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles StdntIDGen.Click
        studentID = sessionID.Text + courseID.Text + levelID.Text + tmpID.Text
End Sub

tmpID.text would be a text box that increases by 1 each time the button was clicked.

[pandeysk_13]

ih doogledude,
thenks for response.
i am a new bee for visual basic 2010 and ms access database but trying to learn maro and more with the help of you all experts
as ur code saying:

studentID = sessionID.Text + courseID.Text + levelID.Text + tmpID.Text

can u explain me, how can i apply and where to apply.
it will be very helpful for me.

at present i am useing this code for filling comboboxes:

 Private Sub loadsession()
        Dim str As String = "select sessionId, sessionName from tblsession"
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(str, conn)
        Dim dt As New DataTable
        da.Fill(dt)
        Dim bs As BindingSource = New BindingSource
        bs.DataSource = dt
        cmbsession.DataSource = bs
        cmbsession.DisplayMember = "sessionName"
        cmbsession.ValueMember = "sessionId"
        txtstudentid.Text = ""
        Me.cmbsession.SelectedValue = -1
    End Sub

..
Private Sub loadcourse()
    Dim str As String = "select courseId, courseName from tblcourse"
    Dim da As OleDbDataAdapter = New OleDbDataAdapter(str, conn)
    Dim dt As New DataTable
    da.Fill(dt)
    Dim bs As BindingSource = New BindingSource
    bs.DataSource = dt
    cmbcourse.DataSource = bs
    cmbcourse.DisplayMember = "courseName"
    cmbcourse.ValueMember = "courseId"
    txtstudentid.Text = ""
    Me.cmbcourse.SelectedValue = -1
End Sub
...
Private Sub loadlevel()
    Dim str As String = "select levelId, levelName from tbllevel"
    Dim da As OleDbDataAdapter = New OleDbDataAdapter(str, conn)
    Dim dt As New DataTable
    da.Fill(dt)
    Dim bs As BindingSource = New BindingSource
    bs.DataSource = dt
    cmblevel.DataSource = bs
    cmblevel.DisplayMember = "levelName"
    cmblevel.ValueMember = "levelId"
    txtstudentid.Text = ""
    Me.cmblevel.SelectedValue = -1
End Sub

the form load with this code:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call connectDatabase()
        loadsession()
        loadcourse()
        loadlevel()
        Call DisconnectDatabase()
    End Sub

the text boxes are changing with this code:
...

     Private Sub cmbsession_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbsession.SelectedIndexChanged
        If (Not Me.cmbsession.SelectedValue Is Nothing) Then
            Me.txtstudentid.Text = Me.cmbsession.SelectedValue.ToString
        End If
     End Sub

.....

    Private Sub cmbcourse_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbcourse.SelectedIndexChanged
        If (Not Me.cmbcourse.SelectedValue Is Nothing) Then
            Me.txtstudentid.Text = txtstudentid.Text + Me.cmbcourse.SelectedValue.ToString
        End If

End Sub

.....

    Private Sub cmblevel_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmblevel.SelectedIndexChanged
        If (Not Me.cmblevel.SelectedValue Is Nothing) Then
            Me.txtstudentid.Text = txtstudentid.Text + Me.cmblevel.SelectedValue.ToString + GenerateID()
        End If
    End Sub

the id is generating with this code:
.....

Private Function GenerateID() As String
        Dim value As String = "0000"
        Dim studentID As String = Nothing
        Try
            ' Fetch the latest ID from the database
            con.Open()
            mycmd = New OleDbCommand("SELECT TOP 1 studentID FROM tblstureg ORDER BY studentID DESC", Con)
            mydr = mycmd.ExecuteReader(CommandBehavior.CloseConnection)
            If mydr.HasRows Then
                mydr.Read()
                value = mydr.Item("studentID")
            End If
            mydr.Close()
            'Increase the ID by 1
            value += 1
            ' Because incrementing a string with an integer removes 0's
            ' we need to replace them. If necessary.
            If value <= 9 Then 'Value is between 0 and 10
                value = "0000" & value
            ElseIf value <= 99 Then 'Value is between 9 and 100
                value = "000" & value
            ElseIf value <= 999 Then 'Value is between 999 and 1000
                value = "00" & value
            End If
        Catch ex As Exception
            ' If an error occurs, check the connection state and close it if necessary.
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            value = "0000"
        End Try
        Return value
    End Function

but this is all confussing because at first time it generate id ok then
again after clicking ADD button it is making hochpoch for me.

please help me for this
thanks again for response.

[poojavb]

Just an info....

Actually ur sql query wont help u....
if u select the first "01" from sessionID , second "02" from courseID , third "02" from levelID then the database would store something like 01020200001

suppose u have selected the first "01" from courseID and when u run the query the value returned will be 01020200001 and this value will be added by 1,then it would return something else which will create a chaos....

[G_Waddell]

Hi,
It maybe easier to do the following:

In your database student table, insert a primary key called id and a second column called studentID the first column should be an autoincrementing field. So that when a new student is created they will have a unique ID. The second column will be a text column, you will put your "generated ID" into here.

Have a second table (call it something like pupil_count,) that has a session, course, level and students column. The students column will contain the number of students taking the course and the rest will be foriegn keys linking to their respective tables.

Now when a new student picks the course your insert query will insert whatever the values you want for your student are and then you return the primary key (id). Next check in your second table against the session, course, level, values to get the students value. If you get nothing back assume no one has yet signed up. Take this number increment it by one (that is the new number you are tacking on the end of your new generated ID) and update the students column to the new number for this session, course, level combo.

Now put together your new StudentID and update the value on the student record where the ID is the one you got earlier.