CODEWITHSUNDEEP
c#asp.netsql
Ahmad Z. Tibi
Ahmad Z. Tibi

Reputation: 429

Let ASP.NET SelectParameters accept null value

I have one page to view all Universities,and I want this page to view filtered data as per city, I added to SQL statement: 

WHERE (Cit_Id = @Unv_CitId OR @Unv_CitId IS NULL)

and set DefaultValue of @Unv_CitId parameter to NULL, but I got error,

How can I solve this issue?

Upvotes: 1

Views: 344

Answers (5)

RVD
RVD

Reputation: 66

delclare @Unv_CitId as null while declaring parameter in procedure as shown below

@Unv_CitId int NULL

so the varibale will accept null value as well.

Upvotes: 0

Ahmad Z. Tibi
Ahmad Z. Tibi

Reputation: 429

Yes now it is ok, I set SqlDataSource's CancelSelectOnNullParameter property to False, and delete the defaultValue of the parameter

Upvotes: 0

nick_w
nick_w

Reputation: 14938

I'm not 100% sure of your intention, but what about something like this:

WHERE (Cit_Id = @Unv_CitId OR Cit_Id IS NULL)

Your original query looks a little odd with @Unv_CitId IS NULL in there: this clause isn't checking against a field, merely against the value being passed in.

Upvotes: 0

SRIRAM
SRIRAM

Reputation: 1888

you can try this

where (Cit_Id = isdbnull(@Unv_CitId)?null:@unv_citId

Upvotes: 0

Asif Mushtaq
Asif Mushtaq

Reputation: 13150

You should assign value DBNull.Value rather then null.

Upvotes: 3

Related Questions