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

Hi,

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

Bhaskardeep Khaund

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


"*** HAWK" <nosharemailHAWK@INVALID.mail.com.Invalid> wrote in message
news:3fcdd41b$0$23812$cc9e4d1f@news.dial.pipex.com ...[color=blue]
> 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[/color]
a[color=blue]
> 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[/color]
DSN-Less[color=blue]
> 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[/color]
must[color=blue]
> 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 &[/color]
maximum)[color=blue]
> 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[/color]
by[color=blue]
> the visitor
> 6 - Create an login that will enable only 'admin users' to obtain a list[/color]
of[color=blue]
> 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[/color]
database[color=blue]
> 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 :-)
>
>[/color]

*** HAWK wrote:[color=blue]
> 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.
>[/color]

<snip>[color=blue]
> Fields in 'tblPasswords' are:
> "Username" "Password"[/color]

"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

<snip>[color=blue]
> 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)[/color]

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

<snip>[color=blue]
> The main priorities of the site are as follows:[/color]
<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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

On Wed, 3 Dec 2003 12:16:41 -0000, "*** HAWK"
<nosharemailHAWK@INVALID.mail.com.Invalid> wrote:
[color=blue]
>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[/color]

SELECT * FROM tblMusicAlbums ORDER BY Artist
[color=blue]
>2 - Obtain a list of CD's by a certain artist listed by release date[/color]

{Get ArtistName from form}
SELECT * FROM tblMusicAlbums WHERE Artist = 'ArtistName' ORDER BY
ReleaseDate
[color=blue]
>3 - Obtain a list of CD's whose price is within a range (minimum & maximum)
>as specified by the user.[/color]

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.
[color=blue]
>4 - Obtain a list of titles of the tracks on any selected CD[/color]

{GetAlbum from form}
SELECT * FROM tblMusicTracks WHERE AlbumID = 'Album'
[color=blue]
>5 - CD's whose overall CD title or track titles contains a keyword input by
>the visitor[/color]

{Get SearchTerm from form}
SELECT AlbumID FROM tblMusicAlbums, tblMusicTracks WHERE
tblMusicAlbums.AlbumTitle LIKE 'SearchTerm' OR
tblMusicTracks.TrackTitle LIKE 'SearchTerm'
[color=blue]
>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)[/color]

You don't provide a mechanism to track these hits, so you can't do
this until you create one.
[color=blue]
>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 :-)[/color]

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

"*** HAWK" <nosharemailHAWK@INVALID.mail.com.Invalid> wrote in message
news:3fcdd41b$0$23812$cc9e4d1f@news.dial.pipex.com ...[color=blue]
> Fields in 'tblMusicAlbums' are: "AlbumID" "AlbumTitle" "Artist"
> "MusicCategoryID" "Label" "ReleaseDate" "NumberOfTracks"
>
> Fields in 'tblMusicTracks' are:
> "TrackID" "AlbumID" "TrackNumber" "TrackTitle"[/color]

My comment is slightly off topic... but I thought I would mention it. In
the tables above, you have Artist associated with an album. Have you
considered how something like a compilation CD might be represented? For
example, a CD like "Now That's What I Call Music! 14"? In my opinion, you
are correct to have an Artist associated with a CD, but you should also have
an Artist associated with a track. That way, you could have "Various
Artists" associated with the CD, and each individual artist associated with
their songs. Just something to consider. :)

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

You really should avoid DSN connections. Why would you want to use DSN on
your completed project?

Regards,
Peter Foti