ASP.NET 3.5 with ADO

Problem

MS Access Query LIKE doesn't return any records...

SELECT Song_List.SongListID, Song_List.Artist, Song_List.Title, Song_List.Volume, Song_List.Track, Song_List.Genre
FROM Song_List
WHERE (((Song_List.Artist) Like "*" & [@Artist] & "*"));

SELECT *
FROM Song_List
WHERE Song_List.Title Like "*" & [@SongName];

However, same code with this Access query does....

SELECT *
FROM Song_List
WHERE UCase(Song_List.Genre)=UCase([@Genre]);

Solution

The problem was not the LIKE clause, it was the * wildcard. ADO doesn't recognize this character and expects a %. As soon as I replaced all asterisks in my Access queries to percent signs, it worked! So, now my Access queries look like this:

SELECT Song_List.SongListID, Song_List.Artist, Song_List.Title, Song_List.Volume, Song_List.Track, Song_List.Genre
FROM Song_List
WHERE (((Song_List.Artist) Like "%"+[@Artist]+"%"));

The only downfall to this approach is that when you run this query in Access, it won't return any records; however, it does run with no errors. So, now I have to keep two versions of these LIKE queries in my Access database - one for ADO and one for Access.

Works great so far.

Thanks to these two discussions:

http://bytes.com/groups/ms-access/192414-trouble-access-like-query-asp

http://forums.devx.com/showthread.php?t=167887

Here's the ADO code I'm running:

OleDbConnection dbConn = new OleDbConnection(conString);

               using (OleDbConnection connection = dbConn)
               {
                   using (OleDbCommand command =
                       new OleDbCommand("SearchSongByArtist", connection))
                   {
                       command.CommandType = CommandType.StoredProcedure;
                       command.Parameters.Add(new OleDbParameter("@Artist", artist_name));
                       connection.Open();
                       command.ExecuteNonQuery();

       }

    }