SQL 7.0 and UniqueIdentifier Field


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: SQL 7.0 and UniqueIdentifier Field

  1. #1
    Bill Hilburn Guest

    SQL 7.0 and UniqueIdentifier Field



    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

    Share on Google+

  2. #2
    Bill Vaughn Guest

    Re: SQL 7.0 and UniqueIdentifier Field


    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
    >


    Share on Google+

  3. #3
    Bill Vaughn Guest

    Re: SQL 7.0 and UniqueIdentifier Field


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

    >


    Share on Google+

  4. #4
    Fred2 Guest

    Re: SQL 7.0 and UniqueIdentifier Field


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

    >


    Share on Google+

  5. #5
    Bill Vaughn Guest

    Re: SQL 7.0 and UniqueIdentifier Field


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

    >>

    >


    Share on Google+

Similar Threads

  1. Stored Procedure Issue in SQL 2000
    By Anthony in forum Database
    Replies: 1
    Last Post: 10-10-2001, 08:35 PM
  2. Saving data to a SQL Server uniqueidentifier field
    By Ryan Schmitt in forum VB Classic
    Replies: 0
    Last Post: 03-23-2000, 05:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center