Provider Not Capable of Request

I am getting the "3251 - Provider is not capable of request...." error when I run this code. I get the error on the first "calc.Update". Can anyone tell me why?

I have a feeling that it has something to do with the "Order By" clause, but it is pertinent to have it in there. Anyone have any suggestions?


calc.Open "Select date, credit, debit, accountbalance " & _
"FROM creditandDebit WHERE Account = '" & Replace(gstrTemp, "'", "''") & "' " & _
"order by date, time asc ;", cnn, adOpenDynamic, adLockOptimistic

If IsNull(calc!credit) Then
strBalance = -(calc!debit)
Else
strBalance = calc!credit
End If

calc.Update
calc!accountbalance = strBalance
calc.Update

etc......

Code:

---

calc.Open "Select date, credit, debit, accountbalance " & _
"FROM creditandDebit WHERE Account = '" & Replace(gstrTemp, "'", "''") & "' " & _
"order by date, time asc", cnn, 3, 3

If IsNull(calc!credit) Then
strBalance = -(calc!debit)
Else
strBalance = calc!credit
End If

calc.Update
calc!accountbalance = strBalance
calc.Update

---

try that, it shoudl wourld

Thanks for the suggestion, but it's still giving me the same error...

If I take out the "Order By" clause, it works....

???

'assumption: i believe when you do ORDER BY, it only will let you GET information as your setting the order by...


give this a try (you were missing one of th field names)

Code:

---

calc.Open "Select date, time,  credit, debit, accountbalance " & _
"FROM creditandDebit WHERE Account = '" & Replace(gstrTemp, "'", "''") & "' " & _
"order by date, time asc", cnn, 3, 3

If IsNull(calc!credit) Then
strBalance = -(calc!debit)
Else
strBalance = calc!credit
End If

calc.Update
calc!accountbalance = strBalance
calc.Update

---

===========================================
wait wait wait (sorry i coudnt see it before, 2 am here)

you dont even need .update
now that i read your code.
why are you doing .update? i mean between the first update and the opening of the record, i dont see anywhere that your modifiction the record, you only do .update if you modify the recordset (infact you dont even need .update, all you need to do is calc!accountBalance = strBalance
when you do that
it does the update automatically
(i never use update, it commits as soon as i set it)

If your assumption is right, then this code is impossible then?

If not, here is my full code:

********************************************
calc.Open "Select date, time, credit, debit, accountbalance " & _
"FROM creditandDebit WHERE Account = '" & Replace(gstrTemp, "'", "''") & "' " & _
"order by date, time asc;", cnn, adOpenKeyset, adLockOptimistic


If IsNull(calc!credit) Then
strBalance = -(calc!debit)
Else
strBalance = calc!credit
End If

calc.Update
calc!accountbalance = strBalance
calc.Update

calc.MoveNext

While Not calc.EOF
If IsNull(calc!credit) Then
strBalance = strBalance - calc!debit
Else
strBalance = strBalance + calc!credit
End If

calc!accountbalance = strBalance

calc.MoveNext
Wend

calc.Close
****************************************


When I start, "accountbalance" doesn't have any value at all. I am actually calculating the fields as I go. This is actually a bank ledger, where there might be adjustments to the totals, so ever time the ledger is opened, it has to recalculate the balance. But, for some stupid reason, they say that I have to save the running balance to the database until the next time the ledger is viewed and recalculated. So, I am updating the "accountbalance" field every time this code is run.

if it works WITHOUT ORDER BY then remove it

from the looks of it
your not showing anything so the ORDER by doesnt do anything other than slow it down
you know every record returned will be modified
so if record 1 goes first, or record 10, they all will get updated

*am still not exactly sure what your trying to do in general*

The more I think about it, I think you are right. I don't know if you can update a recordset when you use "Order By".

The balances are coming out correct, as far as I can tell, but they are not showing up in the right order. Example (this is what I am showing in a dataGrid....I have omitted that part of the code)

Date Credit Debit Balance
------ --------- -------- ---------
6/6/01 $15,123.10 $15,123.10
6/6/01 $1,000.00 $14,123.10
6/6/01 $1,200.00 $20,823.60
6/6/01 $5,500.50 $19,623.60

As you can see, the order is wrong because the 4th row should come before the 3rd row. Therefore, they will think the end balance is $19,623.60 instead of the correct $20,823.60.

That is not understandable because the spaces were deleted in my "mock table". I have attached a screen print of how I really typed that:

you want to update the balances
then display them?

why not remove the ORDER BY from the modification code
do all your updating ect

then do another open just for showing the info (use order by)
(safer, more effecient, depends on how you look at it)

what screen?

The only problem with that is that they need to be able to edit the cells in the datagrid.

Sorry, the screen is there now. I didn't know you couldn't attach Word files, so I had to make it a jpeg

I am going to go home and get some sleep now. Maybe I will be able to think better on a fresh mind. It's 2AM here. I am soooo glad I have tomorrow off!

Thanks for your help. If you think of anything. I may be back on Friday night to check it out.

Thanks Again!