Results 1 to 20 of 20

Thread: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

  1. #1

    Thread Starter
    Super Moderator Wokawidget's Avatar
    Join Date
    Nov 2001
    Location
    Headingly Occupation: Classified
    Posts
    9,632

    Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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:
    1. Public Sub Update(ByVal SQL As String, ByRef Data As DataTable)
    2.         Dim Comm As New SqlCommand(SQL, Connection)
    3.         Dim da As New SqlDataAdapter(Comm)
    4.         Dim db As New SqlCommandBuilder(da)
    5.  
    6.         da.Update(Data)
    7.         CloseConnection()
    8.     End Sub
    Where SQL is like:
    Code:
    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:
    Code:
    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:
    1. Public Sub Update(ByRef Data As DataTable)
    2.         Dim da As New SqlDataAdapter
    3.         da.InsertCommand = BuildInsertCommand(Data)
    4.         da.UpdateCommand = BuildUpdateCommand(Data)
    5.         da.Update(Data)
    6.         CloseConnection()
    7.     End Sub
    8.  
    9.     Private Function BuildUpdateCommand(ByRef Data As DataTable) As SqlCommand
    10.         Dim NewCmd As New SqlCommand
    11.         NewCmd.Connection = Connection
    12.  
    13.         Dim SetString As String
    14.         Dim Identity As String
    15.         For Each Col As DataColumn In Data.Columns
    16.             If Col.AutoIncrement Then
    17.                 Identity = Col.ColumnName & " = @" & Col.ColumnName
    18.             Else
    19.                 If Not (SetString = String.Empty) Then
    20.                     SetString &= ", "
    21.                 End If
    22.                 SetString &= Col.ColumnName & " = @" & Col.ColumnName
    23.             End If
    24.  
    25.             Dim Param As New SqlParameter
    26.             Param.ParameterName = "@" & Col.ColumnName
    27.             Param.SourceColumn = Col.ColumnName
    28.             NewCmd.Parameters.Add(Param)
    29.         Next Col
    30.  
    31.         Dim CmdText As String = "UPDATE " & Data.TableName & " SET " & SetString & " WHERE " & Identity
    32.         NewCmd.CommandText = CmdText
    33.         Return NewCmd
    34.     End Function
    35.  
    36.     Private Function BuildInsertCommand(ByRef Data As DataTable) As SqlCommand
    37.         Dim NewCmd As New SqlCommand
    38.         NewCmd.Connection = Connection
    39.  
    40.         Dim Fields As String
    41.         Dim Values As String
    42.         Dim Identity As String
    43.         For Each Col As DataColumn In Data.Columns
    44.             If Col.AutoIncrement Then
    45.                 Identity = "SELECT @@SCOPE_IDENTITY AS " & Col.ColumnName
    46.             Else
    47.                 If Not (Fields = String.Empty) Then
    48.                     Fields &= ", "
    49.                 End If
    50.                 Fields &= Col.ColumnName
    51.                 If Not (Values = String.Empty) Then
    52.                     Values &= ", "
    53.                 End If
    54.                 Values &= "@" & Col.ColumnName
    55.  
    56.                 Dim Param As New SqlParameter
    57.                 Param.ParameterName = "@" & Col.ColumnName
    58.                 Param.SourceColumn = Col.ColumnName
    59.                 NewCmd.Parameters.Add(Param)
    60.             End If
    61.         Next Col
    62.  
    63.         Dim CmdText As String = "INSERT INTO " & Data.TableName & "(" & Fields & ") VALUES(" & Values & ")"
    64.         If Not (Identity Is Nothing) Then
    65.             CmdText &= "; " & Identity
    66.         End If
    67.         NewCmd.CommandText = CmdText
    68.         Return NewCmd
    69.     End Function
    When saving my Users datatable this produces an insert command text of:
    VB Code:
    1. 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:
    1. Dim Param As New SqlParameter
    2. Param.ParameterName = "@" & Col.ColumnName
    3. Param.SourceColumn = Col.ColumnName
    4. 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

  2. #2

    Thread Starter
    Super Moderator Wokawidget's Avatar
    Join Date
    Nov 2001
    Location
    Headingly Occupation: Classified
    Posts
    9,632

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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:
    1. Public Function BuildInsertCommand(ByRef Data As DataTable, ByVal TableName As String) As SQLCommand
    2.    'blah blah blah
    3.  
    4.     'This line needs to be modified to use the passed param and not the datatable tablename
    5.     Dim CmdText As String = "INSERT INTO " & TableName & "(" & Fields & ") VALUES(" & Values & ")"
    6.        
    7.     'blah blah blah
    8. 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:
    1. Public Overloads Function BuildInsertCommand(ByRef Data As DataTable) As SQLCommand
    2.     Return BuildInsertCommand(Data, Data.TableName)
    3. End Function
    This gives us a little more flexibility when it comes to saving the datatable.

    Hope this helps.

    Woka

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Super Moderator Wokawidget's Avatar
    Join Date
    Nov 2001
    Location
    Headingly Occupation: Classified
    Posts
    9,632

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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

  5. #5
    Banned
    Join Date
    May 2006
    Posts
    161

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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.

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

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

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    Banned
    Join Date
    May 2006
    Posts
    161

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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.

  8. #8
    Banned
    Join Date
    May 2006
    Posts
    161

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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.

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Banned
    Join Date
    May 2006
    Posts
    161

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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.

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12

    Thread Starter
    Super Moderator Wokawidget's Avatar
    Join Date
    Nov 2001
    Location
    Headingly Occupation: Classified
    Posts
    9,632

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    Hahaha this was confusing

    If I did:
    VB Code:
    1. 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

    Woka

  13. #13

    Thread Starter
    Super Moderator Wokawidget's Avatar
    Join Date
    Nov 2001
    Location
    Headingly Occupation: Classified
    Posts
    9,632

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    Quote Originally Posted by techgnome
    Jimminy Christmas!


    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

  14. #14
    Banned
    Join Date
    May 2006
    Posts
    161

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    Quote Originally Posted by 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:

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

  15. #15

    Thread Starter
    Super Moderator Wokawidget's Avatar
    Join Date
    Nov 2001
    Location
    Headingly Occupation: Classified
    Posts
    9,632

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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? Hahaha

    Woof

  16. #16
    Banned
    Join Date
    May 2006
    Posts
    161

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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.

  17. #17

    Thread Starter
    Super Moderator Wokawidget's Avatar
    Join Date
    Nov 2001
    Location
    Headingly Occupation: Classified
    Posts
    9,632

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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

    Woka

  18. #18
    Hyperactive Member ProphetBeal's Avatar
    Join Date
    Aug 2006
    Location
    New York
    Posts
    424

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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.

    Helpful Links:
    VB 6 - How to get the "Key" Value in a collection
    VB.NET - File Search Utility || VB.NET - How to compare 2 directories || VB.NET - How to trust a network share
    VB.NET - Create Excel Spreadsheet From Array || VB.NET - Example Code & Hints you may not know
    VB.NET - Save JPEG with a certain quality (image compression) || VB.NET - DragDrop Files, Emails, and Email Attachments

    Please post some of the code you need help with (it makes it easier to help you)
    If your problem has been solved then please mark the thread [RESOLVED].
    Don't forget to Rate this post

    "Pinky, you give a whole new meaning to the phrase, 'counter-intelligence'."-The Brain-

  19. #19
    Hyperactive Member ProphetBeal's Avatar
    Join Date
    Aug 2006
    Location
    New York
    Posts
    424

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    Double post...sorry
    Last edited by ProphetBeal; Aug 22nd, 2012 at 09:12 AM. Reason: Double post

  20. #20
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: Retrieving Identity field after inserting datarows into SQL Server using ADO.NET

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width