ASP Query Help Needed.

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&")))"

Open in new window


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>

Open in new window



Thoughts
LVL 23
Thomas GrassiSystems AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
In your previous question, http://www.experts-exchange.com/Programming/Languages/Scripting/ASP/Q_28489632.html we are just supplying the recording title.


Your sql really just needs to be
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.Recordings.recordingtitle IN ('Houses of the Holy')))

Open in new window




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

Open in new window


We need to use the IN statement because you can select multiple.
0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott

I agree but only one question about that

What happens when the value is "Billboard Top 100 - 1970"

That will list all 100 of them

I was trying to get the Select to work with the artist name so that it would only list that recording for that artist.


Added note

The query works as long as the recordingtitle is unique but I have recodingtitles with the same name

Thoughts
0
John_VidmarCommented:
Careful, if the variable phrase (on line 15) contains a single-quote then that will mess up your SQL.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Thank's John.  Look up trgrassijr55's profile history by clicking on the username and you can see what he is working on in more detail.   This is not a very safe sql statement as it is and we have gone over that previously.  For now, just trying to help morph into something working, then work on a safer way of passing data.

I see what you want to do now, we need to build on your previous question and use a some more jquery to get the artist name in the form.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Building on the previous question http://www.experts-exchange.com/Programming/Languages/Scripting/ASP/Q_28489632.html we have this code to create a checkbox.

RecordingTitleCheckBox = "<input type="&"[x]"&"checkbox"&"[x]"&"  name="&"[x]"&"chkAlbum"&"[x]"&" value="&"[x]"&RecordingTitle&"[x]"&">"&RecordingTitle

Open in new window

The rendered html looks like
<input type="checkbox" name="chkAlbum" value="recording_title">Recording Title

Open in new window

I want to move to
<input type="checkbox" name="chkAlbum" data-artist="artist_name" value="recording_title">Recording Title

Open in new window

The asp code is going to be
RecordingTitleCheckBox = "<input type="&"[x]"&"checkbox"&"[x]"&"  name="&"[x]"&"chkAlbum"&"[x]"&" data-artist="&"[x]"& ArtistName&"[x]"&" value="&"[x]"&RecordingTitle&"[x]"&">"&RecordingTitle

Open in new window

Try adding that and then I will give you some jquery
0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott

added to my asp code musicajson.asp

RecordingTitleCheckBox = "<input type="&"[x]"&"checkbox"&"[x]"&"  name="&"[x]"&"chkAlbum"&"[x]"&" data-artist="&"[x]"& ArtistName&"[x]"&" value="&"[x]"&RecordingTitle&"[x]"&">"&RecordingTitle

Open in new window



Current musicajson.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%Response.Buffer = false%>
<!-- #include file="db_connection.inc" --> 
{"data": [
<%

Set oConn=Server.CreateObject("ADODB.Connection") 
oConn.Open strConnect
strSQL1 = "SELECT artists.ArtistName, Recordings.RecordingTitle FROM dbo.Artists AS artists INNER JOIN dbo.Recordings AS Recordings ON artists.ArtistID = Recordings.ArtistID UNION ALL SELECT artists.ArtistName, NULL RecordingTitle FROM dbo.Artists AS artists ORDER BY ArtistName, RecordingTitle "
Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

' send data to an array'
if not oRs1.eof then
	myArray=oRs1.getrows()
end if

oRs1.Close
Set oRs1 = Nothing
oConn.Close
Set oConn = Nothing

for r = LBound (myArray,2) to UBound(myArray,2)
	    artistname 		= myArray(0, r)
	    RecordingTitle 	= myArray(1, r)
	
        ' <input type="checkbox" value="link">Link
        RecordingTitleCheckBox = "<input type="&"[x]"&"checkbox"&"[x]"&"  name="&"[x]"&"chkAlbum"&"[x]"&" data-artist="&"[x]"& ArtistName&"[x]"&" value="&"[x]"&RecordingTitle&"[x]"&">"&RecordingTitle
	    ' RecordingTitleCheckBox = "<input type="&"[x]"&"checkbox"&"[x]"&"  name="&"[x]"&"chkAlbum"&"[x]"&" value="&"[x]"&RecordingTitle&"[x]"&">"&RecordingTitle
	    theRow =  replace("["&chr(34)&artistname&chr(34)&","&chr(34)&RecordingTitleCheckBox&chr(34)&"]","\","\\")
	    theRow = replace(theRow,"[x]","\"&chr(34))
	    response.write theRow
	    
        if r < UBound(myArray,2) then
    	response.write ","
	
	
    end if
next
%>
]}

Open in new window



No errors still getting same results.

I could make sure that I do not have any duplicate Recording Titles. That would mean modifying many records in Database and on file system.

What jquery do we need?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Also need to add a where clause back to the sql.   I will post back this evening.
0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott

thanks

Will wait for your response
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
First, please put your json output to 20 or 30 for testing please.

On the main page, try this code.  Using jquery we are appending an input to form.  We can change this to hidden later.   Now you can update the sql on the final page to use both artist and album.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>
<html>
<head>
<link href="css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<script src="js/jquery-1.11.0.min.js"></script>
<script src="js/jquery.dataTables.js"></script>

<script>
    $(document).ready(function() {
    $('#example').dataTable( {
        "ajax": 'MusicAJson.asp',
         "deferRender": true
    });
    $('input[name="chkAlbum"]').change(function(){
        artist = $(this).attr('data-artist');
        if (this.checked){
            $('form').append('<input name="artist" value="'+artist+'">');
        }

    });

} )
</script>
  <meta charset="utf-8">
  <title>TomsMP3</title>
</head>
<body>
<form name="input" action="SelectAlbums.asp" method="post">

<table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Artist Name</th>
                <th>Recording Title</th>
               
            </tr>
        </thead>
 
        <tfoot>
            <tr>
                <th>Artist Name</th>
                <th>Recording Title</th>
               
            </tr>
        </tfoot>
    </table>
    <button type="submit">Submit</button>  <input type="button" value="BACK!"onClick="history.back();">
</form>   
</body>

Open in new window

0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott

New code no errors.

Unclear on what to do next?

 Now you can update the sql on the final page to use both artist and album     Where?

The statement in question on the final page is this line

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&")))"

Open in new window


I believe we want to the Where clause to be AND not OR for one thing

Second the  ("&searchIN&")    should be one for artist and the other for the album

But what do I change

Here is the entire Last Page

<!-- #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>

Open in new window



Thanks
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Well you have

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

Where you see searchWord, maybe change that to searchAlbum

Then add

searchArtist = Request.form("artist") 'change form to method post

Then you can  use

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 ("&searchArtist&") AND (dbo.Recordings.recordingtitle IN ("&searchAlbum&")))"

Open in new window

0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott

Thanks

Testing that now

Should I change this line also

dim searchWord, searchIN

to

dim searchWord, searchIN, searchArtist



<!-- #include file="db_connection.inc" -->
<%
dim searchAlbum, searchArtist,searchIN
dim strFont1
dim strFont2
searchIN = ""

goodData=0

searchAlbum = Request.form("chkAlbum") 'change form to method post 
searchArtist = Request.form("artist") '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'

Open in new window



Change lines 15 20?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
You are going to have to create this bit for each of searchArtist and searchAlbum
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'

Open in new window

0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott,

Ok made changes

Get this error

Microsoft VBScript compilation  error '800a03f6'

Expected 'End'

/SelectAlbums.asp, line 65


I added end IF after line 43   but when I did that I got this message

No Albums Selected


My new code

<!-- #include file="db_connection.inc" -->
<%
dim searchAlbum, searchArtist, searchINal, searchINar
dim strFont1
dim strFont2
searchINal = ""
searchINar = ""


goodData=0

searchAlbum = Request.form("chkAlbum") 'change form to method post 
searchArtist = Request.form("artist") 'change form to method post 

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

if searchAlbum <> "" then
arrSearchAlbum = split(searchAlbum,",") ' convert to array'
for each phrase in arrSearchAlbum
    searchINal=searchINal & "'" & Trim( phrase ) & "'," 
next
searchINal=left(searchINal,len(searchINal)-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 ("&searchINar&") AND (dbo.Recordings.recordingtitle IN ("&searchINal&")))"

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>

Open in new window



What am I missing
0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott,

You back?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Try and indent your code so you can see what is going on.  Every time you start a section of code, it needs to be closed in the same order.  You had two end if's missing and one not needed.
<!-- #include file="db_connection.inc" -->
<%
dim searchAlbum, searchArtist, searchINal, searchINar
dim strFont1
dim strFont2
searchINal = ""
searchINar = ""


goodData=0

searchAlbum = Request.form("chkAlbum") 'change form to method post 
searchArtist = Request.form("artist") 'change form to method post 

if searchArtist <> "" then
  arrSearchArtist = split(searchArtist,",") ' convert to array'
  for each phrase in arrSearchArtist
    searchINar=searchINar & "'" & Trim( phrase ) & "'," 
  next
  searchINar=left(searchINar,len(searchINar)-1) ' remove the last comma'
end if ' ***** added end if

if searchAlbum <> "" then
  arrSearchAlbum = split(searchAlbum,",") ' convert to array'
  for each phrase in arrSearchAlbum
    searchINal=searchINal & "'" & Trim( phrase ) & "'," 
  next
  searchINal=left(searchINal,len(searchINal)-1) ' remove the last comma'
end if  ' **** added end if'

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 ("&searchINar&") AND (dbo.Recordings.recordingtitle IN ("&searchINal&")))"

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

'** take this out 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>

Open in new window

Also, check your sql statement.  You have searchINar twice. I'm guessing that is because  you have the same names in line 20 and 28.  One should be for the artist and one for the album.  Each different not the same.
0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott,

Welcome back

made the above changes

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near ')'.

/SelectAlbums.asp, line 39


check it out http://www.tomsmp3.com/MusicAtable.asp  select one then click submit your see what I get.

Also I looked at the sql string  and it looks ok

current code

<!-- #include file="db_connection.inc" -->
<%
dim searchAlbum, searchArtist, searchINal, searchINar
dim strFont1
dim strFont2
searchINal = ""
searchINar = ""


goodData=0

searchAlbum = Request.form("chkAlbum") 'change form to method post 
searchArtist = Request.form("artist") 'change form to method post 

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

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

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 ("&searchINar&") AND (dbo.Recordings.recordingtitle IN ("&searchINal&")))"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)
if not oRs1.eof then
	goodData=1
	arrResults=oRs1.getrows()
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>

Open in new window

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
What is the code for SelectAlbums.asp
0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott

The last post the current code is for selectalbums.asp
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I bet the real issue is the line prior on line 37, double check your closing parentheses.   You have too many.  For each opening (, there should be a closing ) in the same order.  

Also in your sql, you should not have  searchINal twice.   You need one for artistname and the other for recordingtitle.

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 ("&searchINar&") AND (dbo.Recordings.recordingtitle IN ("&searchINal&")))"
0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott

Line 37

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 ("&searchINar&") AND (dbo.Recordings.recordingtitle IN ("&searchINal&")))"

Open in new window



I do not see searchINar twice only once  

Also the )  add up not sure what is wrong here?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
>I do not see searchINar twice only once  

Ok, I see, the two names are very close.  Probably best to use variables with more meaning and difference. It could hard to trace errors with something like that.  

Good catch on parens.  

I still think something is wrong with the sql.  Try to first eliminate the where clause (make sure for testing you use "select top 30 ..." so you are not getting all records).  Then add one where clause.  If that works, then the next.   See if you can pin point the issue.

You can also check that you have data.  to test
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 ("&searchINar&") AND (dbo.Recordings.recordingtitle IN ("&searchINal&")))"
response.write strSQL1 ' ** for testing
response.end ' ** for testing

Open in new window

0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott

first test

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 ("&searchINar&"))"
response.write strSQL1 ' ** for testing
response.end ' ** for testing

Open in new window



Results

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 ())


The value  ("&searchINar&") is empty  looks like the code from 12 to 21 is not correct
Thoughts
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
First, please keep the results to 20 while we are testing.  "SELECT TOP 20...."

On line 15 of your code there is some jquery.  Try changing
 $('input[name="chkAlbum"]').change(function(){

Open in new window

to
$('#example tbody').on('click', 'input[name="chkAlbum"]', function () {

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thomas GrassiSystems AdministratorAuthor Commented:
Scott

After seeing that no data in the ("&searchINar&")  using this test 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 ("&searchINar&"))"
response.write strSQL1 ' ** for testing
response.end ' ** for testing

Open in new window



I wanted to see what was coming in to the page

So I tried this

<!-- #include file="db_connection.inc" -->
<%
dim searchAlbum, searchArtist, searchINal, searchINar
dim strFont1
dim strFont2
searchINal = ""
searchINar = ""


goodData=0

searchAlbum = Request.form("chkAlbum") 'change form to method post 
searchArtist = Request.form("artist") 'change form to method post 

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

response.write searchartist '** for testing
response.write searchinar '** for testing
response.end ' ** for testing



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

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

Open in new window



But this gave me a blank page

How can I show those two values?
0
Thomas GrassiSystems AdministratorAuthor Commented:
Scott,

This made it working.  

I think the removing of the .change did the trick?

Now to my final stage of this project.

All main pages are now the way I like and the last page which displays each song I want to use the accordion method to display them
Do not like more than one page of output. the Accordion method will help that.

Thanks for all your help.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Ok, great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.