ASP & SQL help

Discussion in 'ASP General' started by *** HAWK, Dec 3, 2003.

  1. *** HAWK

    *** HAWK Guest

    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 :)
     
    *** HAWK, Dec 3, 2003
    #1
    1. Advertising

  2. *** HAWK

    Tom B Guest

    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" <> wrote in message
    news:3fcdd41b$0$23812$...
    > 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, Dec 3, 2003
    #2
    1. Advertising

  3. Hi,

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

    Bhaskardeep Khaund
     
    Bhaskardeep Khaund, Dec 3, 2003
    #3
  4. *** HAWK

    Bob Barrows Guest

    *** 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.
     
    Bob Barrows, Dec 3, 2003
    #4
  5. *** HAWK

    Jeff Cochran Guest

    On Wed, 3 Dec 2003 12:16:41 -0000, "*** 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.
    >
    >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
     
    Jeff Cochran, Dec 3, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UmV6YQ==?=
    Replies:
    3
    Views:
    18,017
    Carlos Barini
    Jun 7, 2004
  2. =?Utf-8?B?UmV6YQ==?=
    Replies:
    2
    Views:
    1,827
    bruce barker
    Jun 8, 2004
  3. =?Utf-8?B?dmE=?=
    Replies:
    4
    Views:
    2,863
    =?Utf-8?B?dmE=?=
    Feb 22, 2006
  4. ecoolone
    Replies:
    0
    Views:
    788
    ecoolone
    Jan 3, 2008
  5. David
    Replies:
    4
    Views:
    166
    Bob Barrows [MVP]
    Sep 28, 2006
Loading...

Share This Page