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

[Bhaskardeep Khaund]

Hi,

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

Bhaskardeep Khaund

[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
"*** HAWK" <no*************@INVALID.mail.com.Invalid> wrote in message
news:3f***********************@news.dial.pipex.com ...

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

[Bob Barrows]

*** HAWK wrote:

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.

<snip> 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

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

<snip> 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
--
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.

[Jeff Cochran]

On Wed, 3 Dec 2003 12:16:41 -0000, "*** HAWK"
<no*************@INVALID.mail.com.Invalid> wrote:

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

[Peter Foti]

"*** HAWK" <no*************@INVALID.mail.com.Invalid> wrote in message
news:3f***********************@news.dial.pipex.com ...

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

Fields in 'tblMusicTracks' are:
"TrackID" "AlbumID" "TrackNumber" "TrackTitle"
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. :)

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)

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

Regards,
Peter Foti