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 TCATEGORYPARENTID is null (real DB
null value).

TCATEGORYID and TCATEGORYPARENTID are uniqueidentifier 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:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:MyBaseConnectionString %>"
SelectCommand="SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY] WHERE
([TCATEGORYPARENTID] = @TCATEGORYPARENTID) ORDER BY [TCATEGORYPARENTID],
[TCATEGORYNAME]">
<SelectParameters>
<asparameter Name="TCATEGORYPARENTID" Type="Object" />
</SelectParameters>
</asp:SqlDataSource>

private void QueryCategory()
{
SqlDataSource1.SelectParameters["TCATEGORYPARENTID"].DefaultValue =
System.Data.SqlTypes.SqlGuid.Null.ToString();

DataView dv =
(DataView)SqlDataSource1.Select(DataSourceSelectArguments.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)
{
[...]
}
}
}

 

[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 (([TCATEGORYPARENTID] = @TCATEGORYPARENTID) OR ([TCATEGORYPARENTID]
IS NULL AND @TCATEGORYPARENTID IS NULL))
ORDER BY [TCATEGORYPARENTID], [TCATEGORYNAME]
"

 

[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
[TCATEGORYPARENTID] IS NULL)
- the other one to handle the case where I provide a parent category (WHERE
[TCATEGORYPARENTID] = @TCATEGORYPARENTID)

Don't SelectParameters work for NULL parameter values as well?

It seems like a severe limitation to me.

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 (([TCATEGORYPARENTID] = @TCATEGORYPARENTID) OR ([TCATEGORYPARENTID]
IS NULL AND @TCATEGORYPARENTID IS NULL))
ORDER BY [TCATEGORYPARENTID], [TCATEGORYNAME]
"

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

TCATEGORYID and TCATEGORYPARENTID are uniqueidentifier 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:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyBaseConnectionString %>"
SelectCommand="SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY]
WHERE ([TCATEGORYPARENTID] = @TCATEGORYPARENTID) ORDER BY
[TCATEGORYPARENTID], [TCATEGORYNAME]">
<SelectParameters>
<asparameter Name="TCATEGORYPARENTID" Type="Object" />
</SelectParameters>
</asp:SqlDataSource>

private void QueryCategory()
{
SqlDataSource1.SelectParameters["TCATEGORYPARENTID"].DefaultValue =
System.Data.SqlTypes.SqlGuid.Null.ToString();

DataView dv =
(DataView)SqlDataSource1.Select(DataSourceSelectArguments.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)
{
[...]
}
}
}

 

[Tim Stahlhut]

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

Tim S

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
[TCATEGORYPARENTID] IS NULL)
- the other one to handle the case where I provide a parent category
(WHERE [TCATEGORYPARENTID] = @TCATEGORYPARENTID)

Don't SelectParameters work for NULL parameter values as well?

It seems like a severe limitation to me.

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 (([TCATEGORYPARENTID] = @TCATEGORYPARENTID) OR
([TCATEGORYPARENTID] IS NULL AND @TCATEGORYPARENTID IS NULL))
ORDER BY [TCATEGORYPARENTID], [TCATEGORYNAME]
"

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

TCATEGORYID and TCATEGORYPARENTID are uniqueidentifier 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:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyBaseConnectionString %>"
SelectCommand="SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY]
WHERE ([TCATEGORYPARENTID] = @TCATEGORYPARENTID) ORDER BY
[TCATEGORYPARENTID], [TCATEGORYNAME]">
<SelectParameters>
<asparameter Name="TCATEGORYPARENTID" Type="Object" />
</SelectParameters>
</asp:SqlDataSource>

private void QueryCategory()
{
SqlDataSource1.SelectParameters["TCATEGORYPARENTID"].DefaultValue =
System.Data.SqlTypes.SqlGuid.Null.ToString();

DataView dv =
(DataView)SqlDataSource1.Select(DataSourceSelectArguments.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(SqlDataSource1.ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM TCATEGORY WHERE
TCATEGORYPARENTID = @TCATEGORYPARENTID");
cmd.Connection = conn;
SqlParameter p = new SqlParameter("@TCATEGORYPARENTID",
SqlDbType.UniqueIdentifier);
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 TCATEGORYPARENTID being NULL
}

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

Tim S

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
[TCATEGORYPARENTID] IS NULL)
- the other one to handle the case where I provide a parent category
(WHERE [TCATEGORYPARENTID] = @TCATEGORYPARENTID)

Don't SelectParameters work for NULL parameter values as well?

It seems like a severe limitation to me.

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 (([TCATEGORYPARENTID] = @TCATEGORYPARENTID) OR
([TCATEGORYPARENTID] IS NULL AND @TCATEGORYPARENTID IS NULL))
ORDER BY [TCATEGORYPARENTID], [TCATEGORYNAME]
"


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

TCATEGORYID and TCATEGORYPARENTID are uniqueidentifier 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:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyBaseConnectionString %>"
SelectCommand="SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY]
WHERE ([TCATEGORYPARENTID] = @TCATEGORYPARENTID) ORDER BY
[TCATEGORYPARENTID], [TCATEGORYNAME]">
<SelectParameters>
<asparameter Name="TCATEGORYPARENTID" Type="Object" />
</SelectParameters>
</asp:SqlDataSource>

private void QueryCategory()
{
SqlDataSource1.SelectParameters["TCATEGORYPARENTID"].DefaultValue =
System.Data.SqlTypes.SqlGuid.Null.ToString();

DataView dv =
(DataView)SqlDataSource1.Select(DataSourceSelectArguments.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)
{
[...]
}
}
}

 

[Russell]

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

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

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(SqlDataSource1.ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM TCATEGORY WHERE
TCATEGORYPARENTID = @TCATEGORYPARENTID");
cmd.Connection = conn;
SqlParameter p = new SqlParameter("@TCATEGORYPARENTID",
SqlDbType.UniqueIdentifier);
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 TCATEGORYPARENTID being NULL
}

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

Tim S

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
[TCATEGORYPARENTID] IS NULL)
- the other one to handle the case where I provide a parent category
(WHERE [TCATEGORYPARENTID] = @TCATEGORYPARENTID)

Don't SelectParameters work for NULL parameter values as well?

It seems like a severe limitation to me.


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 (([TCATEGORYPARENTID] = @TCATEGORYPARENTID) OR
([TCATEGORYPARENTID] IS NULL AND @TCATEGORYPARENTID IS NULL))
ORDER BY [TCATEGORYPARENTID], [TCATEGORYNAME]
"


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

TCATEGORYID and TCATEGORYPARENTID are uniqueidentifier 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:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyBaseConnectionString %>"
SelectCommand="SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY]
WHERE ([TCATEGORYPARENTID] = @TCATEGORYPARENTID) ORDER BY
[TCATEGORYPARENTID], [TCATEGORYNAME]">
<SelectParameters>
<asparameter Name="TCATEGORYPARENTID" Type="Object" />
</SelectParameters>
</asp:SqlDataSource>

private void QueryCategory()
{
SqlDataSource1.SelectParameters["TCATEGORYPARENTID"].DefaultValue =
System.Data.SqlTypes.SqlGuid.Null.ToString();

DataView dv =
(DataView)SqlDataSource1.Select(DataSourceSelectArguments.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)
{
[...]
}
}
}