Stuck on a disconnected recordset concept

[Axoliien]

I have been working at this all day, but here is the idea. I have a stored procedure which returns a recordset of employee information to the client. The recordset returned includes EmpID, EmpName, and EmpAsgn (assigned).

CREATE PROCEDURE dbo.upProjTrack_RetRSEmps AS

SET NOCOUNT ON

SELECT EmpID,
       EmpLogin,
       CAST(0 AS SMALLINT) AS EmpAsgn
FROM tblEmployees
WHERE Inactive = 0

GO

I want to do the following:

1) Connect to SQL Server and retrieve the recordset
2) Disconnect the recordset
2) Populate a listbox of employees unassigned
3) Populate a listbox of employees assigned

These all work fine. The next part is what breaks. I want to allow a user to mark an employee as assigned in the local recordset, but since SQL Server doesn't have a boolean value, I return as a SmallInt, which corresponds to the Access Integer type. When I try to update the local recordset though by changing the value from 0 to 1, I get an error

Run-time Error '-2147217887 (80040e21)':
Multiple-step operation generated errors. Check each status value.

Research has proven inadequate, but I have gathered that the ADO Error is telling me that my data types are incompatible. I have tried so many different types in SQL Server and within Access, but absolutely cannot get past this error.

In case you are wondering why I update a disconnected recordset instead of connecting it, the assignments are stored into a separate table with one-to-many relationships. This is a client-side part of my project.

Static Value Example:

   Dim ProjAssignments as ADODB.Recordset
   Dim cnn As ADODB.Connection
   Set ProjAssignments = New ADODB.Recordset
   Set cnn = New ADODB.Connection
   cnn.Open GlobalVars.gcStrPath
   
   With ProjAssignments
      Set .ActiveConnection = cnn
      .LockType = adLockOptimistic
      .CursorType = adOpenStatic
      .CursorLocation = adUseClient 'adUseClient creates a disconnectable recordset
      .Open "upProjTrack_RetRSEmps", Options:=adCmdStoredProc
      Set .ActiveConnection = Nothing 'Disconnect the recordset
   End With
   
   If Not cnn Is Nothing Then
      If cnn.State <> adStateClosed Then
         cnn.Close
      End If
      Set cnn = Nothing
   End If

   With ProjAssignments
      .MoveFirst
      .Find "EmpID = 999999"
      If Not .EOF Then 'Found the employee
         !IsAsgn = 1 [COLOR=red]'<<BREAKS HERE[/color]
         .Update
      End If
   End With

 

[cmmrfrds]

I don't see error trapping. I recommend looping through the errors collection to make sure that you see any underlying error. Something like.

Public Function Dosomething()
On Error GoTo ErrHandler
....... some code
Exit Function
ErrHandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.Description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.Description
Debug.Print "err source = "; Err.Source
Next
End Function

Also, display any other relevant information, such as the state.
Debug.Print cnn.State etc.....

 

[Axoliien]

Yes, I have error handling, I just pulled out the stuff I am working on here.

If you notice, there is no ADO error trappable for the disconnected recordset, since it is disconnected. I receive an error message from the system, however there is no error on the connection. The two are separate objects, and in this instance my disconnected recordset is not relying upon the connection once it is disconnected.

I have made a separate recordset and added my own definition for columns and types, then created a function to move the data from the retrieved recordset to my local recordset, which was never connected. Still having trouble with updating the disconnected one locally, without connecting again.

 

[Axoliien]

By the way, in my Stored Procedure, it is actually

CAST(0 AS SMALLINT) AS IsAsgn

So that's not it either...

 

[cmmrfrds]

The column IsAsgn does not exist in sql server, so that fact might be noted in some property on the recordset. If you did try to do a batchupdate on the sql server recordset it would error. You could try locking BatchOptimistic.

Are you only putting the 0 or 1 in the recordset to use locally and then plan on killing the recordset without any updates to sql server? This seems like it may be problematic because of the locking, which sql server is probably aware of and somehow flagged that in the recordset. One way to get around would be to create another disconnected recordset on the fly and then equate the recordset you have to the new recordset.

Set rsnew = ProjAssignments

 

[Lightning]

You don't seem to have an Edit statement in your code.

With ProjAssignments
      .MoveFirst
      .Find "EmpID = 999999"
      If Not .EOF Then 'Found the employee
         [COLOR=blue].Edit[/color]
         !IsAsgn = 1 
         .Update
      End If
   End With

On-Line Help: Edit Method


HTH
Lightning

 

[cmmrfrds]

Lightning, ADO does not require an Edit, the Update is sufficient.

 

[Lightning]

OK - that's the new thing I learned today!

L