Let ASP.NET SelectParameters accept null value

Question

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?

What is the error ? please share full query instead of just where clause. — yogi
– yogi, Commented Oct 23, 2012 at 5:10

Asif Mushtaq · Accepted Answer · 2012-10-23 05:25:52Z

3

You should assign value DBNull.Value rather then null.

edited Oct 23, 2012 at 5:25

answered Oct 23, 2012 at 5:11

Asif Mushtaq

13.1k3 gold badges36 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ahmad Z. Tibi Over a year ago

Thanks, however I got this error "Conversion failed when converting the nvarchar value 'DB.Null' to data type int."

Asif Mushtaq Over a year ago

I think you don't need 'OR @Unv_CitId IS NULL' part of your query.

Asif Mushtaq Over a year ago

and change it to the DBNull.Value rather then DBNull.

SRIRAM · Accepted Answer · 2012-10-23 05:27:23Z

0

you can try this

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

answered Oct 23, 2012 at 5:27

SRIRAM

1,8982 gold badges17 silver badges18 bronze badges

Comments

nick_w · Accepted Answer · 2012-10-23 05:29:38Z

0

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.

answered Oct 23, 2012 at 5:29

nick_w

15k3 gold badges55 silver badges72 bronze badges

Comments

Ahmad Z. Tibi · Accepted Answer · 2012-10-23 06:09:12Z

0

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

answered Oct 23, 2012 at 6:09

Ahmad Z. Tibi

4292 gold badges8 silver badges19 bronze badges

Comments

Ramesh · Accepted Answer · 2012-10-23 07:37:07Z

0

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.

edited Oct 23, 2012 at 7:37

Ramesh

13.3k4 gold badges55 silver badges90 bronze badges

answered Oct 23, 2012 at 7:18

RVD

661 bronze badge

Collectives™ on Stack Overflow

Let ASP.NET SelectParameters accept null value

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related