Concatenation and auto number generation

[Ruchi224]

Hello !! I have a windows form having combobox and textbox controls.. I have to generate a part number, consisting of combobox and textbox values included.. I can explain in detail..

This is the code i have used to define a variable "type"..

If ComboBox1.SelectedIndex = 0 Then
            type = "ME"
        End If

        If ComboBox1.SelectedIndex = 1 Then
            type = "EL"

        End If
        If ComboBox1.SelectedIndex = 2 Then
            type = "ER"
        End If

        If ComboBox1.SelectedIndex = 3 Then
            type = "CO"
        End If

        If ComboBox1.SelectedIndex = 4 Then
            type = "SW"
        End If

        If ComboBox1.SelectedIndex = 5 Then
            type = "MS"
        End If

        If ComboBox1.SelectedIndex = 6 Then
            type = "OT"
        End If

Similarly i have defined variable "role"

If ComboBox2.SelectedIndex = 0 Then
            role = "P"
        End If

        If ComboBox2.SelectedIndex = 1 Then
            role = "I"
        End If

        If ComboBox2.SelectedIndex = 2 Then
            role = "A"
        End If

Also, location is defined by

Dim loc As String = textBox5.Text + "  " + textBox6.Text + "  " + textBox7.Text

now.. I want a part number to be generated, which is concatenation of : type, role, loc and number (0001)..
Also, the last part of number should increment, everytime the user adds a new part eg 0002,0003 etc..
This is what i tried.. And i have placed it in submit button click event..

Try
            'MsgBox("Open")
            cn = New OleDbConnection("Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            cn.Open()
            msg = "Select max(partnum) from partno"
            cmd = New OleDbCommand(msg, cn)
            str = cmd.ExecuteScalar()

        Catch myException As Exception

            MsgBox("No Record Inserted" + myException.ToString())

        Finally
            'MsgBox("Closing Connection")

            cn.Close()
        End Try

        'If str Is DBNull.Value Then
        '    Dim intregnum As Integer = Convert.ToInt32(str)
        '    str = 1
        'Else
        '    str = str + 1
        'End If

        'textBox1.Text = str

        If str Is DBNull.Value Then
            pn = "1"
        Else
            pn = (Convert.ToInt32(str) + 1).ToString
        End If

        pn1 = type + "" + role + "" + loc + "pn"

After this, is my insert query for database, that query is executed.. But on inserting, i just get value "1" in my database, column name "partnum"..

Please help !! Thank you.. :)

[Ruchi224]

done, anyways..