Re: Nz Problem in ADO execute


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

--
Brendan Reynolds
Access MVP

"Brendan Reynolds" <brenreyn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:eNF46qbTGHA.4616@xxxxxxxxxxxxxxxxxxxxxxx
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.

--
Brendan Reynolds
Access MVP

"Brendan Reynolds" <brenreyn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ezqcflbTGHA.1576@xxxxxxxxxxxxxxxxxxxxxxx

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.

--
Brendan Reynolds
Access MVP

"TC" <aatcbbtccctc@xxxxxxxxx> wrote in message
news:1142989326.205326.73100@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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







.