problem editing adodb recordset

[Bamboozled]

I've created a recordset in VB Express 2008 as follows

rsmembers = New ADODB.Recordset
with rsmembers
            .Source = cn.Execute("SELECT * FROM members")
            .ActiveConnection = cn

            .CursorType = ADODB.CursorTypeEnum.adOpenDynamic
            .LockType = ADODB.LockTypeEnum.adLockOptimistic
            .CursorLocation = ADODB.CursorLocationEnum.adUseServer

            .Open("members", Options:=ADODB.CommandTypeEnum.adCmdTableDirect)
            .movefirst
            .Fields("Active").Value = vbFalse

but the error window tells me that .fields("active") active is readonly. Tried using cursortype as adopenkeyset and tried without the Source property being set as well but same error.

Can anybody tell me how to this please.

[jlego]

Private ADOConnect As New ADODB.Connection            
Dim ADOCmd As New ADODB.Command

            ADOConnect.Open(My.Settings.DSN, My.Settings.DBUser, My.Settings.DBPass)
            ADOCmd.ActiveConnection = ADOConnect
            ADOCmd.CommandText = "UPDATE members SET Active = 0 "            ADOCmd.Execute()
            ADOConnect.Close()
            ADOCmd = Nothing

[Bamboozled]

Thanks for the reply. That is not what I am trying to achieve but I left out some of the code to make the post short.

What I need to do is scan down through each record in the members file and if one of a number of conditions is true I need to edit the record to make the member inactive. In DAO I could do this using Edit/Update so I assumed in ADO there would be something similar. It would seem awfully inefficient to have to run an update query every time I needed to change something in a record.

[jlego]

you wouldnt have to run an update for each row.

UPDATE members SET active = 0 WHERE condition1 = requirement or condition2 = requirement AND (condition3 = requirement AND condition4 = requirement)

would update everything with the specific conditions.

[Bamboozled]

I could do this but it will not be easy as some of the conditions involve date comparisons but the date field can be null.

But can I take it from your replies that there i no ADO equivalent to DAO's edit?

Thanks.

[jlego]

i dont know i am not familiar with dao - i was just giving you alternatives.

date comparisons arnt hard, if you google them you will find ton of results.

SELECT IF(ISNULL(datecolumn, 0, datecolumn) as datecolumn FROM table WHERE datecolumn <> 0

i think the date comparison technique is called datediff, im not positive though. if you want some examples on that im sure i could get some together for you

[jlego]

sorry

select date from table where date is not null

thats the correct where for selecting no null dates