VB.net check the AutoIncrement property in an Access database

Hi

What VB.net code would I use to get the auto increment value of a column in
an Access database?
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
Meir RivkinConnect With a Mentor Full stack Software EngineerCommented:
what is the primary ID field in Transactions table?

use it in this query (replace TransactionID):
      Dim cs As String = Globals.ThisAddIn.oRIGHT.lblConnectionString.Text
                Dim myConnection As OleDbConnection = New OleDbConnection(cs)

                Try
                    Dim cmd As OleDbCommand = New OleDbCommand("Select Max(TransactionID) As MaxID FROM Transactions", myConnection)
                    myConnection.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader()
                    reader.Read()
                    Dim nextIdentity As Integer = reader.Item(0).ToString()
                    Is_Identity_Column = True
                Catch ex As Exception
                    Is_Identity_Column = False
                    MsgBox(ex.Message)
                End Try

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
here an example:
Dim cmd As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", connection)
    Dim reader  As OleDbDataReader = cmd.ExecuteReader()
    reader.Read()
    dim nextIdentity as int = executeInsertGetIdentity = reader.Item(0).ToString()

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi

Thanks.

If I have a table called "Transactions", how would I use your code to check the if the AutoIncrement value of the ID column?
Would I use SELECT @@IDENTITY FROM Transactions
or do I actually have to execute insert statement in order to get the value
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Meir RivkinFull stack Software EngineerCommented:
for specific table use this:
Dim cmd As OleDbCommand = New OleDbCommand("SELECT IDENT_CURRENT('Transactions')+1", connection)
    Dim reader  As OleDbDataReader = cmd.ExecuteReader()
    reader.Read()
    dim nextIdentity as int = reader.Item(0).ToString()

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. Thanks
I get the error "Undefined function ‘IDENT_CURRENT’ in expression"

when running the following code:

         Dim cs As String = Globals.ThisAddIn.oRIGHT.lblConnectionString.Text
                Dim myConnection As OleDbConnection = New OleDbConnection(cs)

                Try
                    Dim cmd As OleDbCommand = New OleDbCommand("SELECT IDENT_CURRENT('" & oTableName & "')+1", myConnection)
                    myConnection.Open()
                    Dim reader As OleDbDataReader = cmd.ExecuteReader()
                    reader.Read()
                    Dim nextIdentity As Integer = reader.Item(0).ToString()
                    Is_Identity_Column = True
                Catch ex As Exception
                    Is_Identity_Column = False
                    MsgBox(ex.Message)
                End Try
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi Sedgwick
The purpose of this code is to check if the ID column in table is an AutoIncrement field
I should have mentioned that in the question, but I thought that it was apparent in my code
0
 
Meir RivkinFull stack Software EngineerCommented:
the code i've posted will return u the next auto increment identity.
i'm not sure i'm following you
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.