Nz Problem in ADO execute

R

Random

Hi,
I have just discovered an error in some code I have written, and
wondered if anyone can offer an explanation.

I have a linked table 'MYTABLE' which sits on Oracle and is linked via
ODBC. I want to update the value of the 'FINANCE' field, but have to
test for NULL before running the update. My code is shown below:

Set cnn = CurrentProject.Connection

curFeesNew = 50
strSCH = "60809662"

strSQL = "UPDATE MYTABLE " _
& "SET FINANCE = Nz(FINANCE,0)+" _
& curFeesNew & " " _
& "WHERE FIN_ID = '" & strSCH & "';"

cnn.Execute strSQL, lngRecCnt, adCmdText +
adExecuteNoRecords

When I execute this, I do not get any errors, but the value of
'FINANCE' is simply set to 50. For example, if the initial value of
'FINANCE' was 25, I would expect the result to be 75. It seems that Nz
is not interpreted correctly, and worse still it does not throw up an
error.

Strangely, if I surround the NZ statement with a CCur(), then it works
fine i.e:
strSQL = "UPDATE MYTABLE " _
& "SET FINANCE = Ccur(Nz(FINANCE,0))+" _
& curFeesNew & " " _
& "WHERE FIN_ID = '" & strSCH & "';"

I know there are various work arounds possible and the above is only a
small snippet of the overall code, but I would like to know why this
causes an error (my boss wants an explanation as well as a solution).

Can anyone help??
Many thanks
Steve
 
T

TC

Are you using Oracle to look at the initial value? Perhaps it looks
different, depending on which end you look from! (for some reason)

So personally, I would add a statement to display the existing value of
FINANCE, and of NZ(FINANCE,0), before I actually did the update. You
shpould also debug.print the sql string, just to make certain that it
is constructed the way you think it will be.

There are no problems with the NZ function AFAIK. It simply must be
something else.

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
R

Random

Thanks for the prompt reply.

I have done all sorts of testing, and I believe there is a problem with
the way in which Nz is interpreted.
As an example if I do:
strSQL = "UPDATE EZI_SCHEDULE " _
& "SET ESC_FINANCED_AMOUNT = Nz(ESC_FINANCED_AMOUNT,60)
" _
& "WHERE ESC_SCHEDULE_ID = '" & strSCH & "';"
The code (and Nz) works as expected, but as soon as I introduce a
mathematical function (see below) the Nz(ESC_FINANCED_AMOUNT,60) always
evaluates to 0:
strSQL = "UPDATE EZI_SCHEDULE " _
& "SET ESC_FINANCED_AMOUNT = Nz(ESC_FINANCED_AMOUNT,60)
+15 " _
& "WHERE ESC_SCHEDULE_ID = '" & strSCH & "';"

Likewise if I use Docmd.runsql instead of cnn.execute, teh code works
correctly.
I cannot find any other explanation, other than an error in the way Nz
is treated by ADO.

Regards
Steve
 
B

Brendan Reynolds

I don't have access (no pun, etc.) to Oracle, so I tested with SQL Server.
I'm afraid I can't answer the 'why', but I did come up with some interesting
(I think!) further information.

1) The problem does not seem to be limited to the NZ function. I can
reproduce it using IIf instead of NZ.

2) Not only does placing CCur() around the NZ expression resolve the
problem, but placing CCur() around the value being added to the result of
the NZ expression also resolves the problem ...

CurrentProject.Connection.Execute "UPDATE dbo_Orders SET TestField =
((NZ(dbo_Orders.Freight, 0)) + CCur(25))"

I'd search the KB to see if this was a known issue - if only I could think
of suitable terms on which to search! :-(
 
T

TC

I still say you should query the value (before you update it) to see
what it is from the "Access end" of the process. It seems you have not
done that yet.

Are you certain that the sql is being processed locally (by
Access/Jet), and not as a passthrough query (ie. on the Oracle end)?

Personally, I'd have to see very compelling evidence, before I'd accept
that the Access NZ function was not working.

Cheers,
TC (MVP Access)
http://tc2.atspace.com
 
J

John Spencer

I have seen NZ in queries return a string value instead of a numeric value.
It is especially apparent in Crosstab queries.

You can usually spot it by looking at the datasheet view of a query. The
solution is to force the datatype using one of the numeric functions such as
CLng, Cint, CDbl, or Val.
 
B

Brendan Reynolds

The problem certainly seems to be related to implicit type conversion in
some way. Interestingly enough, though, it does not occur when the same
query is executed via DAO. The examples below were tested against a copy of
the Orders table from the SQL Server version of Northwind to which I added a
field called 'TestField', with a data type of money.

'this code exhibits the problem ...
CurrentProject.Connection.Execute "UPDATE dbo_Orders SET TestField =
NZ(dbo_Orders.Freight, 0) + 25"

'this code does *not* exhibit the problem
CurrentDb.Execute "UPDATE dbo_Orders SET TestField = NZ(dbo_Orders.Freight,
0) + 25"

As I indicated in an earlier post, the problem is not limited to the NZ
function, I can reproduce it using the IIf function.

'this code exhibits the problem ...
CurrentProject.Connection.Execute "UPDATE dbo_Orders SET TestField =
IIf(dbo_Orders.Freight IS NULL, 0, dbo_Orders.Freight) + 25"

'this code doesn't
CurrentDb.Execute "UPDATE dbo_Orders SET TestField = IIf(dbo_Orders.Freight
IS NULL, 0, dbo_Orders.Freight) + 25"
 
B

Brendan Reynolds

What is odd about all this is that in my tests the field 'Freight' did not
contain any Null values. So I don't think data type conversion fully
explains what's going on here. "33.5" + "25" = "25" is every bit as
incorrect as 33.5 + 25 = 25, right?
 
T

TC

Holy heck, that sounds strange!

What about retrieving the current value through ADO and displaying it?
I still say that this would be useful. It would clarify whether ADO is
doing the right thing to the wrong value, or conversely, the wrong
thing to the right value.

Cheers,
TC (MVP Access)
http://tc2.atspace.com
 
B

Brendan Reynolds

I haven't been able to reproduce the problem when selecting data. Both ADO
and DAO seem to see the result of the NZ() expression as a string, but
convert to double when a numeric value is added. This code ...

Set rsta = New ADODB.Recordset
Set rsta.ActiveConnection = CurrentProject.Connection
rsta.Source = "SELECT (NZ(Freight, 0)) AS TestCol FROM dbo_Orders WHERE
Freight IS NOT NULL"
rsta.Open
Debug.Print rsta.Fields("TestCol").Type
rsta.Close

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT (NZ(Freight, 0)) AS TestCol FROM
dbo_Orders WHERE Freight IS NOT NULL")
Debug.Print rstd.Fields("TestCol").Type
rstd.Close

.... produces the results 202 and 10, adVarWChar and dbText, showing that
both ADO and DAO see the result of the NZ() function as text. However, this
code ...

Set rsta = New ADODB.Recordset
Set rsta.ActiveConnection = CurrentProject.Connection
rsta.Source = "SELECT (NZ(Freight, 0) + 25) AS TestCol FROM dbo_Orders
WHERE Freight IS NOT NULL"
rsta.Open
Debug.Print rsta.Fields("TestCol").Type
rsta.Close

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT (NZ(Freight, 0) + 25) AS TestCol
FROM dbo_Orders WHERE Freight IS NOT NULL")
Debug.Print rstd.Fields("TestCol").Type
rstd.Close

.... produces the results 5 and 7, adDouble and dbDouble, showing that both
ADO and DAO convert to double when adding the numeric value in this
scenario.

I also tried updating the data via an ADO recordset rather than via the
Execute method of the Connection object, and this does not exhibit the
problem either. Here's the test code ...

Set rsta = New ADODB.Recordset
With rsta
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "SELECT Freight, (NZ(Freight,0)+25) AS TestFreight, " & _
"TestField FROM dbo_Orders"
Do Until .EOF
.Fields("TestField") = .Fields("TestFreight")
Debug.Print "|"; .Fields("Freight"); "|", _
"|"; .Fields("TestFreight"); "|", _
"|"; .Fields("TestField"); "|"
.MoveNext
Loop
.Close
End With

.... and here's a sample of the (correct) output ...

| 24.12 | | 49.12 | | 49.12 |
| 2.79 | | 27.79 | | 27.79 |
| 4.13 | | 29.13 | | 29.13 |
| 31.14 | | 56.14 | | 56.14 |
| 14.01 | | 39.01 | | 39.01 |

So, the problem does not seem to be a general ADO problem, it seems, so far,
to be restricted to the Execute method.
 
B

Brendan Reynolds

BTW: Previous tests have used the Execute method of the Connection object.
This code, using the Execute method of the Command object, also exhibits the
problem ...

Set cmm = New ADODB.Command
With cmm
.ActiveConnection = CurrentProject.Connection
.CommandText = _
"UPDATE dbo_Orders SET TestField = NZ(dbo_Orders.Freight, 0) +
25"
.CommandType = adCmdText
.Execute
End With

I suppose this is probably to be expected, I wouldn't be surprised if they
both call common code behind the scenes.
 
B

Brendan Reynolds

I don't think this tells us anything further about the cause of the problem,
but another work-around is to by-pass the linked table and open a new
connection to the server. This produces the correct results ...

Const strcConnection As String = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=Northwind;Data Source=(local)"

Dim cnn As ADODB.Connection
Dim cmm As ADODB.Command

Set cnn = New ADODB.Connection
cnn.ConnectionString = strcConnection
cnn.Open

Set cmm = New ADODB.Command
With cmm
Set .ActiveConnection = cnn
.CommandText = "UPDATE dbo.Orders SET TestField = " & _
"(ISNULL(dbo.Orders.Freight, 0) + 25)"
.CommandType = adCmdText
.Execute
End With

cnn.Close
 
R

Random

Thanks for all the input, at least I feel some comfort that I haven't
done too much wrong. Thanks particularly Brendan for researching the
failure of the iif statement, which I was going to use as my first work
around. We now have a new policy at work - do not use .Execute in ADO
when the SQL will calculate new values. It's a bit of a nuisance as I
have just moved away from DAO as I believed it to be 'old-technology' !

Thanks again.
 
B

Brendan Reynolds

That seems a bit drastic. The workaround of using a type-conversion function
like CCur() appears to work well.
 
B

Brendan Reynolds

Want to hear something really weird? I can't reproduce it today!

It occurred to me that the problem might be specific to a particular version
of ADO. So I changed my ADO reference to another version, and then attempted
to reproduce the problem. I couldn't. "Aha!" thought I, "I was right, it is
version-specific." So I started going through the various ADO versions,
trying to track down the version or versions that exhibited the problem. And
none of them did, not even the version (2.1) that I was using when I
reproduced the problem - several times - yesterday!
 
B

Brendan Reynolds

Oops! Please ignore the previous post. A silly mistake on my part. In the
course of testing, I've been commenting and uncommenting lines of code, and
I inadvertently left a line uncommented so that the code was actually
updating the data twice, the second time using one of the work-arounds, so
that it only *appeared* that the problem was not reproduced.
 
B

Brendan Reynolds

Now that I've fixed the test code, I can confirm that the problem is
reproducible using ADO 2.0 and ADO 2.8 (the earliest and latest versions on
this PC).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top