SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2000
    Location
    California, USA
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Overview
    I am creating a website that will be a collection of compositions (ie poems, articles, etc.) I want to reside these informations to a database. How can I export them to html/asp? I want to be able for my viewers to search and see them in order of submission date.

    I will create a database that will look like this in columns:
    Title | Poem |Date of submission |Author|Name (if diff. from author)| Email

    Then it should show on my html/asp 1 page like this:

    Poem's Title
    Poem's body
    Date submitted | Author | Name | E-mail

    If there is a better way of doing this, please let me know.

    Thank you so much in advance for your response(s).


  2. #2
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    If your database isn't too large then DBMan (http://www.gossamer-threads.com) might work for you. If you post in their support forum they can show you how to take an MS Access database and export it there.

    It's Perl based, though, so it can only handle 1MB worth of text.



    ------------------
    Chris - TWT Commish
    http://www.warningtrack.net
    "Baseball - Baked Fresh Daily"

  3. #3
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Go to www.4guysfromrolla.com. They have lots of information on ASP and databases. I am working on a series of tutorials with the help of some other ASP people here in the forums. The tutorials will be posted here on Sitepoint and will cover building an application very similar to what you need. Keep an eye out for the tutorials here.


    I have tried DBMan and found it very lacking in a lot of areas. Especially the fact that it isn't relational though you should be able to build joins in the MySQL version of the script.



    ------------------
    Wayne Luke - Sitepoint Moderator/Internet Media Developer
    Digital Magician Studios - Making Magic with Web Applications
    wluke@digitalmagician.com

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2000
    Location
    California, USA
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your responses. I go ahead and check both of them out and compare. If there's a simplest way of doing this, please let me know. Thanks!

  5. #5
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I am not a big fan of MS-FrontPage but it does have an option where you can publish an MS-Access database and create dynamic pages. Access also has built in functions to export objects as ASP.

    ------------------
    Wayne Luke - Sitepoint Moderator/Internet Media Developer
    Digital Magician Studios - Making Magic with Web Applications
    wluke@digitalmagician.com

  6. #6
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Assuming you're using Access2000, do this:

    Open the database
    Open File->Export
    Choose Active Server Pages as the format
    Then follow the wizard

    ------------------
    Who the heck is Gen. Failure and why does he want to read my hard drive?
    Soon to come: Aspology.com

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2000
    Location
    California, USA
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you guys! I will probably use either of those wizards since it sounds simple to do. So, this means I have to upload to those asp pages to my virtual pages everytime I receive new submissions? hmmm.....I really do not know where to start. I think there are alot more efficient ways of doing this.

  8. #8
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The access 2000 ASP wizard doesn't always work. Actually its never worked for me, I have it make the page then I have to go through and fix all the errors much like I would in frontpage.

    Well use Access though anyways. If you can design a query that works in access it can be implemented on the web.

    Well I feel like being nice so I'll do it for you. If your database is using 1 table called main and its System DNS (talk to your hosting provider) is named poetry then this is what your asp page would look like:

    <HTML>
    <HEAD>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
    <TITLE>Poetry</TITLE>
    </HEAD>
    <BODY>
    <%
    Param = Request.QueryString("Param")
    Data = Request.QueryString("Data")
    %>
    <%
    If IsObject(Session("poetry_conn")) Then
    Set conn = Session("poetry_conn")
    Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "poetry","",""
    Set Session("poetry_conn") = conn
    End If
    %>
    <%

    sql = "SELECT [Main].[Poem], [Main].[Author], [Main].[Email], [Main].[Date], [Main].[Title], [Main].[Name] FROM Main WHERE ( (([Main].[Title]) like '" & Request.QueryString("title") & "')"
    If cstr(Param) <> "" And cstr(Data) <> "" Then
    sql = sql & " And [" & cstr(Param) & "] = " & cstr(Data)
    End If
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
    %>
    <center>
    <table width = "400">
    <tr><td>

    <h2><%=Server.HTMLEncode(rs.Fields("title").Value)%></h2>
    <p><%=Server.HTMLEncode(rs.Fields("poem").Value)%></p>
    </td>
    </tr>
    </table>
    <table>
    <tr>
    <td>
    <%=Server.HTMLEncode(rs.Fields("date").Value)%></td><td><%=Server.HTMLEncode(rs.Fields("author").Value)%></td><td><%=Server.HTMLEncode(rs.Fie lds("name").Value)%></td><td><%=Server.HTMLEncode(rs.Fields("email").Value)%></td></tr></table>



    </BODY>
    </HTML>


    what that would do is take input from a search page

    <form method = "get" action = "poetry.asp">
    <input type = "text" size = 20 name = "title">
    <input type = "submit" value = "Get it">

    And it would display a poem that matches the title.

    Well that should get you started. ASP is pretty self explanatory just look at what I did and figure it out for different queries and whatnot.

    Chris


  9. #9
    SitePoint Enthusiast
    Join Date
    May 2000
    Location
    California, USA
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are so nice Chris. Thanks for your help. I'll go check out your scripts tonight. Once again, thank!

  10. #10
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just a little fyi. The key to the whole page lies in the SQL. Specifically whats after the WHERE statement.

    you can use the like argument as I did to make more criteria or different criteria

    one note:

    like '" & request......

    notice the single quote before the double quote, this is important the query will not work without it.

    also if you wanted a wildcar it would be like this:

    like '%" & request...... "%'

    this will search whatever field you're searching for the criteria specified but lets say you're searching for the word "love"

    this result would return "I love you" "Love left me" "Love died tonight" etc etc etc whereas without the % it would only return something if the only value was "love" % is the wildcard more or less.

    What I made for you will display 1 poem. If you want to display a table like "search results..." it would be different and would require a little more code which I can show you if you email me.

    Chris

  11. #11
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Instead of depending on a host provider to set up a DSN for you, you can access the database directly through ADO and OLE-DB. This removes the ODBC layer and makes your database connection faster. (If that was over your head you'll just have to trust me for now.)
    For your connection string you can use:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    Const poetryDSN = "DBQ=D:\path\to\data\base.mdb;Driver={Microsoft Access Driver (*.mdb)}" &_
    ";DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;" &_
    "MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;" &_
    "SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
    Set DBConn = Server.CreateObject("ADODB.Connection")
    DBConn.Open poetryDSN
    [/code]

    That is the same connection string that would be created when your host sets up your DSN.


    The SQL string could use a little fine tuning as well. Wouldn't it be easier to say:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    &lt;%
    sql = "SELECT Poem, Author, Email, Date, Title, Name FROM Main " &_
    "WHERE Title like '" & Request.QueryString("title") & "'"
    If cstr(Param) &lt;&gt; "" And cstr(Data) &lt;&gt; "" Then
    sql = sql & " And " & cstr(Param) & "=" & cstr(Data)
    End If
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
    %&gt;
    [/code]
    Or You could use the following if your going to pull all fields from the table:
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    &lt;%
    sql = "SELECT * FROM Main WHERE Title like '" & Request.QueryString("title") & "'"
    If cstr(Param) &lt;&gt; "" And cstr(Data) &lt;&gt; "" Then
    sql = sql & " And " & cstr(Param) & "=" & cstr(Data)
    End If
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
    %&gt;
    [/code]
    MS Access likes you to put [] around its field names but they are totally unneccesary in a SQL statement. Without the brackets changing this simple statement from Access to SQL Server, Oracle or any other DBMS only requires changing the connection string.

    Since your only using one table you don't have to qualify the table name for each individual field. Both of the above statements will be quicker in execution than the original.

    Don't mean to step on your toes Chris, but in rendering a webpage every millisecond counts. If you can optimize both server and client side processes then everyone wins.

    ------------------
    Wayne Luke - Sitepoint Moderator/Internet Media Developer
    Digital Magician Studios - Making Magic with Web Applications
    wluke@digitalmagician.com

  12. #12
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess I just like to be complete.

    But to the original poster like I said I'll help you if you need it.

    Chris

  13. #13
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Both statements above are just as complete only easier to read.

    ------------------
    Wayne Luke - Sitepoint Moderator/Internet Media Developer
    Digital Magician Studios - Making Magic with Web Applications
    wluke@digitalmagician.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •