Handling blank string ("") text box values when doing select to a database with null values in C#

Question

I'm using a DataGridView to display data from an MSSQL database. I have several text input fields that when an onkeyup event is registered, it performs a lookup query (using a table adapter) with the text field as a parameter to the SQL string.

My database is currently setup to have some nullable fields.

When I have a zero-length string value in the text input field, the value is NOT null, therefore the SQL Select does not match (LIKE clause) the text box value to the NULL value in the database.

What is the best way to handle this situation? Convert all the empty fields in the database to NULL?

I could convert any blank input strings to null when querying the database, but this would then mean that any fields in the database that are empty strings and not actually null would be excluded.

I think I might be missing something obvious, I cannot find any similar problems online, but if anyone can point me in the right direction that would be great!

Thanks all, leaving work now, but will investigate further tomorrow. — Davos555
– Davos555, Commented May 30, 2012 at 16:28

Tim Schmelter · Accepted Answer · 2012-05-30 16:28:09Z

3

I am also filtering by other parameters too. So for example, I have 2 parameters being filled in from the text boxes ...

If you want to handle multiple search parameters:

...
WHERE ( @field1 IS NULL OR COALESCE(field1,'') like '%' + @field1 + '%' )
AND   ( @field2 IS NULL OR COALESCE(field2,'') like '%' + @field2 + '%' )
AND   ( @field3 IS NULL OR COALESCE(field3,'') like '%' + @field3 + '%' )

answered May 30, 2012 at 16:28

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

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

3 Comments

Davos555 Over a year ago

Hi Tim, thanks please see my comment on returning unwanted rows because of the OR NULL below..

Tim Schmelter Over a year ago

@Davos555: Have you seen the COALESCE which treats nulls as empty strings?

Davos555 Over a year ago

Thanks, will check this out tomorrow and choose the answer!

juergen d · Accepted Answer · 2012-05-30 16:30:39Z

3

If you want to select data that is null but holds an empty string instead then you could just change your select query like this:

select * from your_table where some_column is null or len(some_column) = 0

Not exactly sure what you are looking for but how about

SELECT * FROM Table 
WHERE (Param1 is null or Column1 LIKE '%' + Param1 + '%')
and (Param2 is null or Column2 LIKE '%' + Param2 + '%')

edited May 30, 2012 at 16:30

answered May 30, 2012 at 16:20

juergen d

205k40 gold badges305 silver badges377 bronze badges

3 Comments

Tim Schmelter Over a year ago

... or LEN(some_column)=0 instead of some_column = ''might be better.

Davos555 Over a year ago

Thanks for your answer. I am also filtering by other parameters too. So for example, I have 2 parameters being filled in from the text boxes. So one may contain a value "test" the other text box may be "test2" if the query was changed this way then any rows with the first column containing "test" and second column "NULL" would also be returned.

Davos555 Over a year ago

SQL: SELECT * FROM Table WHERE (Column1 LIKE '%' + Param1 + '%') AND ( Column2 LIKE '%' + Param2 + '%')

hkutluay · Accepted Answer · 2012-05-30 16:22:21Z

1

Try to use like this. If your field value is null, simply converts it to empty string and looks for your param.

select * from table
ISNULL(field,'') like @yourparam

answered May 30, 2012 at 16:22

hkutluay

6,9742 gold badges37 silver badges57 bronze badges

Comments

score 1 · Accepted Answer · 2012-05-30 18:09:16Z

1

I am assuming that you are using ASP.NET

Firstly, I think, there is an Issue in the Design. You are going Over and Over again to the Database on each KeyPress event of TextBox. If you can avoid it by keeping the data in ViewState/Session?

Back to the original query...

Declare @Param Varchar(100)
Set @Param = null

if(@Param is null or @Param = '')
Begin
     select * from your_table
End
Else
Begin
     select * from your_table where some_column like '%' + @Param + '%'
End

edited May 30, 2012 at 18:09

answered May 30, 2012 at 17:13

user1425057

2 Comments

Davos555 Over a year ago

Cheers for the heads up - can you recommend some reading on keeping the data in the session? I want to allow the user to search every keystroke - ie autocomplete, is there a way to cache.

user1425057 Over a year ago

You can do cache for complete page, partial page or for some small string value. You can read session here...

bummi · Accepted Answer · 2013-10-26 20:11:19Z

0

SELECT * FROM Table 
Where Column1 LIKE '%' + ISNULL(Param1,Column1) + '%'

Above will check whether the Parameter is NULL. If it is NULL then just compare it with the same column name. This is the most easiest and shortest method.

edited Oct 26, 2013 at 20:11

bummi

27.4k14 gold badges67 silver badges105 bronze badges

answered Oct 26, 2013 at 11:07

Kinjan Shah

766 bronze badges

Collectives™ on Stack Overflow

Handling blank string ("") text box values when doing select to a database with null values in C#

5 Answers 5

3 Comments

3 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

3 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related