Switch() causes strange ADO result


O

onedaywhen

Can anyone explain this?

Sub test()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
..ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb"
..Source = "SELECT Switch(1=1, MyIntegerCol, 0=1, NULL) FROM MyTable"
..Open
MsgBox .Fields(0).Value
MsgBox .Fields(0).Type = adVarBinary
End With
End Sub

Test table:

CREATE TABLE MyTable (
MyIntegerCol INTEGER NOT NULL
)
;
INSERT INTO MyTable VALUES (2)
;

Many thanks,
Jamie.

--
 
Ad

Advertisements

G

Guest

I have never used 'switch' in SQL: I would use Case When. However, I can see
what the 'Switch' might be intended for.

I believe the problem is that the record set does not return 2, as expected.
When I tried:

SELECT Switch(1=1, MyIntegerCol, 0=1,90) FROM MyTable

the recordset does return 2.
Therefore, I would GUESS that it is not possible to specify a missing value
using the word NULL.
 
L

Lynn Trapp

Apparently, Jet is confused about the datatype. Try modifying your .Source
argument like this:

..Source = "SELECT CInt(Switch(1=1, MyIntegerCol, 0=1, NULL)) FROM MyTable"
 
O

onedaywhen

AA2e72E said:
I have never used 'switch' in SQL: I would use Case When.

With Jet? AFAIK it is not supported.
Therefore, I would GUESS that it is not possible to specify a missing value
using the word NULL.

Because the expression 0=1 can never be true it should never actually
return NULL. I guess in common with the IIF function, all clauses are
evaluated regardless.

Thanks,
Jamie.

--
 
O

onedaywhen

Lynn said:
Apparently, Jet is confused about the datatype. Try modifying your
.Source argument

Thanks for suggesting a workaround. However, the query is merely
academic. I'm interested in source of the problem or at least why the
result is of type adVarBinary.

Thanks again,
Jamie.

--
 
L

Lynn Trapp

Thanks for suggesting a workaround. However, the query is merely
academic. I'm interested in source of the problem or at least why the
result is of type adVarBinary.


Well, it is quite likely a bug in Jet. Several of the MVPs have discussed it
among ourselves and we don't really know what the source of it is, unless it
is a bug.
 
Ad

Advertisements

L

Lynn Trapp

Not that I know of. Someone saw your post in the excel newsgroup and several
of us started looking into it.
 
M

Michel Walsh

Hi,


Works fine without explicit recordset, both in ADO and in DAO:


----------------
CurrentDb.Execute "CREATE TABLE MyTable (MyIntegerCol INTEGER NOT NULL)"
CurrentDb.Execute "INSERT INTO MyTable VALUES (2)"
? CurrentDb.OpenRecordset("SELECT Switch(1=1, myintegerCol, 0=1, Null) FROM
MyTable").Fields(0).Value
2
? CurrentProject.Connection.Execute("SELECT Switch(1=1, myintegerCol, 0=1,
Null) FROM MyTable").Fields(0).Value
2
-----------------

It also works fine within Access:


---------------
Public Sub NullSwitch()
Dim rst As New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Switch(1=1, MyIntegerCol, 0=1, NULL) FROM MyTable"
.Open

Debug.Print .Fields(0).Value
End With
End Sub
-------------
returns 2 in the immediate debug window.


Is it possible you didn't create the table into ANOTHER database (or in a db
different than the one you open with the specified connection) ? and that
in your C:\Tempo\New_Jet_DB.mdb, the table MyTable (quite common name)
already has a record where MyIntegerCol ... is null.






Vanderghast, Access MVP
 
Ad

Advertisements

O

onedaywhen

Works fine without explicit recordset, both in ADO
and in DAO

It makes no difference for me. You don't reveal you connection string
but from the DAO comparison I suspect you are using the 3.51 OLE DB
provider (my code as posted uses the 4.0 provider). Please confirm.
Thanks again,
Jamie.

--
 
Ad

Advertisements


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