Select for update in Recordset

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

Code:

---

  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 :eek2:

Quote:

---

Originally Posted by 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

Code:

---

        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

Code:

---

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

@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:

Code:

---

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

---

Good luck...:thumb: