Select for update in Recordset

[mutley]

Hi

How can I do Select for update in recordset, I Must to read a recordset in each processing , I need to update this record

   Set mRsDados = New ADODB.Recordset
        mSql = "SELECT NM_TELA, CD_USUARIO,CD_USUARIO_CC, TP_EMAIL, 
        CD_PERIODO, ID_ORDEM_PROCESSAMENTO FROM SIBTB_USUARIO_EMAIL"
        msql = msql &  " where STATUS = 0"
        mSql = mSql & " ORDER BY ID_ORDEM_PROCESSAMENTO"
        mRsDados.Open mSql, cn, adOpenForwardOnly, adLockReadOnly
        

        Do While Not mRsDados.EOF
            processing....
             'here I neeed to do update in table SIBTB_USUARIO_EMAIL
             'UPDATE SIBTB_USUARIO_EMAIL
             '        SET STATUS = 1 
             '  WHERE ID_ORDEM_PROCESSAMENTO = RS(4)
            mRsDados.MoveNext
         
        Loop
        mRsDados.Close
        Set mRsDados = Nothing

How can I do it , I tried above but no work
Tia

[Spoo]

Hi

How can I do Select for update in recordset, I Must to read a recordset in each processing , I need to update this record

        Set mRsDados = New ADODB.Recordset
        mSql = "SELECT NM_TELA, CD_USUARIO,CD_USUARIO_CC, TP_EMAIL, 
        CD_PERIODO, ID_ORDEM_PROCESSAMENTO FROM SIBTB_USUARIO_EMAIL"
        msql = msql &  " where STATUS = 0"
        mSql = mSql & " ORDER BY ID_ORDEM_PROCESSAMENTO"
        mRsDados.Open mSql, cn, adOpenForwardOnly, adLockReadOnly
        

        Do While Not mRsDados.EOF
            processing....
             'here I neeed to do update in table SIBTB_USUARIO_EMAIL
             'UPDATE SIBTB_USUARIO_EMAIL
             '        SET STATUS = 1 
             '  WHERE ID_ORDEM_PROCESSAMENTO = RS(4)
            mRsDados.MoveNext
         
        Loop
        mRsDados.Close
        Set mRsDados = Nothing

Mutley

Given that the From RS is the same as the Update RS,
I can see why you might be having the problem you mention.

How does this sound for a possible solution -- do 2 passes

• 1st pass collects SQL's "rec#" (or some appropriate indexed field,
  possibly ID_ORDEM_PROCESSAMENTO) into an array
• 2nd pass updates the actual RS by using Seek

Set mRsDados = New ADODB.Recordset
mSql = "SELECT NM_TELA, CD_USUARIO,CD_USUARIO_CC, TP_EMAIL, 
        CD_PERIODO, ID_ORDEM_PROCESSAMENTO FROM SIBTB_USUARIO_EMAIL"
msql = msql &  " where STATUS = 0"
mSql = mSql & " ORDER BY ID_ORDEM_PROCESSAMENTO"
mRsDados.Open mSql, cn, adOpenForwardOnly, adLockReadOnly
----- begin suggested code ---------------
' pass 1 -- collect applicable record numbers in a temporary array
nn = mRsDados.RecordCount
Dim aFlag()                         ' create temporary array
ReDim aFlag(nn)                     ' size the array
ct = 0                              ' initialize counter
Do While Not mRsDados.EOF
    ct = ct + 1                     ' increment counter
    aFlag(ct) = mRsDados("rec#")    ' use an appropriate Field name here that is indexed
Loop
mRsDados.Close
Set mRsDados = Nothing
' pass 2 -- do processing to appropriate records in the RS
For ii = 1 to nn
    SIBTB_USUARIO_EMAIL.Seek "=", aFlag(ii)
    < your update code goes here >
Next ii

Spoo

[akhileshbc]

@mutley: You have opened the recordset using adLockReadOnly. So, you cannot add/edit/delete data, you can only read using it.

So, inoder to edit data, you have to use some other Lock Types. See this: http://www.vbforums.com/showthread.php?t=461226

And for updating values, I think, you can use this format:

rs.Fields("myFieldName").Value=txtNewValue.Text
rs.update

Good luck...