# VBForums CodeBank > CodeBank - Visual Basic .NET >  Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

## Wokawidget

If you have a datatable, and lets say this has a field UserID, which is an Identity column in the DB, then when you update this using a dataadapter the new UserID value is not inserted into the rows in the datatable.

For updating a datatable you can simpley use:

VB Code:
Public Sub Update(ByVal SQL As String, ByRef Data As DataTable)
        Dim Comm As New SqlCommand(SQL, Connection)
        Dim da As New SqlDataAdapter(Comm)
        Dim db As New SqlCommandBuilder(da)
         da.Update(Data)
        CloseConnection()
    End Sub
Where SQL is like:


```
SELECT UserID, Username, Password FROM Users
```

and where *Connection* is a function to get and open a connection to the SQL Server DB.
Using the above function it's quite easy to update, insert and delete rows as the SQLCommandBuilder does most of the hard work for you and creates the Update, insert and delete command objects.

This however is not good enough, as I want to see the userID field values. No idea why ADO.NET doesn't do this automatically for you.

What I decided to do is to scrap the SQL param.
I can manually build up the Update and Insert statements by looping through the columns in the datatable.
While looping, I check for an identity column. If there is one then:


```
SELECT @@SCOPE_IDENTITY AS UserID
```

Is tagged to the end of the insert statement.

I modified the above code so that it is like:

VB Code:
Public Sub Update(ByRef Data As DataTable)
        Dim da As New SqlDataAdapter
        da.InsertCommand = BuildInsertCommand(Data)
        da.UpdateCommand = BuildUpdateCommand(Data)
        da.Update(Data)
        CloseConnection()
    End Sub
     Private Function BuildUpdateCommand(ByRef Data As DataTable) As SqlCommand
        Dim NewCmd As New SqlCommand
        NewCmd.Connection = Connection
         Dim SetString As String
        Dim Identity As String
        For Each Col As DataColumn In Data.Columns
            If Col.AutoIncrement Then
                Identity = Col.ColumnName & " = @" & Col.ColumnName
            Else
                If Not (SetString = String.Empty) Then
                    SetString &= ", "
                End If
                SetString &= Col.ColumnName & " = @" & Col.ColumnName
            End If
             Dim Param As New SqlParameter
            Param.ParameterName = "@" & Col.ColumnName
            Param.SourceColumn = Col.ColumnName
            NewCmd.Parameters.Add(Param)
        Next Col
         Dim CmdText As String = "UPDATE " & Data.TableName & " SET " & SetString & " WHERE " & Identity
        NewCmd.CommandText = CmdText
        Return NewCmd
    End Function
     Private Function BuildInsertCommand(ByRef Data As DataTable) As SqlCommand
        Dim NewCmd As New SqlCommand
        NewCmd.Connection = Connection
         Dim Fields As String
        Dim Values As String
        Dim Identity As String
        For Each Col As DataColumn In Data.Columns
            If Col.AutoIncrement Then
                Identity = "SELECT @@SCOPE_IDENTITY AS " & Col.ColumnName
            Else
                If Not (Fields = String.Empty) Then
                    Fields &= ", "
                End If
                Fields &= Col.ColumnName
                If Not (Values = String.Empty) Then
                    Values &= ", "
                End If
                Values &= "@" & Col.ColumnName
                 Dim Param As New SqlParameter
                Param.ParameterName = "@" & Col.ColumnName
                Param.SourceColumn = Col.ColumnName
                NewCmd.Parameters.Add(Param)
            End If
        Next Col
         Dim CmdText As String = "INSERT INTO " & Data.TableName & "(" & Fields & ") VALUES(" & Values & ")"
        If Not (Identity Is Nothing) Then
            CmdText &= "; " & Identity
        End If
        NewCmd.CommandText = CmdText
        Return NewCmd
    End Function
When saving my Users datatable this produces an insert command text of:

VB Code:
INSERT INTO Users(Username, Password) Values(@Username, @Password); SELECT @@SCOPE_IDENTITY As UserID
The command param object allows you to bind rows to an @Varible by doing:

VB Code:
Dim Param As New SqlParameter
Param.ParameterName = "@" & Col.ColumnName
Param.SourceColumn = Col.ColumnName
NewCmd.Parameters.Add(Param)
Now when I save my DB the new identity value is added to datatable.
Perfect.

I am howveer making some assumptions here:

In both functions I am assuming that the tablename in the DB is the same as the tablename of the datatable.

In the update command function I am using the Identity column to specify which rows to update. This is used to create the WHERE clause of my update statement. I should however change this to use unique columns to build up the WHERE clause. Not figured that out yet.

WOka

----------


## Wokawidget

I am also assuming that the name of the datatable is exactly the same as in the DB. the however may not always be the case.
So, we need to manually specify the table it wishes to update.

VB Code:
Public Function BuildInsertCommand(ByRef Data As DataTable, ByVal TableName As String) As SQLCommand
   'blah blah blah
     'This line needs to be modified to use the passed param and not the datatable tablename
    Dim CmdText As String = "INSERT INTO " & TableName & "(" & Fields & ") VALUES(" & Values & ")"
        
    'blah blah blah
End Sub
This change also needs to be done to the BuildUpdateCommand function.

However, since the datatable tablename is more often than not the same as the DBs tablename then it seems a little excessive to always manually pass in a table name, we could just use the datatables tablename like in my 1st post.

What we can do here is we can overload the Buildcommand functions.

VB Code:
Public Overloads Function BuildInsertCommand(ByRef Data As DataTable) As SQLCommand
    Return BuildInsertCommand(Data, Data.TableName)
End Function
This gives us a little more flexibility when it comes to saving the datatable.

Hope this helps.

Woka

----------


## techgnome

If using SQL Server, it may sometimes be best to use <a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp">@@SCOPE_IDENTITY</a>. This returns the IDENTITY within the current context. This is especialy true if you have triggers which insert into other tables. This is because @@IDENTITY returns the LAST ADDED Identity field.... and if you have triggers, that won't be from the table you jsut inserted. @@SCOPE_IDENTITY on the other hand will return the right identity from the table just inserted into (but not from the triggers.) Did that make sense?

Tg

----------


## Wokawidget

Yea. I saw something on that last night. Was going to look into it this afternoon.
Just thought I'd get my code up and running. OK...I will change my 1st post.

Thanks.

Woka

----------


## JAKSupport

Rather than using DYNAMIC HTML you should take advantage of stored procedures, just a thought.  Dynamic html is vulnerable to SQL Injection, no security mechanism, and the code is a bit more difficult to read.  Generally you want all INSERTS / UPDATES / DELETES to be performed on the server side of things.

----------


## techgnome

Jak - what the heck? Who said anything about DHTML? This was all about getting the last ID inserted....

-tg

----------


## JAKSupport

No look at the code (the vb code pages) he is using static / dynamic html within his code.  It is best to leave that to the db side of things.  Sorry that is what I meant, not sure how you missed it.

----------


## JAKSupport

BTW returning SCOPE_IDENTITY can be sent back as an OUTPUT parameter in 2k, and the new 2k5 (SQL Server) allows you to perform SELECT output.ID FROM ... statements.

----------


## techgnome

Okay, what-ever..... I must be as blind as a bat, because I don't see anything about (D)(X)HTML anywhere. All I see is VB code. Even still, it's not pertinant to this thread.

-tg

----------


## JAKSupport

You dont see the INSERT and SELECT statements within his code?  That is running or executing SQL he creates static SQL in his code, and then uses variables or parameters within the program to construct SQL...and how does it get run EXECUTE ???  See my point about stored procedures and security.

If you still don't understand feel free to PM, I don't want to go off topic.

----------


## techgnome

Jimminy Christmas! THAT's what you are on about. Of course *DYNAMIC SQL* is bad, we all know that.... but that's outside the scope of things here... 

Your quote read:



> Rather than using DYNAMIC HTML you should take advantage of stored procedures, just a thought. Dynamic html


Last I checked, HTML and SQL were NOT the same thing.... now, have things changed? Okay... so, do you know see why I am confused?

-tg

----------


## Wokawidget

Hahaha this was confusing  :Big Grin: 

If I did:

VB Code:
Dim SQL As String = "SELECT * FROM Users WHERE UserID = " & txtUserID.Text
Then, yes, I would agree with your comments regarding SQL injections blah blah.

However, I have not used this method in my code. Using the command object can prevent SQL injections.

OK, point taken, stored procedures...not all DB's have the ability to run SP's...and sometimes SP's are just plain outright overkill.
SQL 2005 has made life very easy regarding writting DB code, but it is still relatively new...and certainly wasn't released when I made this post  :Big Grin: 

Woka

----------


## Wokawidget

> Jimminy Christmas!


 :Big Grin: 

I wouldn't say Dynamic SQL is bad as such, it has it's places.
It's all about what technologies are available at your disposal at any one given time that determinds how things are done.

It also depends on the timescale you've been given for an app, and also the network environment. Maybe the customer hasn't decided what DB to use yet.

The list is endless.

Point being, there are always 2 ways to skin a cat, and neither way is incorrect. You use a knife...I prefer a spoon.

Woooof

----------


## JAKSupport

> Jimminy Christmas! THAT's what you are on about. Of course *DYNAMIC SQL* is bad, we all know that.... but that's outside the scope of things here... 
> 
> Your quote read:
> 
> Last I checked, HTML and SQL were NOT the same thing.... now, have things changed? Okay... so, do you know see why I am confused?
> 
> -tg


Sorry bout that   :Blush:  I meant to say Dynamic SQL.  And it should *never* be an option.  IT is bad practice...if you are going to do any type of sql on the coding side of things (Away from the db) please at least use the parameters collection and ado.net to at least eliminate any possibility of sql injection, or bad code ftm.

----------


## Wokawidget

Like I said...Not all DB's can use Stored Procedures.
And, you'll notice I did use the parameters collection.

Was this you just wanting a random rant?  :Big Grin:  Hahaha

Woof

----------


## JAKSupport

If they dont have procedures they definately have a way to avoid sql injection. I did notice you used the parameters class, which was good, however you did use dynamic sql.  Oracle and SQL server both can handle stored procedures.

----------


## Wokawidget

Yes I was using dynamic sql, but with params, but as explained this is sometimes unavoidable.
Yes, both Oracle and SQL Server have SP's at their disposal...but not everyone uses these DB's I am afraid...we do not always live in an ideal world  :Smilie: 

Woka

----------


## ProphetBeal

I know this is kinda old but I just wanted to say a) I think you did a great job on this,however
b) SELECT @@SCOPE_IDENTITY doesn't work in SQL Server 2008 instead you need to use SELECT SCOPE_IDENTITY()

Not sure when this changed but figured I'd share the change, otherwise the code worked great for me.

----------


## ProphetBeal

Double post...sorry

----------


## techgnome

That's because it never changed... It's always been @@IDENTITY and SCOPE_IDENTITY() ... what's changed apparently has been my memory and recall... apparently I must have been smoking something the day I posted that... I also notice the link doesn't work either.

So here's a better link.
http://msdn.microsoft.com/en-us/library/ms190315.aspx

-tg

----------

