Solved

VB.net check the AutoIncrement property in an Access database

Posted on 2013-07-01
8
420 Views
Last Modified: 2013-07-01
Hi

What VB.net code would I use to get the auto increment value of a column in
an Access database?
0
Comment
Question by:murbro
  • 4
  • 4
8 Comments
 
LVL 42

Expert Comment

by:sedgwick
Comment Utility
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
 

Author Comment

by:murbro
Comment Utility
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
 
LVL 42

Expert Comment

by:sedgwick
Comment Utility
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
 

Author Comment

by:murbro
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
Comment Utility
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
 

Author Comment

by:murbro
Comment Utility
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
 
LVL 42

Expert Comment

by:sedgwick
Comment Utility
the code i've posted will return u the next auto increment identity.
i'm not sure i'm following you
0
 

Author Closing Comment

by:murbro
Comment Utility
Thanks very much
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now