• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

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?
0
Murray Brown
Asked:
Murray Brown
  • 4
  • 4
1 Solution
 
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
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
Meir RivkinFull 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
 
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now