SELECT .. WHERE column IS NULL?

[John]

I just cannot manage to perform a SELECT query with NULL parameter...
My CATEGORY table does have one row where TCATEGORYPARENT ID is null (real DB
null value).

TCATEGORYID and TCATEGORYPARENT ID are uniqueidentifie r columns.

My questions:

- is Type="Object", below, necessary?
- what shall I specify for DefaultValue in my function? I tried everything.
How come the DefaultValue must be a string? Why can't I specify
DBNull.Value?
- How do I make this work....

Thanks for your help.

-J

Code follows:

<asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server" ConnectionStrin g="<%$
ConnectionStrin gs:MyBaseConnec tionString %>"
SelectCommand=" SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY] WHERE
([TCATEGORYPARENT ID] = @TCATEGORYPAREN TID) ORDER BY [TCATEGORYPARENT ID],
[TCATEGORYNAME]">
<SelectParamete rs>
<asp:Paramete r Name="TCATEGORY PARENTID" Type="Object" />
</SelectParameter s>
</asp:SqlDataSour ce>

private void QueryCategory()
{
SqlDataSource1. SelectParameter s["TCATEGORYPAREN TID"].DefaultValue =
System.Data.Sql Types.SqlGuid.N ull.ToString();

DataView dv =
(DataView)SqlDa taSource1.Selec t(DataSourceSel ectArguments.Em pty);

if (dv != null) // I always end up with either dv null or
dv.Table.Rows.C ount = 0 :-(
{
string prefix = new String(' ', depth);
foreach (DataRow row in dv.Table.Rows)
{
[...]
}
}
}

[Tim Stahlhut]

I am not an ASP.NET person so can't help with that but your SQL code appears
to be wrong.
SELECT * FROM tablex WHERE NULL = NULL -- will always return zero rows.

Tim S

Try This

SelectCommand = "
SELECT [TCATEGORYID], [[TCATEGORYNAME]
FROM [TCATEGORY]
WHERE (([TCATEGORYPARENT ID] = @TCATEGORYPAREN TID) OR ([TCATEGORYPARENT ID]
IS NULL AND @TCATEGORYPAREN TID IS NULL))
ORDER BY [TCATEGORYPARENT ID], [TCATEGORYNAME]
"
"John" <no***@replytot group.pleasewro te in message
news:us******** ******@TK2MSFTN GP03.phx.gbl...

>I just cannot manage to perform a SELECT query with NULL parameter...
My CATEGORY table does have one row where TCATEGORYPARENT ID is null (real
DB null value).

TCATEGORYID and TCATEGORYPARENT ID are uniqueidentifie r columns.

My questions:

- is Type="Object", below, necessary?
- what shall I specify for DefaultValue in my function? I tried
everything. How come the DefaultValue must be a string? Why can't I
specify DBNull.Value?
- How do I make this work....

Thanks for your help.

-J

Code follows:

<asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server"
ConnectionStrin g="<%$ ConnectionStrin gs:MyBaseConnec tionString %>"
SelectCommand=" SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY]
WHERE ([TCATEGORYPARENT ID] = @TCATEGORYPAREN TID) ORDER BY
[TCATEGORYPARENT ID], [TCATEGORYNAME]">
<SelectParamete rs>
<asp:Paramete r Name="TCATEGORY PARENTID" Type="Object" />
</SelectParameter s>
</asp:SqlDataSour ce>

private void QueryCategory()
{
SqlDataSource1. SelectParameter s["TCATEGORYPAREN TID"].DefaultValue =
System.Data.Sql Types.SqlGuid.N ull.ToString();

DataView dv =
(DataView)SqlDa taSource1.Selec t(DataSourceSel ectArguments.Em pty);

if (dv != null) // I always end up with either dv null or
dv.Table.Rows.C ount = 0 :-(
{
string prefix = new String(' ', depth);
foreach (DataRow row in dv.Table.Rows)
{
[...]
}
}
}

[John]

Thanks Tim, but your solution is less than ideal, because I would have to
have two different SqlDataSources:

- one to handle the case where I want a NULL parent category (WHERE
[TCATEGORYPARENT ID] IS NULL)
- the other one to handle the case where I provide a parent category (WHERE
[TCATEGORYPARENT ID] = @TCATEGORYPAREN TID)

Don't SelectParameter s work for NULL parameter values as well?

It seems like a severe limitation to me.
"Tim Stahlhut" <st******@netze ro.comwrote in message
news:12******** *****@corp.supe rnews.com...

>I am not an ASP.NET person so can't help with that but your SQL code
appears to be wrong.
SELECT * FROM tablex WHERE NULL = NULL -- will always return zero rows.

Tim S

Try This

SelectCommand = "
SELECT [TCATEGORYID], [[TCATEGORYNAME]
FROM [TCATEGORY]
WHERE (([TCATEGORYPARENT ID] = @TCATEGORYPAREN TID) OR ([TCATEGORYPARENT ID]
IS NULL AND @TCATEGORYPAREN TID IS NULL))
ORDER BY [TCATEGORYPARENT ID], [TCATEGORYNAME]
"
"John" <no***@replytot group.pleasewro te in message
news:us******** ******@TK2MSFTN GP03.phx.gbl...

>>I just cannot manage to perform a SELECT query with NULL parameter...
My CATEGORY table does have one row where TCATEGORYPARENT ID is null (real
DB null value).

TCATEGORYID and TCATEGORYPARENT ID are uniqueidentifie r columns.

My questions:

- is Type="Object", below, necessary?
- what shall I specify for DefaultValue in my function? I tried
everything. How come the DefaultValue must be a string? Why can't I
specify DBNull.Value?
- How do I make this work....

Thanks for your help.

-J

Code follows:

<asp:SqlDataSo urce ID="SqlDataSour ce1" runat="server"
ConnectionStri ng="<%$ ConnectionStrin gs:MyBaseConnec tionString %>"
SelectCommand= "SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY]
WHERE ([TCATEGORYPARENT ID] = @TCATEGORYPAREN TID) ORDER BY
[TCATEGORYPARENT ID], [TCATEGORYNAME]">
<SelectParamete rs>
<asp:Paramete r Name="TCATEGORY PARENTID" Type="Object" />
</SelectParameter s>
</asp:SqlDataSour ce>

private void QueryCategory()
{
SqlDataSource1. SelectParameter s["TCATEGORYPAREN TID"].DefaultValue =
System.Data.Sq lTypes.SqlGuid. Null.ToString() ;

DataView dv =
(DataView)SqlD ataSource1.Sele ct(DataSourceSe lectArguments.E mpty);

if (dv != null) // I always end up with either dv null or
dv.Table.Rows. Count = 0 :-(
{
string prefix = new String(' ', depth);
foreach (DataRow row in dv.Table.Rows)
{
[...]
}
}
}

[Tim Stahlhut]

It is the SQL standard in all databases that claim to be close to SQL 92 let
alone SQL99.

Tim S

"John" <no***@replytot group.pleasewro te in message
news:e0******** ******@TK2MSFTN GP04.phx.gbl...

Thanks Tim, but your solution is less than ideal, because I would have to
have two different SqlDataSources:

- one to handle the case where I want a NULL parent category (WHERE
[TCATEGORYPARENT ID] IS NULL)
- the other one to handle the case where I provide a parent category
(WHERE [TCATEGORYPARENT ID] = @TCATEGORYPAREN TID)

Don't SelectParameter s work for NULL parameter values as well?

It seems like a severe limitation to me.
"Tim Stahlhut" <st******@netze ro.comwrote in message
news:12******** *****@corp.supe rnews.com...

>>I am not an ASP.NET person so can't help with that but your SQL code
appears to be wrong.
SELECT * FROM tablex WHERE NULL = NULL -- will always return zero rows.

Tim S

Try This

SelectComman d = "
SELECT [TCATEGORYID], [[TCATEGORYNAME]
FROM [TCATEGORY]
WHERE (([TCATEGORYPARENT ID] = @TCATEGORYPAREN TID) OR
([TCATEGORYPARENT ID] IS NULL AND @TCATEGORYPAREN TID IS NULL))
ORDER BY [TCATEGORYPARENT ID], [TCATEGORYNAME]
"
"John" <no***@replytot group.pleasewro te in message
news:us******* *******@TK2MSFT NGP03.phx.gbl.. .

>>>I just cannot manage to perform a SELECT query with NULL parameter...
My CATEGORY table does have one row where TCATEGORYPARENT ID is null
(real DB null value).

TCATEGORYID and TCATEGORYPARENT ID are uniqueidentifie r columns.

My questions:

- is Type="Object", below, necessary?
- what shall I specify for DefaultValue in my function? I tried
everything. How come the DefaultValue must be a string? Why can't I
specify DBNull.Value?
- How do I make this work....

Thanks for your help.

-J

Code follows:

<asp:SqlDataS ource ID="SqlDataSour ce1" runat="server"
ConnectionStr ing="<%$ ConnectionStrin gs:MyBaseConnec tionString %>"
SelectCommand ="SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY]
WHERE ([TCATEGORYPARENT ID] = @TCATEGORYPAREN TID) ORDER BY
[TCATEGORYPARENT ID], [TCATEGORYNAME]">
<SelectParamete rs>
<asp:Paramete r Name="TCATEGORY PARENTID" Type="Object" />
</SelectParameter s>
</asp:SqlDataSour ce>

private void QueryCategory()
{
SqlDataSource1. SelectParameter s["TCATEGORYPAREN TID"].DefaultValue =
System.Data.S qlTypes.SqlGuid .Null.ToString( );

DataView dv =
(DataView)Sql DataSource1.Sel ect(DataSourceS electArguments. Empty);

if (dv != null) // I always end up with either dv null or
dv.Table.Rows .Count = 0 :-(
{
string prefix = new String(' ', depth);
foreach (DataRow row in dv.Table.Rows)
{
[...]
}
}
}

[John]

I know there is a standard to respect, but we are talking about the
authoring layer here (ADO.NET). I had thought the authoring layer would
handle this common case.

I have tried using a SqlCommand instead and obtain the same result: a
parameter with a value of NULL doesn't seem to be supported:

using (SqlConnection conn = new
SqlConnection(S qlDataSource1.C onnectionString ))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SEL ECT * FROM TCATEGORY WHERE
TCATEGORYPARENT ID = @TCATEGORYPAREN TID");
cmd.Connection = conn;
SqlParameter p = new SqlParameter("@ TCATEGORYPARENT ID",
SqlDbType.Uniqu eIdentifier);
p.Value = DBNull.Value;
cmd.Parameters. Add(p);

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter( cmd);
da.Fill(ds);
int c = ds.Tables[0].Rows.Count; // I get 0 here, that's wrong, I've got
one row with TCATEGORYPARENT ID being NULL
}

"Tim Stahlhut" <st******@netze ro.comwrote in message
news:12******** *****@corp.supe rnews.com...

It is the SQL standard in all databases that claim to be close to SQL 92
let alone SQL99.

Tim S

"John" <no***@replytot group.pleasewro te in message
news:e0******** ******@TK2MSFTN GP04.phx.gbl...

>Thanks Tim, but your solution is less than ideal, because I would have to
have two different SqlDataSources:

- one to handle the case where I want a NULL parent category (WHERE
[TCATEGORYPARENT ID] IS NULL)
- the other one to handle the case where I provide a parent category
(WHERE [TCATEGORYPARENT ID] = @TCATEGORYPAREN TID)

Don't SelectParameter s work for NULL parameter values as well?

It seems like a severe limitation to me.
"Tim Stahlhut" <st******@netze ro.comwrote in message
news:12******* ******@corp.sup ernews.com...

>>>I am not an ASP.NET person so can't help with that but your SQL code
appears to be wrong.
SELECT * FROM tablex WHERE NULL = NULL -- will always return zero rows.

Tim S

Try This

SelectComma nd = "
SELECT [TCATEGORYID], [[TCATEGORYNAME]
FROM [TCATEGORY]
WHERE (([TCATEGORYPARENT ID] = @TCATEGORYPAREN TID) OR
([TCATEGORYPARENT ID] IS NULL AND @TCATEGORYPAREN TID IS NULL))
ORDER BY [TCATEGORYPARENT ID], [TCATEGORYNAME]
"
"John" <no***@replytot group.pleasewro te in message
news:us****** ********@TK2MSF TNGP03.phx.gbl. ..
I just cannot manage to perform a SELECT query with NULL parameter...
My CATEGORY table does have one row where TCATEGORYPARENT ID is null
(real DB null value).

TCATEGORYI D and TCATEGORYPARENT ID are uniqueidentifie r columns.

My questions:

- is Type="Object", below, necessary?
- what shall I specify for DefaultValue in my function? I tried
everything . How come the DefaultValue must be a string? Why can't I
specify DBNull.Value?
- How do I make this work....

Thanks for your help.

-J

Code follows:

<asp:SqlData Source ID="SqlDataSour ce1" runat="server"
ConnectionSt ring="<%$ ConnectionStrin gs:MyBaseConnec tionString %>"
SelectComman d="SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY]
WHERE ([TCATEGORYPARENT ID] = @TCATEGORYPAREN TID) ORDER BY
[TCATEGORYPARENT ID], [TCATEGORYNAME]">
<SelectParamete rs>
<asp:Paramete r Name="TCATEGORY PARENTID" Type="Object" />
</SelectParameter s>
</asp:SqlDataSour ce>

private void QueryCategory()
{
SqlDataSource1. SelectParameter s["TCATEGORYPAREN TID"].DefaultValue =
System.Data. SqlTypes.SqlGui d.Null.ToString ();

DataView dv =
(DataView)Sq lDataSource1.Se lect(DataSource SelectArguments .Empty);

if (dv != null) // I always end up with either dv null or
dv.Table.Row s.Count = 0 :-(
{
string prefix = new String(' ', depth);
foreach (DataRow row in dv.Table.Rows)
{
[...]
}
}
}

[Russell]

SELECT * FROM TCATEGORY WHERE
(TCATEGORYPAREN TID = @TCATEGORYPAREN TID
OR (TCATEGORYPAREN TID IS NULL AND @TCATEGORYPAREN TID IS NULL))

will fetch records where the IDs match or both the ID and input is
null.

John wrote:

I know there is a standard to respect, but we are talking about the
authoring layer here (ADO.NET). I had thought the authoring layer would
handle this common case.

I have tried using a SqlCommand instead and obtain the same result: a
parameter with a value of NULL doesn't seem to be supported:

using (SqlConnection conn = new
SqlConnection(S qlDataSource1.C onnectionString ))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SEL ECT * FROM TCATEGORY WHERE
TCATEGORYPARENT ID = @TCATEGORYPAREN TID");
cmd.Connection = conn;
SqlParameter p = new SqlParameter("@ TCATEGORYPARENT ID",
SqlDbType.Uniqu eIdentifier);
p.Value = DBNull.Value;
cmd.Parameters. Add(p);

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter( cmd);
da.Fill(ds);
int c = ds.Tables[0].Rows.Count; // I get 0 here, that's wrong, I've got
one row with TCATEGORYPARENT ID being NULL
}

"Tim Stahlhut" <st******@netze ro.comwrote in message
news:12******** *****@corp.supe rnews.com...

It is the SQL standard in all databases that claim to be close to SQL 92
let alone SQL99.

Tim S

"John" <no***@replytot group.pleasewro te in message
news:e0******** ******@TK2MSFTN GP04.phx.gbl...

Thanks Tim, but your solution is less than ideal, because I would have to
have two different SqlDataSources:

- one to handle the case where I want a NULL parent category (WHERE
[TCATEGORYPARENT ID] IS NULL)
- the other one to handle the case where I provide a parent category
(WHERE [TCATEGORYPARENT ID] = @TCATEGORYPAREN TID)

Don't SelectParameter s work for NULL parameter values as well?

It seems like a severe limitation to me.
"Tim Stahlhut" <st******@netze ro.comwrote in message
news:12******** *****@corp.supe rnews.com...
I am not an ASP.NET person so can't help with that but your SQL code
appears to be wrong.
SELECT * FROM tablex WHERE NULL = NULL -- will always return zero rows.

Tim S

Try This

SelectComman d = "
SELECT [TCATEGORYID], [[TCATEGORYNAME]
FROM [TCATEGORY]
WHERE (([TCATEGORYPARENT ID] = @TCATEGORYPAREN TID) OR
([TCATEGORYPARENT ID] IS NULL AND @TCATEGORYPAREN TID IS NULL))
ORDER BY [TCATEGORYPARENT ID], [TCATEGORYNAME]
"
"John" <no***@replytot group.pleasewro te in message
news:us******* *******@TK2MSFT NGP03.phx.gbl.. .
I just cannot manage to perform a SELECT query with NULL parameter...
My CATEGORY table does have one row where TCATEGORYPARENT ID is null
(real DB null value).

TCATEGORYID and TCATEGORYPARENT ID are uniqueidentifie r columns.

My questions:

- is Type="Object", below, necessary?
- what shall I specify for DefaultValue in my function? I tried
everything. How come the DefaultValue must be a string? Why can't I
specify DBNull.Value?
- How do I make this work....

Thanks for your help.

-J

Code follows:

<asp:SqlDataS ource ID="SqlDataSour ce1" runat="server"
ConnectionStr ing="<%$ ConnectionStrin gs:MyBaseConnec tionString %>"
SelectCommand ="SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY]
WHERE ([TCATEGORYPARENT ID] = @TCATEGORYPAREN TID) ORDER BY
[TCATEGORYPARENT ID], [TCATEGORYNAME]">
<SelectParamete rs>
<asp:Paramete r Name="TCATEGORY PARENTID" Type="Object" />
</SelectParameter s>
</asp:SqlDataSour ce>

private void QueryCategory()
{
SqlDataSource1. SelectParameter s["TCATEGORYPAREN TID"].DefaultValue =
System.Data.S qlTypes.SqlGuid .Null.ToString( );

DataView dv =
(DataView)Sql DataSource1.Sel ect(DataSourceS electArguments. Empty);

if (dv != null) // I always end up with either dv null or
dv.Table.Rows .Count = 0 :-(
{
string prefix = new String(' ', depth);
foreach (DataRow row in dv.Table.Rows)
{
[...]
}
}
}