473,547 Members | 2,476 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SELECT .. WHERE column IS NULL?

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)
{
[...]
}
}
}
Aug 13 '06 #1
5 5494
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)
{
[...]
}
}
}


Aug 13 '06 #2
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)
{
[...]
}
}
}



Aug 13 '06 #3
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)
{
[...]
}
}
}




Aug 13 '06 #4
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)
{
[...]
}
}
}




Aug 13 '06 #5
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)
{
[...]
}
}
}

Aug 14 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2405
by: skidvd | last post by:
Hello: I have just recently converted to using the InnoDB table type so that I can enforce FOREIGN key constraints. I have been using MyISAM tables (accessed via JDBC) successfully for some time. However, I have just come across a problem with the new configuration that boggles my mind.... First some configuration data:
0
1277
by: Rob Knowles | last post by:
I have created a script that runs once a week and copies data from one table (phpbb_users is the actual table name) in a database called users I have a table called users. There are two tables in this database. The table names are: phpbb_users phpbb_users_backup
3
3533
by: Ian T | last post by:
Hi, I've got what I think (probably incorrectly) should be a simple SELECT : Two colums with data like col1 col2 1 50 1 51 2 50
4
2830
by: jimh | last post by:
I'm not a SQL expert. I want to be able to write a stored procedure that will return 'people who bought this product also bought this...'. I have a user table that links to a transaction table that links to a transaction items table that links to the products table: (User Table) UserID Other user data
3
5713
by: dumbledad | last post by:
Hi All, I'm confused by how to replace a SELECT statement in a SQL statement with a specific value. The table I'm working on is a list of words (a column called "word") with an index int pointing to the sentence they come from (a column called "regret"). I also have a table of stop words (called "GenericStopWords") that contains the words I...
10
5591
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I create a single View and specify also in this View the WHERE clause that is common in these stored procedures, I will have the new stored procecures...
6
4829
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID SalesManName AT Alan Time
4
6834
by: Lucky | last post by:
hi guys! Currently i'm facing a bit odd situation. i cant modify my code so i have to make changes in SQL Server. the problem is, i've modified one table by adding new column and now i want only those row which has not null value in that column whenever the select statement gets fire. i mean in every select statement only those rows should...
4
1650
by: tshad | last post by:
How do I tell DataAdapter that Column 2 and 3 are string and not integer? Following is the example data that comes from the .csv file FEDERAL TAX,1084,0000 COREHCR,1084,0000 CLIENT P,1084,0000 The select is: Dim da2 As New OleDb.OleDbDataAdapter("Select * from " &
4
10653
tjc0ol
by: tjc0ol | last post by:
Hi guys, I'm a newbie in php and I got error in my index.php which is: 1054 - Unknown column 'p.products_id' in 'on clause' select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status,...
0
7510
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7437
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7947
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7463
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7797
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5362
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5081
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3493
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1050
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.