Solved

ASP SQL query Help

Posted on 2014-04-21
3
421 Views
Last Modified: 2014-04-21
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.
0
Comment
Question by:Thomas Grassi
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 40013861
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&"%')"

Open in new window

0
 
LVL 23

Author Closing Comment

by:Thomas Grassi
ID: 40013870
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
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40013877
Remember to clean your data before sending it to your db
oops
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question