VB6 ADO Set rs=command.Execute

Question

Set rs = command.Execute 
if not rs.EOF then
   'logic here
end if

The above code fails at line 2, because rs is closed(probably the oledb provider figured out, wrongly, that it is an insert command so no need to return anything). The command is an insert statement something like:

Insert into log(Name,Value) values("xxx", 123); select scope_identity()

I need the identity back from the server in one roundtrip. Any ideas?

PS: Updated insert statement with field names(thanks Eduardo), but that is not the problem.

Solution 1

Your problem is that the data source is returning two recordsets. This is due to the NOCOUNT setting of the database. The first recordset is used just to return the records affected from the insert statement. THe second recordset returns the results of the SELECT SCOPE_IDENTITY call. So you need to do:

If rs.State = adStateClosed Then
   Set rs = rs.NextRecordset()
End If

Then you can check EOF and all that. But I usally avoid all this and put stuff like this in a stored procedure. Then I SET NOCOUNT ON in the stored procedure. And return the id at the end of the stored procedure. Right now your inserts might just be simple like that but the logic could grow. By putting it in a stored procedure you can just change the stored procedure and not have to change your compiled VB app. It also isolates the database code a bit.

What you don't want to do is SET NOCOUNT ON in your statement there. That I think affects the whole connection if not the whole database.

Solution 2

Select @@Identity From TableName should give the last inserted id

Solution 3

Is this SQL Server? Try adding SET NOCOUNT ON at the start of your SQL e.g.

SET NOCOUNT ON;
Insert into log(Name,Value) values("xxx", 123); 
select scope_identity()