Help me write a Search function please

Hi all,

I'm very new to ASP.NET stuffs, I'm trying to write a Search function for my website... I have two text boxes, one if called "SongTitle" and the other is "Artist"... Now I need to populate the GridView to display the result, based on the input of the textbox... So if only the "SongTitle" have input, it will search for the Song Titles on the database... if the Artist is searched, then it will return the artist... If both text boxes have value in them, then it need to check for both fields in the database and return the correct item...

For the "Artist", I have 2 columns in the Database (originalArtist and performer), so for the Artist select statement, it need to check both columns on the table, if any of them match then it will return the item.

Any help would be greatly appreciated,

Thank you all,

Kenny.

Try something like this:

SELECTFROM YourTableWHERE (@.SongTitleISNULL OR SongTitle = @.SongTitle)AND (@.ArtistISNULL OR (originalArtist = @.ArtistOR Performer = @.Artist ))

|||

ndinakar,

Thank you very much, here is what I put for my SqlDataSource, but it doesn't appear to work... Nothing returned when I try to search, using just the artist, the song, or both for the input:

<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 IS NULL OR [MUSIC_TITLE2] LIKE '%' + @.MUSIC_TITLE + '%') AND (@.MUSIC_ARTIST IS NULL 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>

|||

If you run thre query in your query analyzer with some values for the parameters does it work as expected? If not, post some sample data from your table and the query you used so we can better understand why its not working.

|||

Here is what I put on the Query window of SQL Manager Studio Express:

DECLARE @.MUSIC_TITLE NVARCHAR(100)
DECLARE @.MUSIC_ARTIST NVARCHAR(100)

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

SET @.MUSIC_TITLE = 'Everytime'
SET @.MUSIC_ARTIST = 'Cascada'

I've tried to use just @.MUSIC_TITLE, or just @.MUSIC_ARTIST, and both... each time it return ALL the records in the table... But if I execute the same select statement in VS 2005, it returns nothing... In the table, there is a song called "Everytime We Touch" performed by Cascada.

|||

Are you setting the values after running the SELECT? post some sample data from your table so I can test it on my machine..

|||

sorry. you need to use OR instead of AND in your WHERE clause.

|||

As you can see I have the SET @.MUSIC_TITLE = 'Everytime' in the previous post... Here are some sample data:

MUSIC_TITLE MUSIC_ORIGINAL_SINGER MUSIC_PERFORMER

Everytime We Touch Cascada Cascada

Dancing Queen ABBA Purity

Heaven Bryan Adam

Hotel California Eagles Eagles

Thank you very much for your help,

Kenny.

|||

Changed the "AND" to "OR" have helped, but I have some problem:

1. If I don't enter anything for the "Artist" or in the "Title" box (meaning only of the box have input), no records returned even if I type part of the name of the song/artist, or even the full name of the song/artist.

2. For example if I have two songs that have some similiar words, i.e. "Hotel", in the Title box I typed "Hotel" and in the Artist box, I typed "Eagles", which should return 1 record only that contain both Hotel & Eagles in the record... But instead it returns all the songs with "Hotel" in it.

Thanks again,

Kenny.

|||

I think we had it right the first time itself. AND should work.

|||

Dinakar,

You are right, the "AND" should work... The problem is that it only works if both the Title and Artist text box have values in them... Leaving one or the other blank does not return any results even if they should be... Any help?

Thank you,

Kenny.

|||

HEre's a sample I set up. I was able to get both records when I used "hotel" for music_title and left the second field blank.

Declare @.ttable (col1int identity, MUSIC_TITLEvarchar(50), MUSIC_ORIGINAL_SINGERvarchar(50), MUSIC_PERFORMERvarchar(50))insert into @.tvalues ('Everytime We Touch','Cascada','Cascada')insert into @.tvalues ('Dancing Queen','ABBA','Purity')insert into @.tvalues ('hotel','Bryan Adam',null)insert into @.tvalues ('Hotel California','Eagles','Cascada')DECLARE @.MUSIC_TITLENVARCHAR(100)DECLARE @.MUSIC_ARTISTNVARCHAR(100)SET @.MUSIC_TITLE ='hotel'SET @.MUSIC_ARTIST =nullSELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]FROM @.t--where ([MUSIC_ORIGINAL_SINGER] LIKE '%' + @.MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @.MUSIC_ARTIST + '%')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 +'%'))

|||

I tried the code above in SQL Manager Studio and they seems to work as expected... but somehow it doesn't work with my SqlDataSource! I don't know what else I have to do...

Thank you very, very much for your help Dinakar.

Kenny.

|||

I think that it doesn't work with SqlDataSource because the empty string (when nothing was entered in either one of the textbox) doesn't mean null in ADO.NET... Can anyone help me convert this to a code-behind, or help me convert that empty string to null? I would love to put this in code-behind file but like I said in my first post, I'm very new to ASP.NET.

Thanks,

Kenny.

|||

Unfortunately I dont do any .NET code so I cant help you there. Perhaps you can create a new post and someone might help you there. Posts with 0 replies have a better chance of being "looked at" than the ones with 12 replies.