SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Nov 2002
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Help On Ms Access

    guys i need real help as i don't know this issue is with W2K or what.

    My client face a problem after he transfer his website on my server.

    Simple ASP script fetching few records from Access database seems to take ages. (after sometime if you click stop then you can see 1 or 2 records displayed on the browser)
    http://www.puskar.biz/demo/test.asp

    Though eCommerce program based on MS Access is working file. http://www.puskar.biz/demo/

    Anyone have idea what may be wrong? The above script was working fine with previous host under IIS4 but this server is IIS5.

    database contains 1 table , 227 records and 22 records are fetched from the query which looks quite normal.

    You can have a look at the code & db at http://www.puskar.biz/demo/testdb.zip

    The Server is Pentium 4 server, RAM 1GB, few sites and not much load.

    Thanks in Advance!

  2. #2
    SitePoint Member
    Join Date
    Nov 2002
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the code: (included in the .zip file)

    Here's the Code:

    <%@ Language=VBScript %>
    <% Option Explicit %>
    <!-- #include file="adovbs.inc"-->
    <html>
    <head>
    <title>Go with Science P4</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body bgcolor="#FFFFFF" text="#330099" link="#6666FF" leftmargin="20" topmargin="20" marginwidth="20" marginheight="20" vlink="#6699FF" alink="#6699FF">
    <% Dim icount
    Dim objConn
    Set objConn = Server.CreateObject("ADODB.Connection")

    Dim MdbFilePath
    MdbFilePath = Server.MapPath("brusci_db.mdb")
    objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="& MdbFilePath &";"
    objConn.Open

    Dim strSQL1
    strSQL1 = "SELECT * FROM bruscip4 WHERE Unit = 1 ORDER BY Type ASC, Resources ASC"

    Dim objRS1
    set objRS1 = Server.CreateObject("ADODB.Recordset")

    Dim doctype
    Dim nametype1, nametype2

    objRS1.Open strSQL1,objConn,3,1,&H0001

    %>
    <a name="top"></a>
    <table width="490" cellspacing="0" cellpadding="0">
    <tr>
    <td colspan="2"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><b><u>THEME
    1: VARIETY AND CLASSIFICATION</u></b></font></td>
    </tr>
    </table>
    <p><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b><font color="#FF0000">Unit
    1: Living and Non-living Things</font></b></font></p>
    <table width="490" border="0" cellspacing="0" cellpadding="3">
    <%
    objRS1.MoveFirst
    do while not (objRS1.EOF)
    icount = icount + 1
    Response.Write(icount)

    %>
    <tr>
    <td valign="top" align="center" height="42" rowspan="2">&nbsp;
    </td>
    <td valign="top" align="left" height="16"><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#6666FF"><b>
    <%
    Response.Write(icount)
    %>
    <a href= "<%=nametype2%><%=objRS1("Link")%>" target="_blank"><%=objRS1("Resources")%></a></b></font></td>
    </tr>
    <tr>
    <td width="578" valign="top" align="left" height="15"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=objRS1("Description")%>&nbsp;<%=objRS1("Document")%></font></td>
    </tr>
    <tr>
    <td valign="top" align="right" height="13">&nbsp;</td>
    <td width="578" valign="top" align="left" height="13">&nbsp;</td>
    </tr>
    <%
    objRS1.MoveNext
    Loop
    %>
    <tr align="right">
    <td colspan="6" valign="top" bgcolor="#FFFFFF"><font face="Verdana, Arial, Helvetica, sans-serif" size="1"><b><a href= "../../html/brusci_pri/bruscip4.html">Back</a>&nbsp;|&nbsp;<a href= "#top">Back
    to top</a></b></font></td>
    </tr>
    </table>
    <br>
    <% objRS1.Close
    Set objRS1 = Nothing
    objConn.Close
    Set objConn = Nothing
    %>
    </body>
    </html>

    Thanks again for any help....

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    There are a couple problems, most likely due to the database. Is it still in access97 format, or is it in Access2000 format. Moving it over to 2000 format will help greatly.

    I also found some coding changes which you can make which will help out the speed considerably. I took the liberty of making the changes and re-posting it. You can find all my changes with comments by them on what changes I made and why I made them.

    Here's the code:
    Code:
    <%@ Language=VBScript %>
    <% Option Explicit %>
    <!-- DEM -- Got rid of adovbs.inc because you weren't using it at all -->
    <html>
    <head>
    <title>Go with Science P4</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body bgcolor="#FFFFFF" text="#330099" link="#6666FF" leftmargin="20" topmargin="20" marginwidth="20" marginheight="20" vlink="#6699FF" alink="#6699FF">
    <% Dim icount
    Dim objConn
    Set objConn = Server.CreateObject("ADODB.Connection" )
    ' DEM -- Changed to OLEDB connection because it's faster.  Even better if you convert the acccess97
    '   database over to 2000.
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& Server.MapPath("brusci_db.mdb") &";"
    objConn.Open
    Dim strSQL1
    ' DEM -- Changed select * to select fields being used.  This approach is faster even if you
    '   are selecting all the fields anyways.
    strSQL1 = "SELECT Link, Resources, Document, Description " & _
    	"  FROM bruscip4 WHERE Unit = 1 ORDER BY Type ASC, Resources ASC"
    Dim objRS1
    set objRS1 = Server.CreateObject("ADODB.Recordset" )
    Dim doctype
    Dim nametype1, nametype2
    ' DEM -- fields added for changes made below...
    Dim arrRecSet, RecCount, i, strLink, strResources, strDocument, strDescription
    objRS1.Open strSQL1,objConn,3,1,&H0001
    ' DEM -- added EOF/BOF check and moved to getrows.  Much faster...
    If objRS1.EOF or objRS1.BOF then
     RecCount = -1
    Else
     arrRecSet = objRS1.GetRows(-1) : RecCount = UBound(arrRecSet, 2)
    End If
    ' Using getrows, connection and recordset can be closed immediately.
    objRS1.Close : Set objRS1 = Nothing
    objConn.Close : Set objConn = Nothing
    %>
    <a name="top"></a> 
    <table width="490" cellspacing="0" cellpadding="0">
    <tr> 
    <td colspan="2"><font face="Verdana, Arial, Helvetica, sans-serif" size="3"><b><u>THEME 
    1: VARIETY AND CLASSIFICATION</u></b></font></td>
    </tr>
    </table>
    <p><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b><font color="#FF0000">Unit 
    1: Living and Non-living Things</font></b></font></p>
    <%
    ' DEM -- made changes to accomodate using getrows array...
    if RecCount < 0 then
    %>
     <p>No Records found for this unit</p>
    <%
    Else
    %>
    <table width="490" border="0" cellspacing="0" cellpadding="3">
    <%
    ' DEM -- GetRows moves everything into an array, so now you loop through the array instead of the recordset.
    For i = 0 to RecCount
     ' DEM -- Move array fields into local variables to make code easier to read later on
     strLink   = arrRecSet(0, i)
     strResources = arrRecSet(1, i)
     strDocument  = arrRecSet(2, i)
     strDescription = arrRecSet(3, i)
     icount = icount + 1 
    %>
     <tr> 
      <td valign="top" align="center" height="42" rowspan="2">&nbsp;</td>
      <td valign="top" align="left" height="16"><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#6666FF"><b>
    <%
       Response.Write(icount)
    %>
       <a href="" "<%=strLink %>" target="_blank"><%=strResources%></a></b></font></td>
     </tr>
     <tr> 
      <td width="578" valign="top" align="left" height="15"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=strDescription%>&nbsp;<%=strDocument%></font></td>
     </tr>
     <tr> 
      <td valign="top" align="right" height="13">&nbsp;</td>
      <td width="578" valign="top" align="left" height="13">&nbsp;</td>
     </tr>
    <% 
    Next
    %>
    <tr align="right"> 
    <td colspan="6" valign="top" bgcolor="#FFFFFF"><font face="Verdana, Arial, Helvetica, sans-serif" size="1"><b><a href="" "../../html/brusci_pri/bruscip4.html">Back</a>&nbsp;|&nbsp;<a href="" "#top">Back 
    to top</a></b></font></td>
    </tr>
    </table>
    <%
    End if ' DEM -- Closes out end if on reccount
    %>
    </body>
    </html>
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  4. #4
    SitePoint Member
    Join Date
    Nov 2002
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Dave,

    Thanks for your effort, i tried but still the same.
    http://www.puskar.biz/demo/test1.asp

    I think something is not behaving properly on the server, as same code is working fine on other server.
    Can't figure out where the things are going wrong.


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
  •