Link to home
Start Free TrialLog in
Avatar of Member_2_6492660_1
Member_2_6492660_1Flag for United States of America

asked on

ASP SQL query Help

My strSQL1 code below had = for
(dbo.Artists.artistname = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

changed to LIKE

(dbo.Artists.artistname Like '"& searchWord&"') OR (dbo.Recordings.RecordingTitle Like '"& searchWord&"') OR (dbo.Tracks.TrackTitle Like '"& searchWord&"')"

Now I can do searches with having the exact name

I use to have to type The Beatles in the search box

Now I can type %Beatles% in the search box


I would like the users to not have to type %Beatles%
Can I place this on the query code line in my ASP code below?

I tried adding it to the code but it failed every time with syntax error.

On my SQL server I wrote a query that works great but I guess the ASP syntax is slightly different.

<%
dim searchWord
searchWord = Request.Querystring("song") 
Set oConn=Server.CreateObject("ADODB.Connection") 

oConn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxxx;Password=xxxxxxx;Initial Catalog=Music;Network Library=DBMSSOCN;Data Source=xxx.xxx.xxx.xxx,1433"

strSQL1 = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle, dbo.Tracks.TrackFileName FROM  dbo.Artists INNER JOIN dbo.Recordings ON dbo.Artists.artistid = dbo.Recordings.ArtistID INNER JOIN dbo.Tracks ON dbo.Recordings.RecordingID = dbo.Tracks.RecordingID WHERE     (dbo.Artists.artistname Like '"& searchWord&"') OR (dbo.Recordings.RecordingTitle Like '"& searchWord&"') OR (dbo.Tracks.TrackTitle Like '"& searchWord&"')"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

if not oRs1.eof then
    do until oRs1.eof
       response.write "<div class=""results""><input type=""checkbox"" name=""selectItem"" value="""&oRs1("TrackFileName")&"""> "&oRs1("artistname")&" "&oRs1("RecordingTitle")&" "&oRs1("TrackTitle")&" "&oRs1("TrackFileName")&"</div>"
    oRs1.movenext
    loop
end if
%>

Open in new window


Windows 2003 Server IIS 6.0
SQL 2008 Server on a Windows 2008 Server both 64 bit.
ASKER CERTIFIED SOLUTION
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Member_2_6492660_1

ASKER

Scott
Great Job
Thanks for the fast response
Worked great
Now you can just type any thing in the search box and it will search
Remember to clean your data before sending it to your db
User generated image