troubleshooting Question

ASP Query Help Needed.

Avatar of Thomas Grassi
Thomas GrassiFlag for United States of America asked on
Microsoft SQL ServerJavaScriptASPjQuerySQL
27 Comments1 Solution427 ViewsLast Modified:
In SQL my query works fine query

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 IN ('Led Zeppelin') and (dbo.Recordings.recordingtitle IN ('Houses of the Holy')))


The searchIN   is the problem.

The current ASP code

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 IN ("&searchIN&") or (dbo.Recordings.recordingtitle IN ("&searchIN&")))"

I think we need another value ("&ArtnameIn&")   and ("&searchIN&")    can stay the same

Is there a way to extract out the artist name and the recording tile from what is passed by the checked item

When I change the OR to AND I get no results

I believe in Lines 3 24 I need the help in

Here is the code.
<!-- #include file="db_connection.inc" -->
<%
dim searchWord, searchIN
dim strFont1
dim strFont2
searchIN = ""

goodData=0

searchWord = Request.form("chkAlbum") 'change form to method post 

if searchWord <> "" then
arrSearchWord = split(searchWord,",") ' convert to array'
for each phrase in arrSearchWord
    searchIN=searchIN & "'" & Trim( phrase ) & "'," 
next
searchIN=left(searchIN,len(searchIN)-1) ' remove the last comma'

strFont1 = "<font style=""font-size: 20px; color: #ff0000; font-family:verdana"">"
strFont2 = "<font style=""font-size: 16px; color: #000000; font-family:verdana"">"

Set oConn=Server.CreateObject("ADODB.Connection") 
oConn.Open strConnect
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 IN ("&searchIN&") or (dbo.Recordings.recordingtitle IN ("&searchIN&")))"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)
if not oRs1.eof then
	goodData=1
	arrResults=oRs1.getrows()
end if

end if

%>

<form method="post" action="music3.asp">

<%
Response.Write strFont1 & "Select Songs you Wish to Add to Your Playlist then click on Submit button<br />"
Response.Write "<br />"

if goodData =1 then
   For r = LBound(arrResults, 2) To UBound(arrResults, 2)
   		artistname		= arrResults(0,r)
   		RecordingTitle	= arrResults(1,r)
   		TrackTitle		= arrResults(2,r)
   		TrackFileName	= arrResults(3,r)
       	response.write strFont2 & "<div class=""results""><input type=""checkbox"" name=""selectItem"" value="""&TrackFileName&"""> "&artistname&" "&RecordingTitle&" "&TrackTitle&" "&TrackFileName&"</div>"
    next
else


Response.Write "No Albums Selected"   
end if


%>
<button type="submit">Submit</button>  <input type="button" value="BACK!"onClick="history.back();">
</form>


Thoughts
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 27 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 27 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros