Solved

ASP SQL query Help

Posted on 2014-04-21
3
415 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
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…
The viewer will learn how to dynamically set the form action using jQuery.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now