Printable View
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 :rolleyes:.
I was wondering... ¿what's the best way to do it?
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 :confused:Quote:
Originally Posted by wild_bill
Have you seen this http://msdn.microsoft.com/en-us/library/ms187342.aspx
Don't forget to read the notes.
You're using seperate commands, so not my suggestion.
The second example would apply if you are using a stored procedure and declaring an output parameter.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
You are safer using a transaction. Put it into a stored procedure to keep it simple.
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:
I call it with this codeCode:INSERT INTO Proveedores
(Cuit, Nombre, [Nombre fantasía])
VALUES (@Cuit,@Nombre,@Nombre_fantasía);
SELECT @@identity
It always returns "1" instead of the new Id :(. What am I missing?Code:Dim ad As New BienesDeUsoDataSetTableAdapters.ProveedoresTableAdapter
Dim id As Integer = ad.InsertQuery("3423432", "Marco", "Polo")
I have not used a ProveedoresTableAdapter, but would gues that InsertQuery will return the number of rows that have been updated.
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.
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.
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.Quote:
the new name of @@identity
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.
Not at all, I should have said better replacement. It is better to over-describe something.Quote:
Originally Posted by FunkyDexter