Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO auto number (VB6 SP5)

    Can anyone tell me why / how to get back an Identity field from a SQL Server database connected to as an ADO Recordset?

    I've go the code from someone else in the office, and he first creates a empty recordset (SQL has a Where 1 = 2 clause)
    He then set various of the fields to the values he wants, finally does an .Update to push the data back into SQL Server

    Then for an audit, he puts the MsgId field (the Identity) into another table

    I've got SQL Server running, (only 7 whereas he's got 2000), and when I look at the recordset in Watch, the field is Empty

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO auto number (VB6 SP5)

    If you simply insert a record into the SQL table, do you get a value in the identity field? In other words, are you sure you have the identity seed and the identity increments set? If you do and you have, then the only reason I could think of would be that you haven't updated the recordset yet or that you picked the wrong kind of recordset to reflect the value back to your front end. Are you using a client-side or server-side cursor?
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO auto number (VB6 SP5)

    Charlotte,

    yes the field increments by one each time I add a new record

    The code first does
    rsLog.Open sql, adoCn, adOpenKeyset, adLockOptimistic, adCmdText

    then
    rsLog.AddNew

    then some work on the fields
    rsLog!OriginatorID = .... value

    then finally
    rsLog.Update

    I can switch to Query Analyzer and the data is in the table, including the new LogId, but the rsLog!LogId is empty - checked with Watch, whereas the other fields have the expected values

    Not sure if I'm using client or server cursor - how do I tell that?

    I'm sure it's down to me as two other guys have been using the DLL this code is in, without an problems (or at least no reported problems)

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO auto number (VB6 SP5)

    If you can return the value in code, then it's there. Why are you trying to view it in Watch?
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO auto number (VB6 SP5)

    Sorry my explanation wasn't clear, the value I'm after (the rsLog!LogId) isn't there, so I can't put it in my audit table
    I've checked in Watch and the recordset has the values I've added in code, e.g. the OriginatorId, but the autonumber LogId (or whatever it's called in SQL Server speak) isn't in the recordset, even after the Update
    I'm unsure how to refresh the recordset - tried the .Refresh and .Resync and neither did the job

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO auto number (VB6 SP5)

    Do you mean that if you put in a statement like

    MsgBox rsLog!LogID

    after the rsLog.Update command, you don't see the LogID? It certainly works on my machine.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO auto number (VB6 SP5)

    Yeap I just get a blank dialog !!
    - the code in my test forms button

    Dim adoCn As New ADODB.Connection
    Dim rsLog As New ADODB.Recordset

    Dim sql As String
    sql = "SELECT * FROM tblLogIn WHERE 1 = 2 "

    adoCn.open msGatewayDbConStr
    rsLog.open sql, adoCn, adOpenKeyset, adLockOptimistic, adCmdText

    rsLog.AddNew
    rsLog!OriginatorAddr = "TestAddr"
    rsLog.Update

    MsgBox ":" & rsLog!LogId & ":"
    MsgBox ":" & rsLog!OriginatorAddr & ":"

    rsLog.Close

    What is weird is that I change the UDL to point at an Access 2000 database with a copy of the tblLogin database in, and the display of the LogId autonumber works <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO auto number (VB6 SP5)

    May I ask why you're doing this:

    sql = "SELECT * FROM tblLogIn WHERE 1 = 2 "

    That opens an empty recordset, but there isn't really any point to that since all you're doing is adding a new record.

    As for it working in another database, it sounds like you've got a corrupted tblLogin in the database you're working with.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO auto number (VB6 SP5)

    Charlotte,

    to be honest, I've no idea why he's doing that - this is code from another developer, who put together the original DLL. He's no longer available, so I've been given the task of doing the next DLL, so I'm on a crash course to try and learn ADO, XML, ASP and also produce the DLL - stressed, somewhat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    I'd seen this and assumed it was needed so the recordset fields would be defined.

    I linked the tblLogIn table in an Access 2000 database, and the autonumber works <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I'll have a go at a new SQL Server database, and a new table

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO auto number (VB6 SP5)

    On the sql question, I wonder if it's his way to make sure the returned recordset has minimal (i.e. zero) records in it ?

    On the missing LogId, tried a new database and table in SQL Server, same problem
    Tried an Access 2000 table with Autonumber, works fine <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Then (with SQL Server connection), tried looking at the LogId after the AddNew, but adding a MoveNext MovePrevious, and now the LogId is there <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    So quite how/why/what the recordset is doing I've no idea <img src=/S/weep.gif border=0 alt=weep width=21 height=16>

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO auto number (VB6 SP5)

    Now upgraded to SQL Server 2000 and it works Ok, so something to do with 7 ??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •