Multiple-step operation generated errors

[bmcc]

I am using ADO. The following SQL query will retrieve 1 record correctly.


Select Distinct hrSsn as hrSn,
' ' as OsnText
From table_abc
Where hrSsn = 119928443
For Fetch Only

But the following code gives error:
"Multiple-step operation generated errors". Debug pinpoint the problem is on the .Fields("OsnText").Value = pp, wonder how to solve it? Many thanks!


With frsOsn
Dim pp As String
pp = Format(CStr(.Fields("TrueOsn").Value), "000-00-0000")
.Fields("OsnText").Value = pp
End With

 

[petermeachem]

the query retrieves one record consisting of a blank and hrsn which is equal to 119928443
I can't see any point in that at all?
Then you use TrueOsn which isn't in the select
As the select says 'for fetch only', not a term I use, but does it mean readonly? If so, you aren't going to be writing it.
A select distinct possibly represents many records, an update isn't going to work is it. You can't update many records like that.

I think what you should be doing is an UPDATE query
csql = "UPDATE table_abc SET OsnText = TrueOsn Where hrSsn = 119928443"
connection.execute csql
What you do with the Format depends on database and field type.

http://www.accuflight.co.uk

http://www.lettersdirect.co.uk

http://www.trelliscoaching.com

http://www.fengshuimarketingcenter.com

http://www.ourhouseinportugal.co.uk

 

[vladk]

bmcc,

I support petermeachem. In addition, I could add that DISTINCT is not efficient since it requires sorting. If you indeed need to know if at least on row exists with some field value, you might want to try something like in ORACLE: Where ROWNUM =1

vladk

 

[vladk]

bmcc,
Another option could be adding unique index so the query would be more selective to avoid DISTINCT

vladk

 

[petermeachem]

yehbut he doesn't need distinct, as far as I can tell, because he should be doing an update not a select

 

[vladk]

Yes, if our guess is correct. So far, it is hard to tell what exactly he needs since there is no connection between two DB episodes.