AspDB Grid Bug when using Oracle

[Chris]

ASP-DB does not correctly handle NULL values when using Oracle as the backend. I use the following code to display and filter records:

Set MyDb=Server.CreateObject("AspDB.View&#34
MyDb.dbDBType = "ORACLE"
'use a unique unit identifier
MyDb.dbUnit = 1015
'Set its std properties
MyDb.dbNameMap = Session("AliasInfo&#34
MyDb.dbDSN="Dsn=X;UID=SA;PWD=SECRET"
MyDb.dbSQL="ACCTS"

'Use EMPTY_TBL table for dropdown values; since it has no records in it
'only Null will show up in the dropdown
MyDB.dbFilterDropFlds="(;,/+SSN,,EMPTY_TBL,ANYFIELD,,AddNull;"

MyDb.ASPdbView

When the form in submitted with the aspDebug=2 argument appended, the Setfilterinsql dump variable is:

Setfilterinsql=SELECT * FROM ACCTS WHERE PCT_DEFERRAL = Null

This syntax is not correct; Oracle uses IS NULL and IS NOT NULL to filter on fields for NULL values. The Setfilterinsql dump variable should read:

Setfilterinsql=SELECT * FROM ACCTS WHERE PCT_DEFERRAL IS Null

There should also be some means to filter do this:

Setfilterinsql=SELECT * FROM ACCTS WHERE PCT_DEFERRAL IS NOT Null

Thanks!

[Frank]

Chris,

Is that so ! OK, I&#39;ll detect ORACLE and = NULL or <> NULLand change it to IS NULL and IS NOT NULL and I&#39;ll send you a DLL. OH ! BTW, might as well get you into the beta tests which I&#39;ll release tonite. Do you have time to do that ? Are you a registered user yet ?

Frank



On 2/3/99 10:13:00 AM, Chris wrote:
> ASP-DB does not correctly handle NULL values when using Oracle as the
> backend. I use the following code to display and filter records:

Set
> MyDb=Server.CreateObject(&#34;AspDB.View&#34
MyDb.dbDBType =
> &#34;ORACLE&#34;
&#39;use a unique unit identifier
MyDb.dbUnit =
> 1015
&#39;Set its std properties
MyDb.dbNameMap =
> Session(&#34;AliasInfo&#34
MyDb.dbDSN=&#34;Dsn=X;UID=SA;PWD=SECRET&#34;

> MyDb.dbSQL=&#34;ACCTS&#34;

&#39;Use EMPTY_TBL table for dropdown values;
> since it has no records in it
&#39;only Null will show up in the
> dropdown
MyDB.dbFilterDropFlds=&#34;(;,/+SSN,,EMPTY_TBL,ANYFIELD,,AddNull;&
> #34;

MyDb.ASPdbView

When the form in submitted with the aspDebug=2
> argument appended, the Setfilterinsql dump variable
> is:

Setfilterinsql=SELECT * FROM ACCTS WHERE PCT_DEFERRAL = Null

This
> syntax is not correct; Oracle uses IS NULL and IS NOT NULL to filter on
> fields for NULL values. The Setfilterinsql dump variable should
> read:

Setfilterinsql=SELECT * FROM ACCTS WHERE PCT_DEFERRAL IS
> Null

There should also be some means to filter do
> this:

Setfilterinsql=SELECT * FROM ACCTS WHERE PCT_DEFERRAL IS NOT Null

Thanks!

Is that so ! OK, I&#39;ll detect ORACLE and = NULL or <> NULLand
> change it to IS NULL and IS NOT NULL and I&#39;ll send you a DLL. OH ! BTW,
> might as well get you into the beta tests which I&#39;ll release tonite. Do
> you have time to do that ? Are you a registered user yet?

Yes, I am a registered user and I could do some beta testing.