Getting the counter value after an addnew-SQL SERVER 2000/ADO

Getting the counter value after an addnew-SQL SERVER 2000/ADO

Post by Bob Hanse » Thu, 08 Mar 2001 01:53:19



I am using SQL Server 2000 and am having problems with the code I have been
using forever with SQL 7 and now it is not working.  Here is the deal...

I am doing an update on data that has just been inserted into the database.
In order to get the value of the ID I set the bookmark to the existing
bookmark and get the ID after that movement.  No more.  I get the following
error...

"Provider cannot determine the value. Possible reasons: The record was just
created, the default value for the field was not available, or the user has
not set a new value."

The following code was executed...
rsData.Open strSQL, DB_CONNECT, adOpenKeyset, adLockOptimistic
    If mudtProps.m_isNew Then rsData.AddNew
    'fill data fields
    With rsData
        .Fields("CC_DESC") = Trim$(mudtProps.m_desc) & sEmpty
        .Update
        If mudtProps.m_isNew Then
            .Bookmark = .Bookmark
            mudtProps.m_id = .Fields("CC_ID")
        End If
        .Close
    End With

It is blowing up on the line that says .Bookmark = .Bookmark

Thanks for any answers to this problem or any suggestions to do it another
way.

Respectfully,

Bob Hansen

 
 
 

Getting the counter value after an addnew-SQL SERVER 2000/ADO

Post by Carl Prothma » Thu, 08 Mar 2001 14:24:43


Bob,
Take a look at the following ADO FAQ
http://www.able-consulting.com/ADO_Faq.htm#Q9

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com


Quote:> I am using SQL Server 2000 and am having problems with the code I have
been
> using forever with SQL 7 and now it is not working.  Here is the deal...

> I am doing an update on data that has just been inserted into the
database.
> In order to get the value of the ID I set the bookmark to the existing
> bookmark and get the ID after that movement.  No more.  I get the
following
> error...

> "Provider cannot determine the value. Possible reasons: The record was
just
> created, the default value for the field was not available, or the user
has
> not set a new value."

> The following code was executed...
> rsData.Open strSQL, DB_CONNECT, adOpenKeyset, adLockOptimistic
>     If mudtProps.m_isNew Then rsData.AddNew
>     'fill data fields
>     With rsData
>         .Fields("CC_DESC") = Trim$(mudtProps.m_desc) & sEmpty
>         .Update
>         If mudtProps.m_isNew Then
>             .Bookmark = .Bookmark
>             mudtProps.m_id = .Fields("CC_ID")
>         End If
>         .Close
>     End With

> It is blowing up on the line that says .Bookmark = .Bookmark

> Thanks for any answers to this problem or any suggestions to do it another
> way.

> Respectfully,

> Bob Hansen


 
 
 

1. Getting default value when using ADO.Addnew method before Update

I'm using an Ado.Addnew method and want to get the default value of a field
BEFORE I update the record.  This always worked fine with Access, but with
SQL 7 I get either a blank string or a Null.  Is there a system setting in
sql server for this?

Can this be done with a trigger?

--
_______________________
Bob Kolo
Video Pipeline, Inc.
http://www.videopipeline.net
http://www.videodetective.com

(800) 638-4321

2. Profiler question

3. getting modified values after calling ado addnew method

4. OPENROWSET WITH OLEDB

5. Identity Field in SQL Server 2000 and AddNew ADO Method

6. User denied on databases not being accessed

7. Getting SQL Server 2000 Username with ADO in VB 6

8. Newbie: Invalid Object Name?

9. Access counter field value during AddNew

10. Getting a SQL Server 2000 database onto SQL Server 7

11. Getting Counter fileds to work in SQL server 6

12. Missing performance counters on SQL server 2000

13. Getting a return value from a SP (SQL 2000, VB 6.0)