Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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