|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Retrieve GUID on/after insert?I need to write a row into a SQL Server table, and then immediately retrieve
the GUID which is generated for that row (it is the primary key). I can't seem to find a way to do this with DataSets and DataAdapters, so I'm sure I'm overlooking something simple. Could someone direct me to a code sample of how to accomplish this in VB.Net? Thanks. Patrick Bates wrote:
> I need to write a row into a SQL Server table, and then immediately retrieve I don't think you can. But with GUIDs you always have a luxury> the GUID which is generated for that row (it is the primary key). I can't > seem to find a way to do this with DataSets and DataAdapters, so I'm sure > I'm overlooking something simple. > > Could someone direct me to a code sample of how to accomplish this in > VB.Net? Thanks. of generating it outside the database. Sericinus hunter wrote:
> Patrick Bates wrote: You *can* do it, the same as you would for an IDENTITY column. But Sericinus >> I need to write a row into a SQL Server table, and then immediately >> retrieve the GUID which is generated for that row (it is the primary >> key). I can't seem to find a way to do this with DataSets and >> DataAdapters, so I'm sure I'm overlooking something simple. >> >> Could someone direct me to a code sample of how to accomplish this in >> VB.Net? Thanks. > > I don't think you can. But with GUIDs you always have a luxury > of generating it outside the database. is correct, just generate it on the client side and then insert the value to the database. Then you don't have to worry about retrieving it. Matt Noonan wrote:
> Sericinus hunter wrote: Would you briefly show how? Not that I think it can be of much use,>> Patrick Bates wrote: >>> I need to write a row into a SQL Server table, and then immediately >>> retrieve the GUID which is generated for that row (it is the primary >>> key). I can't seem to find a way to do this with DataSets and >>> DataAdapters, so I'm sure I'm overlooking something simple. >>> >>> Could someone direct me to a code sample of how to accomplish this in >>> VB.Net? Thanks. >> I don't think you can. But with GUIDs you always have a luxury >> of generating it outside the database. > > You *can* do it, the same as you would for an IDENTITY column. But Sericinus just out of curiosity. Show quote > is correct, just generate it on the client side and then insert the value to > the database. Then you don't have to worry about retrieving it. Sericinus hunter wrote:
> Now that you mention it, it's not that easy to accomplish... :-/> Would you briefly show how? Not that I think it can be of much use, > just out of curiosity. > There's no @@IDENTITY or SCOPE_IDENTITY to retrieve, so unless you have some alternate way of uniquely identifying the newly-inserted row (such as an alternate key), then you can't return the new GUID from a stored proc. So client-side generation of GUIDs is the way to go. I stand corrected. Matt Noonan wrote:
Show quote > Sericinus hunter wrote: Well, I thought of a way... :)>> >> Would you briefly show how? Not that I think it can be of much >> use, just out of curiosity. >> > > Now that you mention it, it's not that easy to accomplish... :-/ > > There's no @@IDENTITY or SCOPE_IDENTITY to retrieve, so unless you > have some alternate way of uniquely identifying the newly-inserted > row (such as an alternate key), then you can't return the new GUID > from a stored proc. So client-side generation of GUIDs is the way to > go. > I stand corrected. Your stored procedure would have to look something like this: (psuedo-code, not tested) CREATE PROCEDURE sp_InsertEmployee @EmployeeID uniqueidentifier OUTPUT, etc. AS SET @EmployeeID = newguid() INSERT INTO Employees (EmployeeID, etc.) VALUES (@EmployeeID, etc.) That should do the trick. Matt Noonan wrote:
> Sorry, that should be newid().> SET @EmployeeID = newguid() > Matt Noonan wrote:
Show quote > Matt Noonan wrote: Right, but this is essentially the same thing: you generate it>> Sericinus hunter wrote: >>> Would you briefly show how? Not that I think it can be of much >>> use, just out of curiosity. >>> >> Now that you mention it, it's not that easy to accomplish... :-/ >> >> There's no @@IDENTITY or SCOPE_IDENTITY to retrieve, so unless you >> have some alternate way of uniquely identifying the newly-inserted >> row (such as an alternate key), then you can't return the new GUID >> from a stored proc. So client-side generation of GUIDs is the way to >> go. >> I stand corrected. > > Well, I thought of a way... :) > > Your stored procedure would have to look something like this: > (psuedo-code, not tested) > > CREATE PROCEDURE sp_InsertEmployee > @EmployeeID uniqueidentifier OUTPUT, > etc. by hand and then insert. The code is just in a different layer now. I have another idea. Let it get generated automatically. In insert trigger retrieve it and put in a special dedicated table, which may just have one column and one row. Then the latest generated guid is always available. Sericinus hunter wrote:
> I said you could do it that way, not that you should. It depends on your > Right, but this is essentially the same thing: you generate it > by hand and then insert. The code is just in a different layer now. application's needs. > I have another idea. Let it get generated automatically. In Which only works if you have one user at a time inserting records. That's > insert trigger retrieve it and put in a special dedicated table, > which may just have one column and one row. Then the latest > generated guid is always available. why you're not supposed to use @@IDENTITY to get the last autonumber value inserted, because it may not return the correct result. Matt Noonan wrote:
> Sericinus hunter wrote: I don't take it that serious, just brain exercise.>> Right, but this is essentially the same thing: you generate it >> by hand and then insert. The code is just in a different layer now. > > I said you could do it that way, not that you should. It depends on your > application's needs. >> I have another idea. Let it get generated automatically. In Well, better to say "unexpected" result. SCOPE_IDENTITY() is what is>> insert trigger retrieve it and put in a special dedicated table, >> which may just have one column and one row. Then the latest >> generated guid is always available. > > Which only works if you have one user at a time inserting records. That's > why you're not supposed to use @@IDENTITY to get the last autonumber value > inserted, because it may not return the correct result. needed in such a case. Hi Patrick,
I did not know that there was another way than these http://www.vb-tips.com/default.aspx?ID=b57d0ce2-3f5f-47d4-9663-fee3733fcd6f http://www.vb-tips.com/default.aspx?ID=6b05b025-2ace-4ad0-9eae-a95385888e22 I hope this helps, Cor Show quote "Patrick Bates" <pbates@spam.me.notsmartbuys.com> schreef in bericht news:%23RhbTuwlGHA.1740@TK2MSFTNGP05.phx.gbl... >I need to write a row into a SQL Server table, and then immediately >retrieve the GUID which is generated for that row (it is the primary key). >I can't seem to find a way to do this with DataSets and DataAdapters, so >I'm sure I'm overlooking something simple. > > Could someone direct me to a code sample of how to accomplish this in > VB.Net? Thanks. > > So, I have to create the GUID client-side then?
What about the Unique requirements on this column in the database? I know that the chances of a GUID duplication are slim, but how do you handle this possibility if you go client-side? Patrick Bates wrote:
> So, I have to create the GUID client-side then? I don't think that the chances of the duplication depend on where> > What about the Unique requirements on this column in the database? I know > that the chances of a GUID duplication are slim, but how do you handle this > possibility if you go client-side? it is generated. In case this happens, catch the error, regenerate it and try again. Patrick,
AFAIK should the chance that there is a duplication of a Guid be so small that as the full population of the world was connected to your database it should not even give a problem. However there can of course forever be a problem, but normally you fix that in your normal error procedures. Cor Show quote "Patrick Bates" <pbates@spam.me.notsmartbuys.com> schreef in bericht news:u9orgBUmGHA.1896@TK2MSFTNGP05.phx.gbl... > So, I have to create the GUID client-side then? > > What about the Unique requirements on this column in the database? I know > that the chances of a GUID duplication are slim, but how do you handle > this possibility if you go client-side? > > Patrick Bates wrote:
> So, I have to create the GUID client-side then? Have to? No, you don't have to, but it's easier if you do it that way. IIRC your question involved using the keys in a foreign key relationship without inserting the parent into the database first. Generating a GUID on the client side is the only way to accomplish that. > I forget the math, but the chances of a collision are so remote that you may > What about the Unique requirements on this column in the database? I > know that the chances of a GUID duplication are slim, but how do you > handle this possibility if you go client-side? never, ever see it. But you can certainly handle the situation in your exception handler. On Fri, 23 Jun 2006 16:25:31 -0500, "Patrick Bates"
<pbates@spam.me.notsmartbuys.com> wrote: >I need to write a row into a SQL Server table, and then immediately retrieve If the application only needs to work with SQL Server 2005 you could use>the GUID which is generated for that row (it is the primary key). I can't >seem to find a way to do this with DataSets and DataAdapters, so I'm sure >I'm overlooking something simple. > >Could someone direct me to a code sample of how to accomplish this in >VB.Net? Thanks. > INSERT INTO ... OUTPUT ... VALUES ... E.g. create table guid_test ( id uniqueidentifier not null, title varchar(20) not null ) insert into guid_test (id, title) output INSERTED.id, INSERTED.title values (newid(), 'Testing') Then on the client side you'd have to use a data reader to read the result from the sql command. With VB.Net it's probaly easier just to generate the guid client side, but the output parameter would be good if you were using some kind of server side function to generate an ID of some form.  |
|||||||||||||||||||||||
Other interesting topics