Link to home
Start Free TrialLog in
Avatar of Member_2_6492660_1
Member_2_6492660_1Flag for United States of America

asked on

Need help with ASP responce.write checkbox name

ASP classic
Ajax
Json

I had two pages that displayed information by using my file system folders and sub folders.
All this information also is in a SQL database. So I decided to use the SQL database records for my selections and queries.
This makes it easier for my to write sql queries than to try and use the file system.

Also using ajax and json to help build a better page display.

The page I am working on builds a list which has checkbox so the user can select the items they wish to see.
Once checked then hit the submit button and the new page presents a list of the selected items

That's the part I am having trouble with at this time I am getting a blank page

The original method produces a very long list and that's is what I wanted to change so I used ajax and json with several js and css codes.

So I have the test pages almost working.

here is the site to check out the original pages and the new pages.

The original pages
http://www.tomsmp3.com/AlbumSearch.asp

You will see a very long list 8232 records
If you select any and click on submit it takes you to http://www.tomsmp3.com/SelectAlbums.asp


That is the display I am trying to get to with my new code.

The new code
http://www.tomsmp3.com/musicatable.asp

I am only displaying top 50 records for testing

If you check any of the records and click on submit you will see what I am talking about.

The code for the new page is here

MusicAtable.asp
<%@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
    } );
} )
</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



You can see the above uses this

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

Set oConn=Server.CreateObject("ADODB.Connection") 
oConn.Open strConnect
strSQL1 = "set rowcount 50 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]"&"track_title"&"[x]"&" type="&"[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


The code on lines 22 to 35 is what I need help on

The uses selectalbums.asp    
selectablums.asp is expecting "CHKALBUM" to be passed to it

Here is selectalbums.asp

<!-- #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 artists selected"   
end if


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

Open in new window


thanks
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

Since the check box name is,track_title, you have to use request.form("track_title") to get the data.

<input type="checkbox" name="track_title" billboard="" top="" 100="" -="" 1975"="">

Open in new window


It will be in the form of a comma delimited file if multiple items are checked.

Just change

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

to

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

Is that the data you are expecting though?
You can see your WHERE clause will also need to be changed too.  Note below what you have.

WHERE     (dbo.Artists.artistname IN ("&searchIN&") or (dbo.Recordings.recordingtitle IN ("&searchIN&"))
Ok, also, what happened to the value?

<input type="checkbox" name="track_title" billboard="" top="" 100="" -="" 1975"="">

The bold does not mean anything.  Should be

<input type="checkbox" name="track_title" value="some_title">
Avatar of Member_2_6492660_1

ASKER

Scott,

I see your back.

1.  Not sure about the data yet once I get it to display then I will know.

2. The where clause is ok Look at selectalbums.asp "searchIn" is created from chkAlbum  lines 12 to 17

3. Again not sure about the value yet.

 You said this
It will be in the form of a comma delimited file if multiple items are checked.

 Just change

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

 to

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


selectalbums.asp line 10 is searchWord = Request.form("chkAlbum")        Already.

I would rather keep this this way and not use "track_Title"


SelectAlbums.asp is already in production and would like to keep it from being changed at this time.

Can we fix musicAJson.asp  I think Line 27  needs the change

Line 27 currently

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

Open in new window



How does this look?

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

Open in new window



Just tried that and I get message no albums selected
It looks right. Give it a try.

I'm intermittent for the next week....
Scott,

Just tested get this "No Albums Selected"   that message comes from selectAlbums.asp

So musicajson.asp not passing the data yet.


My 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 = "set rowcount 50 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]"&" type="&"[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

ASKER CERTIFIED SOLUTION
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Scott

That was it

Check it out http://www.tomsmp3.com/musicatable.asp

I need to work on the sql query in selectAlbums.asp

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

Not doing exactly as I expect. I believe the or condition is my problem need to work on that.


Your code help was great thanks again
The where statement is looking for the artist name or the recordingtitle.  Don't we just want recording title?
Scott

Example  

Recording Title   "Billboard Top 100 - 1970"

This title has 100 different artists.

So the artist name and the recordingtitle  has to match otherwise all 100 would appear.

I think I need to change the OR to AND


 Current using OR
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


Change

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

Open in new window


Been testing in SQL using the and seems to be working

Your thoughts?
I'm not at my regular place this week and I have a test copy of your db there.  All you can do is try and if it gives you the results you want, it is a winner.
Scott

I think I have it figured out but need some code help

I opened another ticket

https://www.experts-exchange.com/questions/28491000/ASP-Query-Help-Needed.html

Thanks