[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Handling SQL Server Identity columns within a Datatable

Posted on 2004-09-15
3
Medium Priority
?
475 Views
Last Modified: 2012-05-05
Hello all,

I have a tabe in a SQL Server database with an identity column set as the primary key.  I'm loading this table into a datatable through the use of a sqldataadaptor.  The problem is this, when I add a row to the datatable the column is not incremented.  That column is just set to a null value.  I tried setting that column's autoincrement (along with the autoincrementseed) property but still no go.  Any ideas?

Thanks,

JD
0
Comment
Question by:mrbass78
  • 2
3 Comments
 
LVL 4

Expert Comment

by:gdexter
ID: 12067062
Use this method of the DataAdapter to retrieve the new value after an update:

Private Shared Sub DaAdapter_RowUpdated(ByVal sender As Object, ByVal e As   System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles DaAdapter.RowUpdated

        If (e.StatementType = StatementType.Insert) Then
            Dim cmd As New OleDbCommand("SELECT @@IDENTITY", e.Command.Connection)
           
           'Inser the name of your ID column
            e.Row("ID") = cmd.ExecuteScalar()
            e.Row.AcceptChanges()
        End If

    End Sub

'Now after the call to update you can get the value:
'sample code

dataTbl.Rows.Add(newRow)
DaAdapter.Update(ds, "YourTable")

Dim id as Integer = newRow.Item("ID")

0
 

Author Comment

by:mrbass78
ID: 12067093
Thanks for the help but is there a way you could do it without updating?  I want the user to be able to work using the table but if they exit without saving I don't want the changes made to the actual table in the database.  So is there a way to do it just in the datatable itself?
0
 
LVL 4

Accepted Solution

by:
gdexter earned 2000 total points
ID: 12067380
Is the Identity column relevent to the user?
You should be able to add as many new rows as you would like and during the update allow SQL Server to take care of the ID column.

Remeber the Datatable is disconnected from the Database and has know way of knowing what the next ID is going to be because other users could be making inserts while you are disconnected, that is why the row updated event is their.

In addtion I gave you the OleDb version before: sorry,
Private Shared Sub DaAdapter_RowUpdated(ByVal sender As Object, ByVal e As   System.Data.SqlClient.SqlRowUpdatedEventArgs) Handles DaAdapter.RowUpdated

        If (e.StatementType = StatementType.Insert) Then
            Dim cmd As New SqlCommand("SELECT @@IDENTITY", e.Command.Connection)
           
           'Inser the name of your ID column
            e.Row("ID") = cmd.ExecuteScalar()
            e.Row.AcceptChanges()
        End If

    End Sub
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month19 days, 4 hours left to enroll

834 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