ASP & SQL help

[*** HAWK]

I previously posted but didnt have much response. I have written out what
exactly I am trying to do with my ASP site again but hopefully clearer.

I am making a site will emulate something like hmv.com or cdwow.com - a
website that allows users to view information of albums that are stored in a
seperate access database.

Ok. My database called 'cdshop.mdb' has 3 tables as follows:
1 - 'tblMusicAlbums'
2 - 'tblMusicTracks'
3 - 'tblPasswords' (not to concerned about this one for the time being)

Fields in 'tblMusicAlbums' are: "AlbumID" "AlbumTitle" "Artist"
"MusicCategoryID" "Label" "ReleaseDate" "NumberOfTracks"

Fields in 'tblMusicTracks' are:
"TrackID" "AlbumID" "TrackNumber" "TrackTitle"

Fields in 'tblPasswords' are:
"Username" "Password"

I am making an *.asp file that will connect to the database using a DSN-Less
connection at home (although this is for testing purposes at home - I will
need to alter this to a DSN connection once its completed)
I have both DSN & DSN-less connections working so this isn't a problem for
now.

The main priorities of the site are as follows:
Using a series of input fields /jump menus / submit buttons the visitor must
be able to do the following:
1 - Obtain a list of ALL CD's in the database listed A-Z by Artist
2 - Obtain a list of CD's by a certain artist listed by release date
3 - Obtain a list of CD's whose price is within a range (minimum & maximum)
as specified by the user.
4 - Obtain a list of titles of the tracks on any selected CD
5 - CD's whose overall CD title or track titles contains a keyword input by
the visitor
6 - Create an login that will enable only 'admin users' to obtain a list of
CD's in order of interest as measured by the number of equiries about each
CD retrieved in requirement 4 (eg no of hits per CD/Album)

The database contains 20+ albums (only small scale for this purpose is
needed). Using SQL to perform the actions as mentioned earlier. The database
is made in MS Access (*.mdb format)

Thanks. let me know if this isn't very clear. remember I am somewhat a
beginner to ASP so please be gentle with me

 

[Tom B]

Again, what's the problem?
It sounds like you have everything figured out. So I'm confused, you seem
to know how to connect to your database and I assume you know how to pull
out the data.

Are you having trouble creating the SQL statements? If so, use Access'
query designer to create a query, then switch to SQL view to have a peek at
how it wrote the statement.

Are you having trouble navigating the results? If so, look for any message
from Bob Barrows, it'll explain how to open a recordset, convert it to an
array and display it. I'm exagerrating, not all of Bob's posts are on this
topic, but he's a strong proponent of this approach.

How many pages are you creating? It looks like you should go this
route.....

Start page - has the search options available.
Search Results page - obviously returns the results based on the
type of search, with hyperlinks to the Details page
Details page - the tracks and other information of
the selected CD.

Admin Login page - linked to from the Start Page, this will
have your login logic in it
Admin Reports page - links to the reports you need.
Reports pages - I'd create a page for each report
unless they are going to be nearly identical


Start with the basics - get the search results page working. And post
SPECIFIC problems as they arise.

Tom
P.S. I only replied to the ASP general group

 

[Bhaskardeep Khaund]

Hi,

i understood ur site, but whats the problem you are facing???????

Bhaskardeep Khaund

 

[Bob Barrows]

I previously posted but didnt have much response. I have written out
what exactly I am trying to do with my ASP site again but hopefully
clearer.

Fields in 'tblPasswords' are:
"Username" "Password"

"Password" is a reserved keyword that should not be used for database object
names. If you can, rename this field. Otherwise you will be required to
surround it with brackets [] whenever you use it in a query run by ADO. Here
is the list of reserved words which you should avoid when naming your tables
and fields:
http://www.aspfaq.com/show.asp?id=2080

I am making an *.asp file that will connect to the database using a
DSN-Less connection at home (although this is for testing purposes at
home - I will need to alter this to a DSN connection once its
completed)

Why??? Don't use the obsolete ODBC connection! Use the native Jet OLEDB
provider. (www.able-consulting.com/ado_conn.htm)

The main priorities of the site are as follows:

<snip of well-thought-out design specs>

So what do you want from us? Do you want somebody to write this application
for you? Perhaps you should be hiring a consultant.

Bob Barrows

 

[Jeff Cochran]

I previously posted but didnt have much response. I have written out what
exactly I am trying to do with my ASP site again but hopefully clearer.

I am making a site will emulate something like hmv.com or cdwow.com - a
website that allows users to view information of albums that are stored in a
seperate access database.

Ok. My database called 'cdshop.mdb' has 3 tables as follows:
1 - 'tblMusicAlbums'
2 - 'tblMusicTracks'
3 - 'tblPasswords' (not to concerned about this one for the time being)

Fields in 'tblMusicAlbums' are: "AlbumID" "AlbumTitle" "Artist"
"MusicCategoryID" "Label" "ReleaseDate" "NumberOfTracks"

Fields in 'tblMusicTracks' are:
"TrackID" "AlbumID" "TrackNumber" "TrackTitle"

Fields in 'tblPasswords' are:
"Username" "Password"

I am making an *.asp file that will connect to the database using a DSN-Less
connection at home (although this is for testing purposes at home - I will
need to alter this to a DSN connection once its completed)
I have both DSN & DSN-less connections working so this isn't a problem for
now.

The main priorities of the site are as follows:
Using a series of input fields /jump menus / submit buttons the visitor must
be able to do the following:
1 - Obtain a list of ALL CD's in the database listed A-Z by Artist

SELECT * FROM tblMusicAlbums ORDER BY Artist

2 - Obtain a list of CD's by a certain artist listed by release date

{Get ArtistName from form}
SELECT * FROM tblMusicAlbums WHERE Artist = 'ArtistName' ORDER BY
ReleaseDate

3 - Obtain a list of CD's whose price is within a range (minimum & maximum)
as specified by the user.

No can do. You don't have a field for album price. If you had one,
you could use a BETWEEN in your WHERE clause to get what you want.

4 - Obtain a list of titles of the tracks on any selected CD

{GetAlbum from form}
SELECT * FROM tblMusicTracks WHERE AlbumID = 'Album'

5 - CD's whose overall CD title or track titles contains a keyword input by
the visitor

{Get SearchTerm from form}
SELECT AlbumID FROM tblMusicAlbums, tblMusicTracks WHERE
tblMusicAlbums.AlbumTitle LIKE 'SearchTerm' OR
tblMusicTracks.TrackTitle LIKE 'SearchTerm'

6 - Create an login that will enable only 'admin users' to obtain a list of
CD's in order of interest as measured by the number of equiries about each
CD retrieved in requirement 4 (eg no of hits per CD/Album)

You don't provide a mechanism to track these hits, so you can't do
this until you create one.

The database contains 20+ albums (only small scale for this purpose is
needed). Using SQL to perform the actions as mentioned earlier. The database
is made in MS Access (*.mdb format)

Thanks. let me know if this isn't very clear. remember I am somewhat a
beginner to ASP so please be gentle with me

Well, start with the various ASP database tutorials (use Google or
aspin.com to find them) and the simple queries. Check sqlcourse.com
for a primer on query language. Other than the JOIN in #5 above the
query is pretty simple and is basically selecting what you display.
Note that this particular query language *should* work in Access, but
you can always create a query in the Access GUI that does what you
need, then look at the query in SQL view and use that query syntax
directly in your ASP code.

Jeff