ADO auto number (VB6 SP5)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

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

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

Reply | Quote

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

Then (with SQL Server connection), tried looking at the LogId after the AddNew, but adding a MoveNext MovePrevious, and now the LogId is there

So quite how/why/what the recordset is doing I’ve no idea

Reply | Quote

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

Reply | Quote