Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP & SQL help

Reply
Thread Tools

ASP & SQL help

 
 
*** HAWK
Guest
Posts: n/a
 
      12-03-2003
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


 
Reply With Quote
 
 
 
 
Tom B
Guest
Posts: n/a
 
      12-03-2003
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" <(E-Mail Removed)> wrote in message
news:3fcdd41b$0$23812$(E-Mail Removed) ...
> 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
>
>



 
Reply With Quote
 
 
 
 
Bhaskardeep Khaund
Guest
Posts: n/a
 
      12-03-2003
Hi,

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

Bhaskardeep Khaund
 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      12-03-2003
*** 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.


 
Reply With Quote
 
Jeff Cochran
Guest
Posts: n/a
 
      12-03-2003
On Wed, 3 Dec 2003 12:16:41 -0000, "*** HAWK"
<(E-Mail Removed)> 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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
Access SQL to ASP SQL not working ... need help please David ASP General 4 09-28-2006 02:23 PM
asp and sql statement in sql server db weiwei ASP General 3 09-22-2004 04:12 PM
How to read an SQL Server into a ASP page and then change, add, delete and write it back to SQL Server Belinda ASP General 4 06-11-2004 12:16 PM
ASP SQL - using variables in SQL select screen Ed Garcia ASP General 4 08-07-2003 07:41 PM



Advertisments