Detect and convert an empty string (from textbox) to null?

Hi all,

I have this code that I use for my Search function:

SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]
FROM t_music
WHERE (@.MUSIC_TITLEISNULL OR [MUSIC_TITLE]LIKE'%' + @.MUSIC_TITLE +'%')
AND (@.MUSIC_ARTISTISNULL OR ([MUSIC_ORIGINAL_SINGER]LIKE'%' + @.MUSIC_ARTIST +'%'OR [MUSIC_PERFORMER]LIKE'%' + @.MUSIC_ARTIST +'%'))

But right now if I don't enter anything in one of the textbox (2 have two, either of them can be left empty), the above Sql statement doesn't return anything since ADO.NET can't tell an empty textbox and treat it at null... So anyone please help me how to detect an empty textbox and set that to null for the above SQL statement to work. (It work in SQL Manager Studio, when I set one of the parameter = null.)

I'm very new to ASP.NET stuffs, so if someone can help me to convert that function to code-behind and help me to call it from the .aspx, that would be even better as I don't want to put the code in my .aspx page... But I'm not quite there yet.

Thank you all,

Kenny.

hi,

You could set the text field to NULL if it's empty:

//check if theMUSIC_TITLE field is empty

if (MUSIC_TITLE.Text.Trim() =="")

{

MUSIC_TITLE.Text = NULL;

}

|||

Hi,

Rewrite your query in this way

SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]
FROM t_music
WHERE (@.MUSIC_TITLE=''OR [MUSIC_TITLE]LIKE'%' + @.MUSIC_TITLE +'%')
AND (@.MUSIC_ARTIST = ''OR ([MUSIC_ORIGINAL_SINGER]LIKE'%' + @.MUSIC_ARTIST +'%'OR [MUSIC_PERFORMER]LIKE'%' + @.MUSIC_ARTIST +'%'))

Hope this will work fine.

|||

Hi all,

I've tried both, try to set TextboxMusic.Text = null and tried to change the SQL statement but both method didn't work.

Any idea?

Thank you all,

Kenny.

|||

I think there is a DBNull.Value that you can set the textbox value to if its empty?

|||

The best solution is really to make all your columns non-nullable and you won't have this problem at all. In fact, it will avoid many problems that will have you pulling your hair out because of the unintuitive way that NULLs work. For example:

WHERE COLUMN1 <> "ABC"

will not return the row where COLUMN1 is null. The reason is that null means unknown, and unknown means that it could be anything, even ABC. This is by design and is a common feature of all relational databases that I am aware of.

So, I'd recreate your tables with non null columns. You can do this by creating an identical table and doing a SELECT INTO. You should also be able to rename your first table with sp_rename and create the new one under the old name, but I believe (not certain) this will require you to manually recompile all procs that use the table else it will continue to run against the original.

|||

The column in the database is not null... What null is the querystring that I pass from a previous page to the search page via textbox. But ADO.NET does not interpretet an empty textbox ("") as null and thus my WHERE statement doesn't work properly.

Thanks,

Kenny.

|||

How are you adding parameters to the database command object? You might need to do something like this:

object paramVal =null;if(text !=string.Empty) paramVal = text;cmd.Parameters.AddWithValue("@.myParam", paramVal);

|||

I think people misread my question or the answers are something that I haven't fully understand yet... Here is my situation... I have a page with 2 textbox, named TextBoxTitle and TextBoxArtist... Here visitor can enter the name of the song (title) or the artist and click search... They can search by title, artist, or by both... And then I will pass that as a querystring as mydomain.com/search.aspx?title=[ValueFromTextBoxTitle]&artist=[ValueFromTextBoxArtist]

On the search page, I have a SqlDataSource and a GridView to display the results... Here is how I setup my SqlDataSource:

<asp:SqlDataSource ID="DSResults" runat="server" ConnectionString="<%$ ConnectionStrings:notesnhacConnectionString1 %>"
SelectCommand="SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER] FROM t_music WHERE (@.MUSIC_TITLE = '' OR [MUSIC_TITLE] LIKE '%' + @.MUSIC_TITLE + '%') AND (@.MUSIC_ARTIST = '' OR ([MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%'))">
<SelectParameters>
<asp:QueryStringParameter Name="MUSIC_TITLE" QueryStringField="title" Type="String" />
<asp:QueryStringParameter Name="MUSIC_ARTIST" QueryStringField="artist" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

The problem is that, when I try this Select statement in SQL Manager Studio, I was able to search for just the song title, the artist, or both... But on my web page, I can't search for just title or artist, I HAVE to enter both the title and the artist field for the search to return my records... If only one of the field are entered, nothing returned, even though they should.

Thank you all,

Kenny.

|||

so if the textbox is empty exclude the querystring parameter. Instead of doing title=&artist=something, do artist=something.

then the value sent to the database will be null instead of string.empty

|||

I've tried to exclude one of the querystring if the textbox is empty but it didn't work either :(

Thanks,

Kenny.

|||

Add this attribute to the <asp:QueryStringParameter />

ConvertEmptyStringToNull="true"

|||

Thank you all, but I think I'll give up!!! I've tried all the different ways but none seems to be work. I guess I need to find someway to do a code-behind instead of using that SqlDataSource.

Thanks again,

Kenny.

|||

Hi,

>> " I HAVE to enter both the title and the artist field for the search to return my records... If only one of the field are entered, nothing returned, even though they should."

For this situaltion, in my view, I would rather use dynamic sql statement instead of inserting null into the sql condition. See the following sample:

string condition1="";string condition2="";string sql1 ="SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER] FROM t_music WHERE 1=1";if(textboxTitle.Text!=""){ condition1=" and MUSIC_TITLE like '%"+ textboxTitle.Text +"%'" ;}else if(textboxArtist.Text!=""){ condition2=" and MUSIC_ARTIST like '%"+ textboxArtist.Text +"%'" ;}sql1+=condition1;sql1+=condition2;

In this way, you can search for just title or artist.
Hope this helps.

Thanks.

|||

This code is susceptible to sql-injection, and I would absolutely not use this code. Change it to use a parameterized query.