Results 1 to 12 of 12

Thread: id of inserted register in SQL Server

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    9

    id of inserted register in SQL Server

    Hi, I have this process that inserts a register in one table and then another register in another table. The thing is that the one in the second table must be referenced the id of the register from the first table.

    Usually what I do after the register is inserted, i use a select command with the Top(1) to get the last one. It works fine with a single user, but It doesn't seem very professional .

    I was wondering... ¿what's the best way to do it?

  2. #2
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: id of inserted register in SQL Server

    You could pass the id back from your SQL statement using a Select command, or by using an output parameter. Is the register id an identity field?
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    9

    Re: id of inserted register in SQL Server

    Quote Originally Posted by wild_bill View Post
    You could pass the id back from your SQL statement using a Select command, or by using an output parameter. Is the register id an identity field?
    Yes, it is an identity field. The first method you named is the one I'm using (i think), the second... not sure what you are talking about

  4. #4
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    Re: id of inserted register in SQL Server

    Have you seen this http://msdn.microsoft.com/en-us/library/ms187342.aspx
    Don't forget to read the notes.

  5. #5
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: id of inserted register in SQL Server

    You're using seperate commands, so not my suggestion.
    Code:
        Public Function InsertRegister(description As String) As Integer
            'first example
            Using con As New SqlClient.SqlConnection("connection string")
                con.Open()
    
                Using cmd As New SqlClient.SqlCommand("INSERT INTO REGISTER (DESCRIPTION) VALUES (@DESCRIPTION); SELECT @@IDENTITY")
    
                    cmd.Parameters.AddWithValue("@DESCRIPTION", description)
                    Return DirectCast(cmd.ExecuteScalar(), Integer)
    
                End Using
    
            End Using
        End Function
    The second example would apply if you are using a stored procedure and declaring an output parameter.
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  6. #6
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    Re: id of inserted register in SQL Server

    You are safer using a transaction. Put it into a stored procedure to keep it simple.

  7. #7

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    9

    Re: id of inserted register in SQL Server

    Interesting, there is a way .

    Usually i work with typed datasets, so i call the querys of the DataAdapter (i found this way more convinient than working with stored procedures, I don't remember why).

    I tried the @@identity but doesn't work.

    I have this INSERT QUERY in ProveedoresTableAdapter:
    Code:
    INSERT INTO Proveedores
                          (Cuit, Nombre, [Nombre fantasía])
    VALUES     (@Cuit,@Nombre,@Nombre_fantasía); 
    SELECT @@identity
    I call it with this code

    Code:
    Dim ad As New BienesDeUsoDataSetTableAdapters.ProveedoresTableAdapter
    Dim id As Integer = ad.InsertQuery("3423432", "Marco", "Polo")
    It always returns "1" instead of the new Id . What am I missing?

  8. #8
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    Re: id of inserted register in SQL Server

    I have not used a ProveedoresTableAdapter, but would gues that InsertQuery will return the number of rows that have been updated.

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: id of inserted register in SQL Server

    Just a bit of pedantry but you should be using Scope_Identity() rather than @@Identity. It just makes sure you don't get tripped up by any triggers etc that might be running in the background.

    I'm afraid I don't know why your inserts not producing the right result as I typically don't use table adapaters, I work with commands as Wild Bill showed you. I'd guess Grimfort is right, though. Because it's doing an insert it's probably running ExecuteNonQuery under the lid. That returns a row count.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    Re: id of inserted register in SQL Server

    You are correct, SCOPE_IDENTITY would be better, I forgot the new name of @@identity as was on my tablet when I made that post.

  11. #11
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: id of inserted register in SQL Server

    the new name of @@identity
    Be a little careful there. It's not a new name, it's a different value. @@Identity returns the last identity value generated, Scope_Identity() returns the last identity value generated in the current scope. So if, for example, you insert into a tableA and that fires a trigger that inserts into tableB and both have identity fields, @@Identity will return the value of the identity from tableB while Scope_Identity() will return the value from tableA.

    Typically it's the tableA value you'd want and the accidental use of @@Identity can result in errors where the dev has forgotten to check for triggers (or a trigger has been added later) which is why I'd recommend Scope_Idenity by default but there may be times when you'd want the tableB value in which case @@Identity would be the correct thing to use... although I must admit, I can't think of a scenario.

    Apologies if I'm being a bit pedantic here and/or teaching you to suck eggs but @@identity has been the root cause of so many bugs I've had to trace over the years that it seemed worth a little pedantry.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  12. #12
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    Re: id of inserted register in SQL Server

    Quote Originally Posted by FunkyDexter View Post
    Apologies if I'm being a bit pedantic here and/or teaching you to suck eggs but @@identity has been the root cause of so many bugs I've had to trace over the years that it seemed worth a little pedantry.
    Not at all, I should have said better replacement. It is better to over-describe something.

Tags for this Thread

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