Generate User Id in VB.net and access

PRAVIN FRANCIS 0
Viewed 43

What is the code for auto generating the user id in VB.net which has the database in MS Access.

Imports System.Data.OleDb
Imports System.Data
Imports System.Web.UI
Imports System.Web.UI.WebControls

Partial Class UserRegister
    Inherits System.Web.UI.Page

    Dim con As OleDbConnection
    Dim cmd As OleDbCommand

    'Database Connection
    Public Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
        Try
            Dim num As Integer = 0
            Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = D:\Pravin\Project\Online Complaint\Online Complaint Registration\Database\OCRS.mdb"
            con = New OleDbConnection(ConStr)
            con.Open()
            Dim a As String = "select max(u_id) from User"
            cmd = New OleDbCommand(a, con)
            cmd.Connection = con
            If (IsDBNull(cmd.ExecuteScalar)) Then
                num = 1
                txt9.Text = "UID" + num.ToString
            Else
                num = cmd.ExecuteScalar + 1
                txt9.Text = "UID" + num.ToString
            End If
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

I tried this code it gives me the error "syntax error FROM clause". I don't have idea what to do...

ms-access vb.net
1 Answers

Gee, this is a bit confusing.

You in general have two types of numbers for a table.

In general, most tables REALLY should and will have a autonumber ID column. When you insert a row, this primary key value will automatic increment for you. As noted, all such tables should have this PK auotnumber.

But those numbers are for generating a PK value.

Then you might have say a invoice system in which each new invoice created NEEDS by control of the developer a incrementing number. And you often want to set the next invoice number. So, in this case, you would create + use a table - often with just ONE row and ONE column with that number to use for the next invoice.

so, say we have a table of users, like this:

Table Design: enter image description here

And with some data, like this:

enter image description here

So, if we are to add a new user, then right after we insert the new row, then we can grab/get/use/see the new PK id assigned to that new row of data.

Our code for this will look like:

So, say this simple markup like this:

        <div style="width: 20%; text-align: right; border: solid 2px; padding: 15px; border-radius: 15px">
            <p>
                <label font-size="large">First Name: </label>
                <asp:TextBox ID="txtFirst" runat="server"></asp:TextBox>
            </p>
            <p>
                <label font-size="large">Last Name: </label>
                <asp:TextBox ID="txtLast" runat="server"></asp:TextBox>
            </p>
            <p>
                <label font-size="large">Email: </label>
                <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
            </p>
            <asp:Button ID="cmdAdd" runat="server" Text="Add" CssClass="btn" Style="float: left" />
            <br />
            <asp:Label ID="lblInfo" runat="server" Text="Label" Font-Size="Large"></asp:Label>
        </div>

Code behind is this:

Protected Sub cmdAdd_Click(sender As Object, e As EventArgs) Handles cmdAdd.Click

    Dim strSQL As String =
        "INSERT INTO tblUsers (FirstName, LastName, Email)
         VALUES (@First, @Last, @Email)"

    Using conn As New OleDbConnection(My.Settings.AccessDB)
        Using cmdSQL As New OleDbCommand(strSQL, conn)
            conn.Open()

            With cmdSQL.Parameters
                .Add("@First", OleDbType.VarWChar).Value = txtFirst.Text
                .Add("@Last", OleDbType.VarWChar).Value = txtLast.Text
                .Add("@Email", OleDbType.VarWChar).Value = txtEmail.Text
            End With
            cmdSQL.ExecuteNonQuery()

            ' now get auto number generated
            cmdSQL.Parameters.Clear()
            cmdSQL.CommandText = "SELECT @@IDENTITY"
            Dim pk As Integer = cmdSQL.ExecuteScalar
            lblInfo.Text = "new user PK id = " & pk
        End Using
    End Using

End Sub

And we now get/see this:

enter image description here

so, the above is how you typical get the new "autonumber" PK row id assigned to that new row you just added.

However, as noted, perhaps this some type of invoice number, badge number, or whatever. In that case, we would STILL in most cases have the PK number, but we could say assign a incrementing Badge number to the above user - say for a trade show or whatever.

And say the badge numbers are to start at say 1000, and increment by 10 for each new badge number.

So, we would add a new column to tblUsers (badge number). And then create a new able called NextBadgeNumber, and it would have one column. We would get that value, increment by 1 (or by 10 or whatever we want). Update badge number table, and then use this new badge number in our above save code.

You will note that as a general rule, we don't need to use "max()" or some such, but simple use @@Identity to get the last new autonumber PK id generated.

edit: I see this was tagged VB.net and not web (asp.net).

However, the code stub posted should work much the same, and still shows how to get the last autonumber PK id.

Albert D. Kallal0
Related