Solved

ASP code help to produce output list as url

Posted on 2014-04-21
21
324 Views
Last Modified: 2014-04-24
ASP code below,

Would like to produce the list of output given a url so they can click on the line item and it will play the song.

currently the output just lists the name of the songs.

Is it also possible to have each line of output have a checkbox so they can select which songs they want to play and then it could create a play list for them mysong.m3u that would produce a separate page with just the list of songs they selected.




<%
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=xxxxxxxx;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 = '"& searchWord&"') OR (dbo.Recordings.RecordingTitle = '"& searchWord&"') OR (dbo.Tracks.TrackTitle = '"& searchWord&"')"

Set oRs1=oConn.Execute(strSQL1,lngRecs,1)

if not oRs1.eof then
    do until oRs1.eof
         response.write "<div class=""results"">"&oRs1("artistname")&" "&oRs1("RecordingTitle")&" "&oRs1("TrackTitle")&" "&oRs1("TrackFileName")&"</div>"
    oRs1.movenext
    loop
end if
%>

Open in new window


Any help would be great
0
Comment
Question by:Thomas Grassi
  • 10
  • 10
21 Comments
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
how do you normally play the files? where are that paths to the file stored?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
Your checkbox would probably look like this

 response.write "<div class=""results""><input type=""checkbox"" name=""selectItem"" value="""&oRs1("TrackFileName")&"""> "&oRs1("artistname")&" "&oRs1("RecordingTitle")&" "&oRs1("TrackTitle")&" "&oRs1("TrackFileName")&"</div>"

Open in new window


Then your output would be a comma separated text string.  To see for your self, do a
response.write request.form("selectItem")

Open in new window


Take a look at how forms work to start with http://www.w3schools.com/html/html_forms.asp
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
To parse the response of a comma delimited field, you will create an array and loop through it.

dim PlayList 
PlayList = request.form("selectItem")
arrPlayList = split(PlayList,",")

For each item in arrPlayList
    response.write Item &"<br>"
    ' Or play song
next

Open in new window

http://www.w3schools.com/vbscript/func_split.asp
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott

Thanks for replying
Sorry for the delay back long day at work today.

I put the checkbox code it and now it displays a check box that's great.
You second Posting with the array where do I place that code

<%
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=xxxxxxxx;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


Thanks in advance
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Big Monty:

The music gets played on any media player, works on Computer IE, Firefox Chrome, Safari, on iPad iPhone Android etc.

www.tomsmp3.com   check it out.

The files are on a drive connected to the web server using IIS 6.0
The all are in a sql database from which I can run the query from.

Working on fine turning the query.

Thanks in advance
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
You are going to place the checkbox's in a form.  When the submit button is used, the form data for the check box's will be a comma delimited string.  

You would place the array on the page you are submitting the data to.
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott little confused about where this goes.

You are talking about the ASP Code that I listed above correct?

I tried adding it after the end if but that did not work

You suggestion?
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
Comment Utility
It would be best to start with plain htm, get that to work, then back in your scripting.

For testing try this

This goes on page1.asp
<form method="post" action="page2.asp">
<input type="checkbox" name="mySelections" value="Song 1">
<input type="checkbox" name="mySelections" value="Song 2">
<input type="checkbox" name="mySelections" value="Song 3">
<button type="submit">Submit</button>
</form>

Open in new window

On page2.asp
<%
if request.form<>"" then

response.write "This is what the raw data from the form post looks like<br>"
response.write request.form
response.write "<hr>This is the raw data just from the checkbox's  mySelections"&strSongs&"<br>
response.write "This is data parsed from the checkbox<br>"
strSongs=request.form("mySelections")
arraySongs=split(strSongs,",")
for each song in arraySongs
     response.write song&"<br>"
next
else
response.write "You did not post any data yet"
end if
%>

Open in new window

0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott

Tested the above code and it worked.

Now working on getting that code into my asp code.

Thanks for the help will post results later
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott

Back to my scripting and seem to not be able to incorporate your code from above.

Try placing the form in my asp code but every time I tested the page I got http 500

Removed the code now page works.

How can I add the submit function to mode code below?

<%
dim searchWord
dim strFont1
dim strFont2
searchWord = Request.Querystring("song") 
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 "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxxxxxxxxxx;Password=xxxxxxxxxxxxxx;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)

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

if not oRs1.eof then
    do until oRs1.eof
       response.write strFont2 & "<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


I am working on page2.asp which will be music3.asp

The above code is music2.asp
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
Try turning on errors / sending to the browser http://www.iis.net/learn/application-frameworks/running-classic-asp-applications-on-iis-7-and-iis-8/classic-asp-script-error-messages-no-longer-shown-in-web-browser-by-default

If you don't have access to this, you will have to ask your host to do it for you.

The 500 errors are generic.  By turning on asp errors, you will get the line number and possible issue.
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott

Thanks this is for IIS 7 and IIS 8

I am using IIS 6 and I found this
http://www.reedolsen.com/show-errors-for-classic-asp-pages-in-iis-6/

I do have that setting on

But I got the Submit button added and working sorta

Making progress Will post more later.
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scoot Good new got the Page working now

Check it out

When you select the songs from the search output the new page created lists only the songs you check off

Now I need to convert that out to a URL so they can click on it to play it

any thoughts?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
On http://www.tomsmp3.com/page2.asp I end up with

M:\Music\MP3MusicAlbums\Guess Who\Billboard Top 100 - 1970\02-Billboard Top 100 - 1970-03 No Sugar Tonight.mp3

You need a simple link http://www.w3schools.com/html/html_links.asp

<a href="M:\Music\MP3MusicAlbums\Guess Who\Billboard Top 100 - 1970\02-Billboard Top 100 - 1970-03 No Sugar Tonight.mp3">No Sugar Tonight</a>

Open in new window

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
Actually, the link has to be relative to your domain.

<a href="M:\Music\MP3MusicAlbums\Guess Who\Billboard Top 100 - 1970\02-Billboard Top 100 - 1970-03 No Sugar Tonight.mp3">No Sugar Tonight</a>

<a href="www.tomsmp3.com/folder/folder/something.mp3">Something</a>

Open in new window


You probably actually need to access the mp3 via file system object if it is outside of the domain. That would be another question though.
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott

Can not get the url to work in page2.asp

<%
if request.form<>"" then

strSongs=request.form("selectItem")
arraySongs=split(strSongs,",")
for each song in arraySongs
   response.write <a href="song&"> </a>
   next
else
response.write "You did not post any data yet<br>"
end if
%>
 <input type="button" value="BACK!"onClick="history.back();">

Open in new window


Any thoughts

Also I just saw your last post:

If you look at the results of page2.asp

It is in the correct format just need to make it a url output line.

Then I have a routine that will convert the path but I need the url created first.
0
 
LVL 23

Author Closing Comment

by:Thomas Grassi
Comment Utility
Scott,

Closing this going to open new one for the url issue

thanks
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
for each song in arraySongs
   response.write "<a href=""&song&"">"&song&"</a>"
next

Open in new window

0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott,

Got the url but it is all on one line how do I break <br> the line between each entry

output looks like this
M:\Music\MP3MusicAlbums\ZZ Top\Eliminator\09-Eliminator-Dirty Dog.mp3 M:\Music\MP3MusicAlbums\ZZ Top\Eliminator\10-Eliminator-If I Could Only Flag Her Down.mp3 M:\Music\MP3MusicAlbums\ZZ Top\Eliminator\11-Eliminator-Bad Girl.mp3 M:\Music\MP3MusicAlbums\ZZ Top\Greatest Hits\01-Greatest Hits-Gimme All Your Lovin'.mp3

check the site out
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
for each song in arraySongs
   response.write "<a href=""&song&"">"&song&"</a><br>"
next

Open in new window


or

response.write "<ul>"
for each song in arraySongs
   response.write "<li><a href=""&song&"">"&song&"</a></li>"
next
response.write "</ul>"

Open in new window

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
If you go the li method, add this to your css

li{list-style:none;}

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Uploading files to the web server has become common part of almost any kind of web application. People use different technologies to solve this, but regardless of the technology used, it is always useful to have some kind of progress indicator shown…
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.
This video teaches users how to migrate an existing Wordpress website to a new domain.
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.

772 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