Re: Nz Problem in ADO execute

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





.

Relevant Pages

  • Re: update store procedure via vb exe
    ... Just use the Execute method of the Connection object (you ARE using ADO, ... to run an ALTER PROCEDURE statement. ...
    (microsoft.public.vb.general.discussion)
  • Re: update store procedure via vb exe
    ... Just use the Execute method of the Connection object (you ARE using ADO, ... to run an ALTER PROCEDURE statement. ...
    (microsoft.public.vb.database)
  • VB6 ADO SQL2005 problem
    ... I use ADO to update data in SQL2005 db from vb application. ... It updates about 400 records with Execute method from Connection object ...
    (microsoft.public.vb.database.ado)
  • Re: add records to table through ado
    ... Does that syntax use ADO? ... I'd suggest the Execute method of the ADO ... Connection object: ...
    (microsoft.public.access.formscoding)
  • Re: ping DelM: coInitialize
    ... that if an ADO connection object exists, ... never had to actively call coinit or couninit in any of my ISAPI programs. ...
    (borland.public.delphi.database.ado)