ADO Update Problem ***URGENT***

[ufobaby]

hi, am new to ADO and the below code (for updating recordset) has some problem
================> Code
cnn.Open (varGlobal.connectionstring())

rs.Open "SELECT distinct r.cod_location," _
& " r.cod_bankarr,c.txt_locname, c.txt_address" _
& " FROM tbl_RetUpload as r, tbl_corddetails as c " _
& &quot;WHERE r.cod_location=c.cod_location And r.dat_debit<(date()-7) And r.typ_status='N' and r.dat_debit = #&quot; & Format(&quot;03/06/2002&quot;, &quot;mm/dd/yyyy&quot & &quot;#&quot;, cnn, adOpenStatic, adLockOptimistic

Do While rs.EOF = False
vloc = rs.Fields(&quot;cod_location&quot

newrs.ActiveConnection = cnn
newrs.Source = &quot;SELECT distinct r.cod_client,r.nbr_micr, r.dat_debit,&quot; _
& &quot; r.typ_status, r.amt_chkamt,r.cod_bankarr,c.cod_location, c.txt_locname, c.txt_address&quot; _
& &quot; FROM tbl_RetUpload as r, tbl_corddetails as c &quot; _
& &quot;WHERE r.cod_location=c.cod_location And r.dat_debit<(Date() - 7) And r.typ_status='N' and r.dat_debit = #&quot; & Format(&quot;03/06/2002&quot;, &quot;mm/dd/yyyy&quot & &quot;#&quot; _
& &quot;and r.cod_location='&quot; & vloc & &quot;'&quot;
newrs.LockType = adLockOptimistic
newrs.CursorType = adOpenStatic
newrs.Open

Do While newrs.EOF = False
'//// DO STUFF like writting to a word file
newrs(&quot;typ_status&quot = &quot;L&quot;
newrs.Update
Loop

newrs.Close
Set newrs = Nothing

rs.MoveNext
Loop

Give an error when newrs.update happens
-> Error -> Too Few Parameters Expected 11.
if i remove the update stmt. it says the Database is locked for editing.


Niraj

 

[ufobaby]

hi,

i think this is happening because of the join in the query. it allows me to update a query (recordset) w/o any joins

any more info on this ???


Niraj

 

[petermeachem]

I think its the distinct. A unique set of columns is being retrieved and the updat doesn't know which to update. Suppose you had a single column and rows containing
1
1
1
2
2
3
3
A distinct select would be
1
2
3

If you do an update, how is it to know which 1 is to be updated?
Peter Meachem
peter@accuflight.com

 

[petermeachem]

Could be the join too. It needs to be able to identify a record to update. Peter Meachem
peter@accuflight.com

 

[NickISD]

I agree with Peters comments and would add you need to change the newrs.CursorType = adOpenStatic statement to be either adOpenKeySet or adOpenDynamic. Static recordsets support scrolling forward and backward; changes made by other users are not visible.

Regards, Nick