Solved

ASP code to access SQL Data base

Posted on 2014-04-15
21
445 Views
Last Modified: 2014-04-17
ASP
IIS 6.0
SQL 2008

I have a Music SQL Database which has three tables Artists, Recording and Tracks

Created a web site that lists all the mp3 files but the list is over 35,000 files long.

That works but I would like to have the users be able to search by artists, songs and or Albums.

Also the output should display in a new window just listing the song or all the songs by the artist or all the songs in an album etc

I have the db_connection.inc file created and it works.

I would like this to be in asp code.

I do use Microsoft Expression Web 4 for my development work.

Any samples or suggestions how to do this.
0
Comment
Question by:Thomas Grassi
  • 10
  • 9
21 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Are you looking for examples of how to connect to the database (your subject tends to suggest this however you say you have a database.inc that works)

Or are you wanting code on how to retrieve the output and display it based on searches and sorting?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
What is the sql you are using now?

What is the layout for the tables Artists, Recording and Tracks?

Assuming you have these 3 related together in one view/query.
> search by artists, songs and or Albums
>just listing the song

You will start with a form on page1.asp
<form action="page2.asp" method="post">
Search: <input type="text" name="search">
<button type="submit">Search</button>
</form>

Open in new window

Then on page 2
<%
' ************
' Clean data.  For this sample use Server.HTMLEncode  http://www.w3schools.com/asp/met_htmlencode.asp
' for more robust scrubbing look at http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx
'*************
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

' assume a view/query called vSearchByArtistSongAlbum with fields artist, song, song_id  album.  
' Only allow first 30 results
' for exact match, change LIKE to = and remove the %
SQL = "SELECT TOP 30 song, song_id FROM vSearchByArtistSongAlbum WHERE artist LIKE '%"&searchWord&"%' OR  song LIKE '%"&searchWord&"%' OR album LIKE '%"&searchWord&"%'"

' make your connection and create a recordset called rsResults
'use either getrows() which is faster or loop through recordset
' sample looping through recordset
if not rsResults.eof then
    do until rsResults.eof
         response.write "<div class=""results"">"&rsResults("song")&"</div>"
    rsResults.movenext
    loop
end if


%>

Open in new window


If you want to use getrows, I have a short explanation here http://www.experts-exchange.com/Programming/Languages/Scripting/ASP/Q_28366983.html#a39866067.  The advantage of getrows is it will be quicker to output a lot of rows, plus you can reuse the data.  It can be a bit more confusing at first because you are using an array.  But the way I have explained it where the array key is converted to a name may help.
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott thanks for responding so fast.

Tables
(Artists)
artistid
artistname

(Recording)
RecordingID
Recording Title
Year
Location
Description
alternate_name
Album_rating
Publisher
Genre
ArtistID

(Tracks)
TrackID
TrackTitle
RecordingID
TrackSequence
TrackFileName


Also I have a HTML coded home page that has this code

<form name="input" action="music2.asp" method="get">
                 Song, Artist, Album: <input type="text" name="song">
                 <input type="submit" value="Submit">
                </form>


music2.asp will be the search code
QueryMusic.asp will be the output of the search



This is my sample code from another page I have which only lists from another sql database.

</body>
</html>
<!-- #include file="db_connection.inc" -->
<%
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open strConnect
strSQL = "Select last,first,team,id from names order by last"
Set oRs=oConn.Execute(strSQL,lngRecs,1)
%>
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
You have 3 pages?  
-index.asp                Home Page
-music2.asp             Data gets posted to
-querymusic.asp      Show Results

Why 3 pages?  Do you mean to do an ajax request?  Otherwise the code that you send data to and display's the data can be on the same page.  

When you said pop up, do you mean a small window that opens up as a small window?  A modal would be a better choice especially for mobile.
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott

My home page is music.html
www.tomsmp3.com

I do not know what  a ajax request is.

It can be on the same page That is ok with me how do I do that

Check out the link above you will see better than my writing
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Yes that will be fine

Did you see the site?

Any code ideas?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
Take a look at what I already posted but your sql might be something like this  http://sqlfiddle.com/#!3/686cf/2

SELECT dbo.tArtists.artistname, dbo.tRecording.RecordingTitle, dbo.tTracks.TrackTitle FROM dbo.tArtists INNER JOIN dbo.tRecording ON dbo.tArtists.artistid = dbo.tRecording.ArtistID INNER JOIN dbo.tTracks ON dbo.tRecording.RecordingID = dbo.tTracks.RecordingID

Open in new window


If we had sample tables
CREATE TABLE tArtists 
	(
     artistid int identity primary key, 
     artistname varchar(20), 
    
    );
 
INSERT INTO tArtists
(artistname)
VALUES
('John'),
('Cathy'),
('Bruce'),
('Bill'),
('Mike');


CREATE TABLE tRecording 
	(
     RecordingID int identity primary key,
     ArtistID int, 
     RecordingTitle varchar(20), 
    
    );
 
INSERT INTO tRecording
(ArtistID, RecordingTitle)
VALUES
('1','Nashville Now'),
('2', 'Something Blue'),
('3', 'Down Road'),
('4', 'Hot Hot Hot'),
('5', '500 Bottles Of Beer');

CREATE TABLE tTracks 
	(
     TrackID int identity primary key,
     RecordingID int, 
     TrackTitle varchar(20), 
    
    );
 
INSERT INTO tTracks
(RecordingID, TrackTitle)
VALUES
('1','Way Down South'),
('2', 'The Colors'),
('3', 'Highway To Home'),
('4', 'Beach Ball'),
('5', 'Go Wisconsin');

Open in new window


Try and plug that sql into the answer I have http:#a40003560 and let me know what you come up with.  You only need 2 pages.  The form page and the receiver/results page.
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott

like this

<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

<!-- #include file="db_connection.inc" --> 
<% 
Set oConn=Server.CreateObject("ADODB.Connection") 

SQL = SELECT dbo.tArtists.artistname, dbo.tRecording.RecordingTitle, dbo.tTracks.TrackTitle FROM dbo.tArtists INNER JOIN dbo.tRecording ON dbo.tArtists.artistid = dbo.tRecording.ArtistID INNER JOIN dbo.tTracks ON dbo.tRecording.RecordingID = dbo.tTracks.RecordingID

if not rsResults.eof then
    do until rsResults.eof
         response.write "<div class=""results"">"&rsResults("song")&"</div>"
    rsResults.movenext
    loop
end if


%>

Open in new window



I removed all the comments

Where will the output be displayed?

Can I enter on the main page (music.htm) the name of a song artist or track title?
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott,

I tested and got this

Microsoft VBScript compilation error '800a03ea'

Syntax error

/music2.asp, line 10
SQL = SELECT dbo.tArtists.artistname, dbo.tRecording.RecordingTitle, dbo.tTracks.TrackTitle FROM dbo.tArtists INNER JOIN dbo.tRecording ON dbo.tArtists.artistid = dbo.tRecording.ArtistID INNER JOIN dbo.tTracks ON dbo.tRecording.RecordingID = dbo.tTracks.RecordingID



check it out www.tomsmp3.com  

Enter a song name or artist or album

Check my code above

Thanks
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
It looks like you are using my exact example and that does not match your exact db layout.  I have my tables named like tArtists and yours may just be Artists or Artist.  Also, I am using the schema dbo because I am used to using mssql server.   You may be using a different db or a different schema.

Also, the sql statement needs to be string.

 Instead of,
SQL = SELECT dbo.tArtists.artistname, ....

Open in new window

SQL = "SELECT dbo.tArtists.artistname, ...."

Open in new window

What I gave you did not have a where clause.  Let's first try this with a hard coded search phrase.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
If you are using mssql server, can you use manager studio, create a new view and get something like this to work?  The example below will need to be modified to your own layout.   Once you have this, save it, name it something like vSearchArtistSongTitle or anything meaningful to you. I typically put a v before any views and a t before any tables to help me know which I am using.

SELECT     dbo.tArtists.artistname, dbo.tRecording.RecordingTitle, dbo.tTracks.TrackTitle
FROM         dbo.tArtists INNER JOIN
                      dbo.tRecording ON dbo.tArtists.artistid = dbo.tRecording.ArtistID INNER JOIN
                      dbo.tTracks ON dbo.tRecording.RecordingID = dbo.tTracks.RecordingID
WHERE     (dbo.tArtists.artistname = 'searchWord') OR
                      (dbo.tRecording.RecordingTitle = 'searchWord') OR
                      (dbo.tTracks.TrackTitle = 'searchWord')

Open in new window


If you are finding that too hard, can you again, go to manager studio, open up the db, open up the Tables folder, select one of the tables, right click, Script Table As, Create To, New Query Window.  This will give you some output similar to
CREATE TABLE [dbo].[tArtists](
	[artistid] [int] IDENTITY(1,1) NOT NULL,
	[artistname] [varchar](20) NULL,

Open in new window

Please do this for each of the 3 tables you are using and post back here we can put together a view or sql statement to use.
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott

Thanks for the update.

Removed the "t" added "s" at end of recording  should be recordings


I ran this from SSMS

ELECT     dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle
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 = 'The Doors') OR
                      (dbo.Recordings.RecordingTitle = 'The Doors') OR
                      (dbo.Tracks.TrackTitle = 'The Doors')




artistname      RecordingTitle      TrackTitle
The Doors      Billboard Top 100 - 1967      Light My Fire
The Doors      Billboard Top 100 - 1968      Hello I Love You
The Doors      Billboard Top 100 - 1969      Touch Me
The Doors      Billboard Top 100 - 1971      Love Her Madly
The Doors      Billboard Top 100 - 1971      Riders On The Storm
The Doors      L.A. Woman      The Changeling
The Doors      L.A. Woman      Love Her Madly
The Doors      L.A. Woman      Been Down So Long
The Doors      L.A. Woman      Cars Hiss by My Window
The Doors      L.A. Woman      L.A. Woman
The Doors      L.A. Woman      L' America
The Doors      L.A. Woman      Hyacinth House
The Doors      L.A. Woman      Crawling King Snake
The Doors      L.A. Woman      The WASP (Texas Radio and the Big Beat)
The Doors      L.A. Woman      Riders on the Storm
The Doors      Live At The Hollywood Bowl      Wake Up
The Doors      Live At The Hollywood Bowl      Light My Fire
The Doors      Live At The Hollywood Bowl      Unknown Soldier
The Doors      Live At The Hollywood Bowl      A Little Game
The Doors      Live At The Hollywood Bowl      The Hill Dwellers
The Doors      Live At The Hollywood Bowl      Spanish Caravan
The Doors      Live At The Hollywood Bowl      Light My Fire (Edit Of Live Version)
The Doors      Morrison Hotel      Blue Sunday
The Doors      Morrison Hotel      Indian Summer
The Doors      Morrison Hotel      Land Ho!
The Doors      Morrison Hotel      Maggie M'Gill
The Doors      Morrison Hotel      Peace Frog
The Doors      Morrison Hotel      Queen Of The Highway
The Doors      Morrison Hotel      Roadhouse Blues
The Doors      Morrison Hotel      Ship Of Fools
The Doors      Morrison Hotel      The Spy
The Doors      Morrison Hotel      Waiting For The Sun
The Doors      Morrison Hotel      You Make Me Real
The Doors      The Doors      Break On Through(To The Other Side)
The Doors      The Doors      Soul Kitchen
The Doors      The Doors      The Crystal Ship
The Doors      The Doors      Twentieth Century Fox
The Doors      The Doors      Alabama Song(Whisky Bar)
The Doors      The Doors      Light My Fire
The Doors      The Doors      Back Door Man
The Doors      The Doors      I Looked at You
The Doors      The Doors      End Of The Night
The Doors      The Doors      Take It As It Comes
The Doors      The Doors      The End
The Doors      The Matrix- San Fran '67      People Are Strange
The Doors      The Matrix- San Fran '67      Alabama Song (Whisky Bar)
The Doors      The Matrix- San Fran '67      The Crystal Ship
The Doors      The Matrix- San Fran '67      Twentieth Century Fox
The Doors      The Matrix- San Fran '67      Moonlight Drive
The Doors      The Matrix- San Fran '67      Summer's Almost Gone
The Doors      The Matrix- San Fran '67      Unhappy Girl
The Doors      The Matrix- San Fran '67      Back Door Man
The Doors      The Matrix- San Fran '67      My Eyes Have Seen You
The Doors      The Matrix- San Fran '67      Soul Kitchen
The Doors      The Matrix- San Fran '67      Get Out of My Life Women
The Doors      The Matrix- San Fran '67      Crawling King Snake
The Doors      The Matrix- San Fran '67      I Can't See Your Face In My Mind
The Doors      The Best of the Doors [1985] Disc 1      Break on Through (To the Other Side)
The Doors      The Best of the Doors [1985] Disc 1      Light My Fire
The Doors      The Best of the Doors [1985] Disc 1      The Crystal Ship
The Doors      The Best of the Doors [1985] Disc 1      People Are Strange
The Doors      The Best of the Doors [1985] Disc 1      Strange Days
The Doors      The Best of the Doors [1985] Disc 1      Love Me Two Times
The Doors      The Best of the Doors [1985] Disc 1      Alabama Song (Whiskey Bar)
The Doors      The Best of the Doors [1985] Disc 1      Five to One
The Doors      The Best of the Doors [1985] Disc 1      Waiting for the Sun
The Doors      The Best of the Doors [1985] Disc 1      Spanish Caravan
The Doors      The Best of the Doors [1985] Disc 1      When the Music's Over
The Doors      The Best of the Doors [1985] Disc 2      Hello I Love You
The Doors      The Best of the Doors [1985] Disc 2      Roadhouse Blues
The Doors      The Best of the Doors [1985] Disc 2      L.A. Woman
The Doors      The Best of the Doors [1985] Disc 2      Riders on the Storm
The Doors      The Best of the Doors [1985] Disc 2      Touch Me
The Doors      The Best of the Doors [1985] Disc 2      Love Her Madly
The Doors      The Best of the Doors [1985] Disc 2      The Unknown Soldier
The Doors      The Best of the Doors [1985] Disc 2      The End


That worked

My current ASP code now below

<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

<!-- #include file="db_connection.inc" --> 

Set oConn=Server.CreateObject("ADODB.Connection") 


SQL = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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"

if not rsResults.eof then
    do until rsResults.eof
         response.write "<div class=""results"">"&rsResults("song")&"</div>"
    rsResults.movenext
    loop
end if


%>

Open in new window



I am getting this error now

Microsoft VBScript runtime error '800a01a8'

Object required: 'rsResults'

/music2.asp, line 12


Now my ASP code does not have the where clause

should I add that?

I should replace the "The Doors" with "search"

Search comes from the form correct?
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott,

The site was down so I made some changes

<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

<!-- #include file="db_connection.inc" --> 

Set oConn=Server.CreateObject("ADODB.Connection") 


SQL = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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")

if not rsResults.eof then
    do until rsResults.eof
         response.write "<div class=""results"">"&rsResults("song")&"</div>"
    rsResults.movenext
    loop
end if


%>

Open in new window



check out the site for the error      www.tomsmp3.com

this is the error

Microsoft VBScript compilation error '800a03ea'

Syntax error

/music2.asp, line 11
WHERE     (dbo.Artists.artistname = "searchWord") OR
 --------------------------------------------------------------------------^
<%
dim searchWord
searchWord=Server.HTMLEncode(request.form("search"))

<!-- #include file="db_connection.inc" --> 

Set oConn=Server.CreateObject("ADODB.Connection") 


SQL = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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")

if not rsResults.eof then
    do until rsResults.eof
         response.write "<div class=""results"">"&rsResults("song")&"</div>"
    rsResults.movenext
    loop
end if


%>

Open in new window

0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
Comment Utility
When you said your page was listing items without the search, I assumed you were creating a recordset.  The last code you posted you did not create a recordset.  http://support.microsoft.com/kb/289531  

Your query should look something like
sql = "SELECT     dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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 = 'The Doors') OR (dbo.Recordings.RecordingTitle = 'The Doors') OR (dbo.Tracks.TrackTitle = 'The Doors')"

Open in new window

With a variable
sql = "SELECT     dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle 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&"')"

Open in new window

I replaced 'The Doors' with "& searchWord&" where the double quotes are inside the single quotes.

Since you are using data that is user input, you should really use a parameterized query to make your recordset.  It is just a bit more code, but it helps keep things safer.  http://support.microsoft.com/kb/q200190
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
We posted about the same time.  You need to put your query all on one line.  Or you can build it like this

SQL = "SELECT dbo.Artists.artistname, dbo.Recordings.RecordingTitle, dbo.Tracks.TrackTitle" SQL = SQL& "FROM dbo.Artists INNER JOIN dbo.Recordings ON dbo.Artists.artistid ="
SQL = SQL& " dbo.Recordings.ArtistID INNER JOIN dbo.Tracks ON "
SQL = SQL& "dbo.Recordings.RecordingID = dbo.Tracks.RecordingID"
SQL = SQL& "      WHERE     (dbo.Artists.artistname = '"&searchWord&"') OR "
SQL = SQL& "                      (dbo.Recordings.RecordingTitle = '"&searchWord&"') OR"
SQL = SQL& "                     (dbo.Tracks.TrackTitle = '"&searchWord&"')
'   ***** wrapped your double quotes in single quote and wrapped searchWord in &

Open in new window

0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
Scott

Have it on one line for now

It gets past that line but getting this error now

Microsoft VBScript runtime error '800a01a8'

Object required: 'rsResults'

/music2.asp, line 12


I will try your second with on multiple lines now

Any thoughts on the above error

You can see it by going to my site
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
At least look at the first link I posted on how to create a recordset.
0
 
LVL 23

Author Comment

by:Thomas Grassi
Comment Utility
No not creating a record set

Do not know what I would need that for
0
 
LVL 23

Author Closing Comment

by:Thomas Grassi
Comment Utility
Dave

Closing this out we got past the sql select.

Going to open a new question for the error I am getting
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Read about why website design really matters in today's demanding market.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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