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.

detach problem

I'm using following connection string in my application in (C# 2.0 windows )

Data Source=.\SQLEXPRESS; Trusted_Connection=yes;AttachDbFilename=|DataDirectory|\myDB.mdf"

My application attaches the file successfully when the first connection is established, but after closing my application the database is remained in the sql server express and it does not detached automatically. According to this article the database shall be detached and I need it in my application because of the security.

http://technet.microsoft.com/en-us/library/ms165716.aspx

Please let me know what shall I do ?

Thanks in advance,

Saeideh

You should always close the conenction to the database as far as you don′t use it anymore.Othwerwise the database will be closed (and detached after a certain period of time). If you have enabled connection pooling it "can take a while" after the pool will be released and the connection will be closed.

http://msdn2.microsoft.com/en-us/library/bb264564.aspx

User instance databases have the Auto Close option set so that if there are no connections to a database for 8-10 minutes, the database shuts down and the file is closed. This happens automatically, but it can take a while, especially if connection pooling is enabled for your connections.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Design Question - Is Integration Services the way to go ?

we need to transfer data from a OLTP SQL 2005 database to the backend OLAP Datamart and Datawarehouse databases. This data is a long string of XML (from Infopath), out of which we need to extract the data values and populate the Datamart and datawarehouse. Ideally this will be an automated process - and will also have the flexibility to cater for changes in the xml structur.

we would also like the Analysis services cubes to be "updated" in real-time as the transfer occurs.

is Integration Services the way to go ?

You have a lot of choices here. I would recommend starting out by investigating the XML methods you have in SQL Server in general. Paste this link in Books Online in the "URL" bar:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/63cbe6c9-79d0-4cf3-b451-36dbe4668bf6.htm

Without knowing more, I think SSIS is the way to go for you, but that depends a great deal on whether you are "trickling" the data or loading it in one pass periodically. SSIS gives you a lot of flexibility and functionality, but there is a learning curve with it. That being said, I think it's worth the effort. Spending a little more time getting a package together and documenting it well is a great way to implement your solution in SSIS.

Buck Woody

http://www.buckwoody.com

|||

Buck

thx for this

the destination datamart stores the latest version of the case (i.e. the latest record), the destination datawarehouse stores all of the versions of the case (i.e. all records). Ideally we would be updating both of these records at the same time in real-time "trickling" mode - but if necessary, we could bulk the warehouse updates to (for example) an hourly process.

also need to consider the analysis services part - we are using analysis services cubes for our reporting - and would need these to also be updated in real-time (or bulk upload if required on the datawarehouse database) - is this easily done within SSIS ?

also - alternatives... have considered a couple of alternatives

might SQLCLR code done as a trigger on the required table be an alternative solution ? - how would this perform in comparison to the possible SSIS solution ?

amending the web service that does the update of the OLTP table to also do the update of the datamart / datawharehouse ? (not so keen on this one as we are trying to separate these out ?)

thx

mark

|||

The performance questions you have would have to be tested - there are just too many variables to say one way or another.

The bigger question is how your Analysis system will be used. If you need "real-time" information, then trickling is the way to go, and I'd recommend using an HTTP endpoint or one of the other XML methods native to SQL Server, or perhaps the CLR route you've mentioned if the business logic should be coded in.

If your analysis is truly strategic (based on this data, should we make hubcaps or aircraft parts), then SSIS is my recommendation. Strategic decisions aren't made multiple times a day, so a bulk load process is better tolerated. In either case you will transform "in-flight", meaning you don't have to stage the data, so you save a lot of resources and time.

And in answer to your question about cubes, yes, SSIS is suited for that. In fact, it has many steps and processes built right in specific to AS.