SQL 7.0 and UniqueIdentifier Field

[Bill Hilburn]

Bill,
The lobbyistID field in my table is a UniqueIdentifier field,
it generates an ID (newid()) for each record entered.

When I use the following recordset (rsLob) I cannot seem to pull
the strLobID value in the code below.

I have tried both adUseClient and adUseServer along with
each recordset type(dynamic, static etc...) on the connection.

The strLobID variable always gets assigned:
'{00000000-0000-0000-0000-000000000000}'

What are some things I could look for ?

With rsLob
.AddNew

!FullName = rsSHAPE!LNAME
!FirmName = rsLobbyistDetail!LFIRMNAME
!oAddress = rsLobbyistDetail!LADDR1
!oAddress1 = rsLobbyistDetail!LADDR2
!oCity = rsLobbyistDetail!LCITY
!oState = rsLobbyistDetail!LSTATE
!oZip = rsLobbyistDetail!LZIP
!oPhone = rsLobbyistDetail!LPHONE
!hAddress = rsLobbyistDetail!LADDR1
!hCity = rsLobbyistDetail!LCITY
!hState = rsLobbyistDetail!LSTATE
!hZip = rsLobbyistDetail!LZIP
!hPhone = rsLobbyistDetail!LPHONE

.Update

strLobID = rsLob!LobbyistID

End With


Thanks !

Keep up the *great* work !

Bill Hilburn
Senior Analyst / Programmer
Nebraska State Legislature

[Bill Vaughn]

How is ADO supposed to know what ID is assigned by the backend? Since ADO
is simply executing an UPDATE (check the profiler), it won't fill in the
field with the newly generated key--not until you requery. However, how do
you tell which row to requery? Well, ADO can't tell either. There are several
approaches that work better to solve this problem. First, use an SP to do
the insert. Pass the column values as arguments and return the new ID as
a return status (integer only) or Output parameter. Another approach is to
pre-allocate the records using a routine that creates a number of "blank"
records with pre-assigned IDs. From this point forward, you simply update
the existing rows.

Incidentally, your bang (!) syntax is costing you dearly in performance (3x
per !) over ordinal (rs(0)) referencing. I also don't reference individual
fields in an AddNew, I use
rs.AddNew vaFields, vaData
in one line you do all of the work with zero binding problems.

I have examples of this in the new book.

hth

bv


"Bill Hilburn" <bhilburn@unicam.state.ne.us> wrote:
>
>
>Bill,
>The lobbyistID field in my table is a UniqueIdentifier field,
>it generates an ID (newid()) for each record entered.
>
>When I use the following recordset (rsLob) I cannot seem to pull
>the strLobID value in the code below.
>
>I have tried both adUseClient and adUseServer along with
>each recordset type(dynamic, static etc...) on the connection.
>
>The strLobID variable always gets assigned:
>'{00000000-0000-0000-0000-000000000000}'
>
>What are some things I could look for ?
>
> With rsLob
> .AddNew
>
> !FullName = rsSHAPE!LNAME
> !FirmName = rsLobbyistDetail!LFIRMNAME
> !oAddress = rsLobbyistDetail!LADDR1
> !oAddress1 = rsLobbyistDetail!LADDR2
> !oCity = rsLobbyistDetail!LCITY
> !oState = rsLobbyistDetail!LSTATE
> !oZip = rsLobbyistDetail!LZIP
> !oPhone = rsLobbyistDetail!LPHONE
> !hAddress = rsLobbyistDetail!LADDR1
> !hCity = rsLobbyistDetail!LCITY
> !hState = rsLobbyistDetail!LSTATE
> !hZip = rsLobbyistDetail!LZIP
> !hPhone = rsLobbyistDetail!LPHONE
>
> .Update
>
> strLobID = rsLob!LobbyistID
>
> End With
>
>
>Thanks !
>
>Keep up the *great* work !
>
>Bill Hilburn
>Senior Analyst / Programmer
>Nebraska State Legislature
>

[Bill Vaughn]

Oh, you can also try to enable "auto resync" by setting the "Update Resync"
property to adResyncAutoIncrement. According to the doc this should re-query
the row in question to find/return the new ID value.


"Bill Vaughn" <billva@microsoft.com> wrote:
>
>How is ADO supposed to know what ID is assigned by the backend? Since ADO
>is simply executing an UPDATE (check the profiler), it won't fill in the
>field with the newly generated key--not until you requery. However, how
do
>you tell which row to requery? Well, ADO can't tell either. There are several
>approaches that work better to solve this problem. First, use an SP to do
>the insert. Pass the column values as arguments and return the new ID as
>a return status (integer only) or Output parameter. Another approach is
to
>pre-allocate the records using a routine that creates a number of "blank"
>records with pre-assigned IDs. From this point forward, you simply update
>the existing rows.
>
>Incidentally, your bang (!) syntax is costing you dearly in performance
(3x
>per !) over ordinal (rs(0)) referencing. I also don't reference individual
>fields in an AddNew, I use
> rs.AddNew vaFields, vaData
>in one line you do all of the work with zero binding problems.
>
>I have examples of this in the new book.
>
>hth
>
>bv
>
>
>"Bill Hilburn" <bhilburn@unicam.state.ne.us> wrote:
>>
>>
>>Bill,
>>The lobbyistID field in my table is a UniqueIdentifier field,
>>it generates an ID (newid()) for each record entered.
>>
>>When I use the following recordset (rsLob) I cannot seem to pull
>>the strLobID value in the code below.
>>
>>I have tried both adUseClient and adUseServer along with
>>each recordset type(dynamic, static etc...) on the connection.
>>
>>The strLobID variable always gets assigned:
>>'{00000000-0000-0000-0000-000000000000}'
>>
>>What are some things I could look for ?
>>
>> With rsLob
>> .AddNew
>>
>> !FullName = rsSHAPE!LNAME
>> !FirmName = rsLobbyistDetail!LFIRMNAME
>> !oAddress = rsLobbyistDetail!LADDR1
>> !oAddress1 = rsLobbyistDetail!LADDR2
>> !oCity = rsLobbyistDetail!LCITY
>> !oState = rsLobbyistDetail!LSTATE
>> !oZip = rsLobbyistDetail!LZIP
>> !oPhone = rsLobbyistDetail!LPHONE
>> !hAddress = rsLobbyistDetail!LADDR1
>> !hCity = rsLobbyistDetail!LCITY
>> !hState = rsLobbyistDetail!LSTATE
>> !hZip = rsLobbyistDetail!LZIP
>> !hPhone = rsLobbyistDetail!LPHONE
>>
>> .Update
>>
>> strLobID = rsLob!LobbyistID
>>
>> End With
>>
>>
>>Thanks !
>>
>>Keep up the *great* work !
>>
>>Bill Hilburn
>>Senior Analyst / Programmer
>>Nebraska State Legislature
>>
>

[Fred2]

Hi

Why not create a GUID yourself, that way you don't need to do an insert to
get the value.

Declare Function CoCreateGuid Lib "ole32.dll" (pguid As Byte) As Long
Declare Function StringFromGUID2 Lib "ole32.dll" (pguid As Byte, ByVal lpsz
As Long, ByVal cchMax As Long) As Long

Public Function NewGUID() As String
Dim g(15) As Byte
Call CoCreateGuid(g(0))
NewGUID = Space$(256)
Call StringFromGUID2(g(0), StrPtr(NewGUID), 255)
NewGUID = Left$(NewGUID, InStr(1, NewGUID, Chr$(0), vbBinaryCompare)
- 1)
End Function

bye


"Bill Vaughn" <billva@microsoft.com> wrote:
>
>How is ADO supposed to know what ID is assigned by the backend? Since ADO
>is simply executing an UPDATE (check the profiler), it won't fill in the
>field with the newly generated key--not until you requery. However, how
do
>you tell which row to requery? Well, ADO can't tell either. There are several
>approaches that work better to solve this problem. First, use an SP to do
>the insert. Pass the column values as arguments and return the new ID as
>a return status (integer only) or Output parameter. Another approach is
to
>pre-allocate the records using a routine that creates a number of "blank"
>records with pre-assigned IDs. From this point forward, you simply update
>the existing rows.
>
>Incidentally, your bang (!) syntax is costing you dearly in performance
(3x
>per !) over ordinal (rs(0)) referencing. I also don't reference individual
>fields in an AddNew, I use
> rs.AddNew vaFields, vaData
>in one line you do all of the work with zero binding problems.
>
>I have examples of this in the new book.
>
>hth
>
>bv
>
>
>"Bill Hilburn" <bhilburn@unicam.state.ne.us> wrote:
>>
>>
>>Bill,
>>The lobbyistID field in my table is a UniqueIdentifier field,
>>it generates an ID (newid()) for each record entered.
>>
>>When I use the following recordset (rsLob) I cannot seem to pull
>>the strLobID value in the code below.
>>
>>I have tried both adUseClient and adUseServer along with
>>each recordset type(dynamic, static etc...) on the connection.
>>
>>The strLobID variable always gets assigned:
>>'{00000000-0000-0000-0000-000000000000}'
>>
>>What are some things I could look for ?
>>
>> With rsLob
>> .AddNew
>>
>> !FullName = rsSHAPE!LNAME
>> !FirmName = rsLobbyistDetail!LFIRMNAME
>> !oAddress = rsLobbyistDetail!LADDR1
>> !oAddress1 = rsLobbyistDetail!LADDR2
>> !oCity = rsLobbyistDetail!LCITY
>> !oState = rsLobbyistDetail!LSTATE
>> !oZip = rsLobbyistDetail!LZIP
>> !oPhone = rsLobbyistDetail!LPHONE
>> !hAddress = rsLobbyistDetail!LADDR1
>> !hCity = rsLobbyistDetail!LCITY
>> !hState = rsLobbyistDetail!LSTATE
>> !hZip = rsLobbyistDetail!LZIP
>> !hPhone = rsLobbyistDetail!LPHONE
>>
>> .Update
>>
>> strLobID = rsLob!LobbyistID
>>
>> End With
>>
>>
>>Thanks !
>>
>>Keep up the *great* work !
>>
>>Bill Hilburn
>>Senior Analyst / Programmer
>>Nebraska State Legislature
>>
>

[Bill Vaughn]

Cool. Never thought of that. Can I steal (er, leverage) the idea?

bv

"Fred2" <fred@glo.be> wrote:
>
>Hi
>
>Why not create a GUID yourself, that way you don't need to do an insert
to
>get the value.
>
>Declare Function CoCreateGuid Lib "ole32.dll" (pguid As Byte) As Long
>Declare Function StringFromGUID2 Lib "ole32.dll" (pguid As Byte, ByVal lpsz
>As Long, ByVal cchMax As Long) As Long
>
>Public Function NewGUID() As String
> Dim g(15) As Byte
> Call CoCreateGuid(g(0))
> NewGUID = Space$(256)
> Call StringFromGUID2(g(0), StrPtr(NewGUID), 255)
> NewGUID = Left$(NewGUID, InStr(1, NewGUID, Chr$(0), vbBinaryCompare)
>- 1)
>End Function
>
>bye
>
>
>"Bill Vaughn" <billva@microsoft.com> wrote:
>>
>>How is ADO supposed to know what ID is assigned by the backend? Since ADO
>>is simply executing an UPDATE (check the profiler), it won't fill in the
>>field with the newly generated key--not until you requery. However, how
>do
>>you tell which row to requery? Well, ADO can't tell either. There are several
>>approaches that work better to solve this problem. First, use an SP to
do
>>the insert. Pass the column values as arguments and return the new ID as
>>a return status (integer only) or Output parameter. Another approach is
>to
>>pre-allocate the records using a routine that creates a number of "blank"
>>records with pre-assigned IDs. From this point forward, you simply update
>>the existing rows.
>>
>>Incidentally, your bang (!) syntax is costing you dearly in performance
>(3x
>>per !) over ordinal (rs(0)) referencing. I also don't reference individual
>>fields in an AddNew, I use
>> rs.AddNew vaFields, vaData
>>in one line you do all of the work with zero binding problems.
>>
>>I have examples of this in the new book.
>>
>>hth
>>
>>bv
>>
>>
>>"Bill Hilburn" <bhilburn@unicam.state.ne.us> wrote:
>>>
>>>
>>>Bill,
>>>The lobbyistID field in my table is a UniqueIdentifier field,
>>>it generates an ID (newid()) for each record entered.
>>>
>>>When I use the following recordset (rsLob) I cannot seem to pull
>>>the strLobID value in the code below.
>>>
>>>I have tried both adUseClient and adUseServer along with
>>>each recordset type(dynamic, static etc...) on the connection.
>>>
>>>The strLobID variable always gets assigned:
>>>'{00000000-0000-0000-0000-000000000000}'
>>>
>>>What are some things I could look for ?
>>>
>>> With rsLob
>>> .AddNew
>>>
>>> !FullName = rsSHAPE!LNAME
>>> !FirmName = rsLobbyistDetail!LFIRMNAME
>>> !oAddress = rsLobbyistDetail!LADDR1
>>> !oAddress1 = rsLobbyistDetail!LADDR2
>>> !oCity = rsLobbyistDetail!LCITY
>>> !oState = rsLobbyistDetail!LSTATE
>>> !oZip = rsLobbyistDetail!LZIP
>>> !oPhone = rsLobbyistDetail!LPHONE
>>> !hAddress = rsLobbyistDetail!LADDR1
>>> !hCity = rsLobbyistDetail!LCITY
>>> !hState = rsLobbyistDetail!LSTATE
>>> !hZip = rsLobbyistDetail!LZIP
>>> !hPhone = rsLobbyistDetail!LPHONE
>>>
>>> .Update
>>>
>>> strLobID = rsLob!LobbyistID
>>>
>>> End With
>>>
>>>
>>>Thanks !
>>>
>>>Keep up the *great* work !
>>>
>>>Bill Hilburn
>>>Senior Analyst / Programmer
>>>Nebraska State Legislature
>>>
>>
>