Solved

ASP SQL query Help

Posted on 2014-04-21
3
422 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 53

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 53

Expert Comment

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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
auto play video on web page 4 58
Show MP4 file and close (HTA) 3 42
Windows 2003 Web server - authentication required message 3 47
WordPress Page Content Lost 2 49
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
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 get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

732 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