none
NO SUCCESS AUTO COUNT ID RRS feed

  • Question

  • I face one issue is when my program set auto count id. my id can not auto cunt to next number. is i missing something.

    Private Sub getNextNumber()
            If Not app.State = ConnectionState.Open Then
                'open connection
                app.Open()
            End If

            Dim da As New OleDb.OleDbDataAdapter("select top 1 ID from ADMIN order by ID desc;", app)
            Dim dt As New DataTable
            'fill data to datatable
            da.Fill(dt)
            app.Close()

            If dt.Rows.Count > 0 Then
                txtID.Text = (Val(dt.Rows(0)(0)) + 1).ToString.PadLeft(10, "A")
            Else
                txtID.Text = "1".PadLeft(10, "A")
            End If
        End Sub

      Private Function getLastNumber() As Integer

            Dim app1 As New OleDb.OleDbConnection
            app1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=MYX.accdb"
            If Not app1.State = ConnectionState.Open Then
                app1.Open()
            End If

            Dim da As New OleDb.OleDbDataAdapter("select top 1 ID from ADMIN order by ID desc;", app1)
            'desc = sort the data returned in descending order
            Dim dt As New DataTable

            da.Fill(dt)
            app1.Close()

            If dt.Rows.Count > 0 Then
                Return Val(dt.Rows(0)(0))
            End If
            Return 0
        End Function
    • Edited by christing Thursday, August 1, 2019 9:31 AM
    Thursday, August 1, 2019 9:17 AM

All replies

  • What type is your ID. 

    If it is an automatic identifier in your Access database. Then you cannot set it from the datatable. If you create a row, then automatically an identifier with a negatif value is created. At update time that is replaced by the real identifier which Access has given. 

    Be aware that a database is mostly multi user and 2 persons can not give at the same time the same identifier.


    Success
    Cor

    Thursday, August 1, 2019 9:57 AM
  • Hello,

    Here is a code sample that will do what you want with the caveat it's not wise to do this in a multi-user environment.

    Data class

    Imports System.Data.OleDb
    
    Public Class Operations
        Private mHasException As Boolean = False
        Public ReadOnly Property IsSuccessful() As Boolean
            Get
                Return mHasException = False
            End Get
        End Property
        Private mException As Exception
        Public ReadOnly Property LastException() As Exception
            Get
                Return mException
            End Get
        End Property
        Public ReadOnly Property ConnectionString As String
            Get
                Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb"
            End Get
        End Property
        
        Public Function CurrentIdentifier() As Integer
            Using cn As New OleDbConnection(ConnectionString)
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT TOP 1 ID FROM Admin ORDER BY ID DESC;"
                    }
    
                    cn.Open()
                    Return CInt(cmd.ExecuteScalar())
    
                End Using
            End Using
        End Function
        Public Function NextIdentifier() As Integer
            Using cn As New OleDbConnection(ConnectionString)
                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT TOP 1 ID FROM Admin ORDER BY ID DESC;"
                    }
    
                    cn.Open()
                    Return CInt(cmd.ExecuteScalar()) + 1
    
                End Using
            End Using
        End Function
    End Class

    Form code

    Public Class Form1
        Private operations As New Operations
        Private Sub GetCurrentButton_Click(sender As Object, e As EventArgs) _
            Handles GetCurrentButton.Click
    
            CurrentTextBox.Text = operations.CurrentIdentifier().ToString()
    
        End Sub
    
        Private Sub GetNextButton_Click(sender As Object, e As EventArgs) _
            Handles GetNextButton.Click
    
            NextTextBox.Text = operations.NextIdentifier().ToString()
    
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, August 1, 2019 10:16 AM
    Moderator
  • @Cor Ligthert

    I have setting automatic identifier on my access database. i setting two id one is ms access primary key auto id. Second id i code setting the code is i ask on this forum.

    My program result

    my id can not be auto change to AAAAAAAA83

    • Edited by christing Friday, August 2, 2019 3:36 AM
    Friday, August 2, 2019 3:04 AM
  • Christing,

    Net OleDB is disconnected access. It means that before the update it does know nothing about chances that happens to the database outside your program.

    You can simply not affect an automatic identifier from outside ms-access itself. 

    A very good alternative for that is the global unique identifier. But that you have always to set before you update the database from your program.

    A Guid. 

    In VB you create then a new key by:

    dim AccessID = new guid 

    That one you can change. 

    I assume it is seldom used in Access because it is typical for more professional databases.


    Success
    Cor

    Friday, August 2, 2019 10:18 AM
  • @Cor Ligthert

    Which mean is i need create a new key for my access database on my program load file. 

    Monday, August 5, 2019 3:08 AM
  • Hi,

    Do you resolve the issue?If you resolve the issue, we appreciated you shared us your solution and mark it as an answer.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 14, 2019 1:23 AM
  • @Cor Ligthert

    I have setting automatic identifier on my access database. i setting two id one is ms access primary key auto id. Second id i code setting the code is i ask on this forum.

    My program result

    my id can not be auto change to AAAAAAAA83

    Hi ,

    I think you need some code like this

     Private Sub autonum()
            Using cn As New SqlConnection(cs)
                cn.Open()
                Using cmd As New SqlCommand
                    cmd.Connection = cn
                    cmd.CommandText = "select * from Vendor order by VendorID"
                    Using da As New SqlDataAdapter(cmd), dt As New DataTable()
                        da.Fill(dt)
                        If dt.Rows.Count < 1 Then
    
                            txtStockID.Text = "AAAAA-80"
                        Else
                            Dim count As Integer = dt.Rows.Count
                            Dim oldid As String = dt.Rows(count - 1)("VendorID").ToString
     
                            Dim arr As String() = oldid.Split("-"c)
                            Dim value As Int32 = (CInt(arr(1)) + 1)
                            txtStockID.Text = "AAAAA" + "-" + value.ToString
                        End If
                    End Using
                End Using
            End Using
        End Sub
    Hope it helps ......


    Regards From Egypt

    Wednesday, August 14, 2019 12:07 PM