none
Windows 7: data source not found error when opening connection from a class RRS feed

  • Question

  • Not really sure which forum to post this in, but here goes.

    I am trying to run an application that I developed in ASP classic several years ago. It works fine on XP and Windows 7. Hoewever, I have one page that dynamically builds a list using a class. When I attempt to establish a connection to the database from within the class, I get the error: Data source name not found and no default driver specified. The class is an include file on the page that calls it.

    The connection string is: Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\\inetpub\wwwroot\...........;Uid=Admin;Pwd=;

     The class script is included below:

    <%
    Class clsDataGridCSS

     

    '-----------------------------------------------------------
    '--- Declare all class variables to be used
    '-----------------------------------------------------------
    private m_strSQL  '--- The SQL used to generate the report.
    private m_strConn  '--- The connection string to talk to the database.
    private m_strRowColor1  '--- First of alternating row colors in grid.
    private m_strRowColor2  '--- Second of alternating row colors in grid.
    private m_strMode  '--- Indicates whether "Add Record" will appear.
    private m_strProcessPage '--- If Edit mode, this is the page that will be called
        ' when "Add Record" is clicked.
    private m_RowsPerPage  
    private m_strTitle  '--- Title that will display at top of grid.
    private m_strRSName  '--- Name of saved recordset.
    private m_strFindFields  '--- Column names for which to enable finding
    private m_strReload  'whether or not to rehit db, similar to request("Reload")
    '-----------------------------------------------------------

    '-----------------------------------------------------------
    '--- Declare and define class properties
    '-----------------------------------------------------------
    Public Property Get SQL
     SQL = m_strSQL
    End Property
    Public Property Let SQL(strSQL)
     m_strSQL = strSQL
    End Property
    Public Property Get Conn
     Conn = m_strConn
    End Property
    Public Property Let Conn(strConn)
     m_strConn = strConn
    End Property
    Public Property Get RowColor1
     If IsNull(m_strRowColor1) OR Len(m_strRowColor1) = 0 Then
      RowColor1 =  "#ffffff"
     Else
      RowColor1 = m_strRowColor1
     End If
    End Property
    Public Property Let RowColor1(strRowColor1)
     m_strRowColor1 = strRowColor1
    End Property
    Public Property Get RowColor2
     If IsNull(m_strRowColor2) OR Len(m_strRowColor2) = 0 Then
      RowColor2 =  "#E6E6E6"
     Else
      RowColor2 = m_strRowColor2
     End If
    End Property
    Public Property Let RowColor2(strRowColor2)
     m_strRowColor2 = strRowColor2
    End Property
    Public Property Get Mode
     If IsNull(m_strMode) OR Len(m_strMode) = 0 Then
      Mode =  "View"
     Else
      Mode = m_strMode
     End If
    End Property
    Public Property Let Mode(strMode)
     If strMode <> "View" AND strMode <> "Edit" Then
      Response.Write("Invalid mode - only View or Edit allowed<br>")
      Response.End
     Else
      m_strMode = strMode
     End If
    End Property
    Public Property Get ProcessPage
     ProcessPage = m_strProcessPage
    End Property
    Public Property Let ProcessPage(strProcessPage)
     m_strProcessPage = strProcessPage
    End Property
    Public Property Get RowsPerPage
     If IsNull(m_RowsPerPage) OR Len(m_RowsPerPage) = 0 Then
      RowsPerPage =  20
     Else
      RowsPerPage = m_RowsPerPage
     End If
    End Property
    Public Property Let RowsPerPage(strRowsPerPage)
     m_RowsPerPage = strRowsPerPage
    End Property
    Public Property Get Title
     If IsNull(m_strTitle) OR Len(m_strTitle) = 0 Then
      Title =  "Data Grid"
     Else
      Title = m_strTitle
     End If
    End Property
    Public Property Let Title(strTitle)
     m_strTitle = strTitle
    End Property
    Public Property Get RSName
     If IsNull(m_strRSName) OR Len(m_strRSName) = 0 Then
      RSName =  "Grid"
     Else
      RSName = m_strRSName
     End If
    End Property
    Public Property Let RSName(strRSName)
     m_strRSName = strRSName
    End Property
    Public Property Get FindFields
     FindFields = m_strFindFields
    End Property
    Public Property Let FindFields(strFindFields)
     m_strFindFields = strFindFields
    End Property
    Public Property Get Reload
     If IsNull(m_strReload) OR Len(m_strReload) = 0 Then
      Reload =  "N"
     Else
      Reload = m_strReload
     End If
    End Property
    Public Property Let Reload(strReload)
     m_strReload = strReload
    End Property

    '-----------------------------------------------------------
    '--- Declare and define class methods
    '-----------------------------------------------------------
    Sub ShowDataGrid

    Dim intPageNum  '--- This is used when the list is returned to from
       ' another page and we want to return to the page
       ' that the user left.
    Dim objConn  '--- The ADO Connection to the database.
    Dim objRS  '--- The ADO Recordset that stores the data.
    Dim intAbs  '--- Used to save the absolute position in the recordset.
    Dim intCurrentPage '--- The current page.
    Dim intFindCol  '--- The column to do the find in.
    Dim intPageSize  '--- The amount of pages.
    Dim intRow  '--- A loop counter variable.
    Dim intCol  '--- A loop counter variable.
    Dim i   '--- A loop counter variable.
    Dim intPos  '--- Position of a square bracket within a string.
    Dim intDisplayRows '--- Display this many rows on a page.
    Dim strSort  '--- The column to sort by.
    Dim strSortDir  '--- The direction of the sort.
    Dim strLastSort  '--- The column we sorted by last time.
    Dim strLastSortDir '--- The direction of the sort we used last time.
    Dim strColor  '--- Used to store the color of the report rows.
    Dim strFind  '--- The string to pass to the recordset find method
    Dim boolFind  '--- Boolean value indicating whether a find was requested
    Dim boolFound  '--- Boolean value indicating whether a string was found
    Dim strFindFields '--- Array holding field names for which to allow finds
    Dim strCurrentPage '--- Name of this ASP page.Const adUseClient = 3

    '--- Define ADO constants
    Const adUseClient = 3
    Const adOpenDynamic = 2
    Const adAsyncFetchNonBlocking = &H00000040
    Const adSearchForward = 1
    Const adChar = 300
    Const adVarChar = 200

    '--- Put array of field names that find will be allowed on into
    ' local array
    If IsArray(FindFields) Then
     strFindFields = FindFields
    End If

    '-----------------------------------------------------------
    '--- Make sure SQL and Conn properties have
    ' been set if the disconnected recordset doesn't
    ' exist.
    '-----------------------------------------------------------
    If NOT IsObject(Session(RSName)) AND _
    (IsNull(SQL) OR Len(SQL) = 0) Then
     Response.Write("You must set the SQL property if not passing a recordset<br>")
     Response.End
    End If
    If NOT IsObject(Session(RSName)) AND _
    (IsNull(Conn) OR Len(Conn) = 0) Then
     Response.Write("You must set the Conn property if not passing a recordset<br>")
     Response.End
    End If

    '-----------------------------------------------------------
    '--- If mode is Edit, make sure ProcessPage
    ' property has been set.
    '-----------------------------------------------------------
    If Mode = "Edit" AND (IsNull(ProcessPage) OR Len(ProcessPage) = 0) Then
     Response.Write("For Edit mode, you must set the ProcessPage property<br>")
     Response.End
    End If

    '-----------------------------------------------------------
    '--- Get the name of the current page.
    ' This will be used by the form to resubmit to itself.
    '-----------------------------------------------------------
    strCurrentPage = Request.ServerVariables("PATH_INFO")
    If InStr(1, strCurrentPage, "/") > 0 Then _
     strCurrentPage = Right(strCurrentPage, Len(strCurrentPage) - InStrRev(strCurrentPage, "/"))

    '-----------------------------------------------------------
    '--- Create the disconnected recordset, or retrieve it
    '--- from the session.
    '-----------------------------------------------------------
    if IsObject(Session(RSName)) and (Reload <> "Y") then
     '--- Retrieve the disconnected recordset.
     set objRS = Session(RSName)
    else
     '--- Create the ADO objects
     Set objConn = Server.CreateObject("ADODB.Connection")
     Set objRS = Server.CreateObject("ADODB.Recordset")
     Set Session(RSName) = objRS
     
     '--- Connect to the database using OLE DB
     objConn.Open Conn

     '--- Use a client side cursor so we can sort later
     '--- and so we can create a disconnected recordset.
     objRS.CursorLocation = adUseClient
     objRS.Source = SQL
     objRS.CursorType = adOpenDynamic
     objRS.Properties("Initial Fetch Size") = 800
     set objRS.ActiveConnection = objConn
     
     '--- Open the report's recordset in asynchronous mode
     ' so that an initial subset of records can be returned while
     ' the rest continue to download
     objRS.Open ,,,,adAsyncFetchNonBlocking
     
     '--- Disconnect the recordset from the connection.
     set objRS.ActiveConnection = nothing
     
     '--- Close the connection to the database.
     objConn.Close
    end if
    '-----------------------------------------------------------

    '-----------------------------------------------------------
    '--- Sorting section
    '-----------------------------------------------------------
    '--- If the user requested the recordset to be re-sorted...
    If Trim(Request("SortBy")) <> "" and Trim(Request("Resort")) <> "" Then
     '--- Retrieve the field the user chose to sort by.
     strSort = Request("SortBy")
     '--- Get the field (and direction) we used to
     '--- sort the recordset the last time.
     '--- The column name is enclosed within brackets,
     '--- in case it has multiple words.
     intPos = Instr(2, objRS.Sort, "]")
     If intPos > 0 Then
      strLastSort = Left(objRS.Sort, intPos)
      strLastSortDir = Trim(Mid(objRS.Sort, intPos + 2))
     End If
     '--- Check if the sorting field has changed.
     If Trim(strSort) <> Trim(strLastSort) then
      '--- The sorting field has changed,
      '--- so we will sort in ascending order.
      strSortDir = "asc"
     Else
      '--- The sorting field is the same.
      '--- Now we determine which sort order direction
      '--- we used last time (so we can switch it).
      If strLastSortDir = "asc" Then
       strSortDir = "desc"
      Else
       strSortDir = "asc"
      End If  
     End If
     objRS.Sort = strSort & " " & strSortDir
    End If
    '-----------------------------------------------------------

    '-----------------------------------------------------------
    '--- Paging section
    '-----------------------------------------------------------
    '--- Set the default page size
    intPageSize = RowsPerPage
    '--- Determine if the user entered a page size.
    If Trim(request("txtPageSize")) <> "" then
     '--- Set the page size to what the user chose.
     intPageSize = request("txtPageSize")
    End If
    '--- See if a page number was passed in on the URL.
    intPageNum = Trim(Request.QueryString("PageNum"))
    '--- Check if we are not showing all records (then we are paging).
    If (Trim(Request("lstPages")) <> "" or intPageNum <> "") and Trim(Request("AllRecs")) = "" then
     '--- Set the current page to what was passed in on the
     ' URL or to what the user chose.
     If intPageNum <> "" Then ' passed in on URL
      intCurrentPage = intPageNum
     Else ' page number selected by user
      intCurrentPage = Request("lstPages")
     End If
    Else
     '--- Since we are showing all records,
     '--- set the current page to the first page.
     intCurrentPage = 1
    End If
    '--- As long as there are records...
    If Not (objRS.BOF and objRS.EOF) Then
     '--- Set the page size
     objRS.PageSize = intPageSize
     '--- If the page we were on is no longer valid,
     '--- set the current page to the last page available.
     If CInt(intCurrentPage) > CInt(objRS.PageCount) Then
      intCurrentPage = objRS.PageCount
      'session("PageNum") = intCurrentPage
     End If
    End If
    session("PageNum") = intCurrentPage
    '-----------------------------------------------------------

    '-----------------------------------------------------------
    '---  Find section
    '-----------------------------------------------------------
    '--- If the user requested to find a string...
    boolFind = False
    '--- Find will only work if we are paging (not showing all records)
    If Trim(Request("FindCol")) <> "" AND _
       Trim(Request("FindIt")) <> "" AND _
       Trim(Request("find" & Request("FindCol"))) <> "" AND _
       (objRS.RecordCount > objRS.PageSize) Then
     boolFind = True
     '--- Retrieve the field the user chose to do the find on.
     intFindCol = CInt(Request("FindCol"))
     '--- Build the string to pass to the recordset Find method
     strFind = "[" & objRS(intFindCol).Name & "] LIKE '" & _
         Request("find" & intFindCol) & "%'"
        '--- Save the current record position in case the string is not found
        intAbs = objRS.AbsolutePosition
     '--- Find the string
        objRS.Find strFind, 1 , adSearchForward
        '--- If string was found, we are positioned on the first record
        ' containing that string.  We need to set the page # it is on.
        ' If the string was not found, the AbsolutePosition property
        ' is negative and needs to be reset to its previous value.
        boolFound = False
        If objRS.AbsolutePosition < 1 Then
      objRS.AbsolutePosition = intAbs
     Else
      boolFound = True
      '--- Set the page number to the page that the found record
      ' would be on so that subsequent paging will work properly
      intCurrentPage = Int(objRS.AbsolutePosition / objRS.PageSize)
      If objRS.AbsolutePosition MOD objRS.PageSize <> 0 Then
       intCurrentPage = intCurrentPage + 1
      End If
      '--- Save the position of the found record
      intAbs = objRS.AbsolutePosition
      session("PageNum") = intCurrentPage   
     End If
    Else ' no find requested, reset recordset position to first record of page
     If Not (objRS.BOF and objRS.EOF) Then _
      objRS.AbsolutePage = intCurrentPage
    End If
    '-----------------------------------------------------------
    %>
    <script language="javascript">
    <% If boolFind AND NOT boolFound Then %>
    window.status='** String Not Found **'
    <% End If %>
    function Refresh()
    // Refresh the recordset by submitting the form.
    {
     document.frmReport.submit();
    }

    function MoveToPage(PageNumber)
    {
    // Select the page number to go to, then submit the page.
     if (PageNumber != -1)
      {document.frmReport.lstPages[PageNumber].selected = true;}
     else
      {document.frmReport.lstPages[0].selected = true;}
     Refresh();
    }

    function ShowAllRecs()
    {
     //Show all of the records on 1 page, then submit the page.
     document.frmReport.txtPageSize.value = <%=objRS.RecordCount%>;
     document.frmReport.AllRecs.value = "yes"
     Refresh();
    }

    function ReSort(SortString)
    {
     //Sort the recordset, then submit the page.
     document.frmReport.SortBy.value = SortString;
     document.frmReport.ReSort.value = "yes";
     Refresh();
    }

    function DoFind(ColNum)
    {
     //Sort the recordset, then submit the page.
     document.frmReport.FindCol.value = ColNum;
     document.frmReport.FindIt.value = "yes";
     Refresh();
    }
    </script>

     

    <form name="frmReport" method="post" action="<%=strCurrentPage%>">


    <!-- Display the report, itself. -->
    <%If Not (objRS.BOF and objRS.EOF) Then%>
     <table border="0" cellpadding="0" cellspacing="0" width="100%">
     <tr>
     <td>
     <table border="0" cellpadding="5" cellspacing="0" width="100%" class="data">
      <!-- Display the column headings. -->
      <tr>
      <%For intCol = 0 To objRS.Fields.Count - 1%>
       <th nowrap valign="top" align="left">
        <b>
        <% ' If FIND is enabled for this column, put FIND button in heading %>
        <%
        If IsArray(FindFields) Then
         boolFound = False
         For i = 0 to UBound(strFindFields)
          If UCase(objRS(intCol).Name) = UCase(strFindFields(i)) Then
          %>
           <!--<a href="javascript:DoFind('<%=intCol%>')" onmouseover="window.status='Find next record containing specified string in <%=objRS(intCol).Name%>'" onmouseout="window.status='';" title="Find within <%=objRS(intCol).Name%>">-->
           <input type="button" value="Find" onclick="javascript:DoFind('<%=intCol%>')" onmouseover="window.status='Find next record beginning with specified string in <%=objRS(intCol).Name%>'" onmouseout="window.status=''"><!--</a>-->
           <input type="text" name="find<%=intCol%>" size="5" maxlength="5" value="<%If boolFind Then Response.Write(Request("find" & intCol))%>"><br>
          <%
           boolFound = True
           Exit For
          End If
         Next
         If NOT boolFound Then
          Response.Write("<br>")
         End If
        End If
        %>
        
        <%=objRS(intCol).Name%></b>
       </th>
      <%Next%>
      </tr>
      <!-- Display the data rows. -->
      <%intDisplayRows = objRS.AbsolutePosition + objRS.PageSize - 1%>
      <%For intRow = objRS.AbsolutePosition to intDisplayRows%>
       <tr>
       <%If CBool( Instr(1, CStr(intRow / 2), ".") > 0) Then
        strColor = RowColor1
       else
        strColor = RowColor2
       End If%>
       <%For intCol = 0 To objRS.Fields.count - 1%>
        <td class="smallfont" align="center" valign="top" style="background:<%=strColor%>" >
        <%=objRS.Fields(intCol).value%></td>
       <%Next%>
       </tr>
       <%objRS.MoveNext%>
       <%if objRS.EOF then exit for%>
      <%Next%>
      <%
       '--- If a find was requested, reset recordset position to
       ' that of the found record
       ' Otherwise, set it back to the top of the page
       ' Don't do any of this if we are not paging
       If objRS.RecordCount > objRS.PageSize Then
        If boolFind Then
         objRS.AbsolutePosition = intAbs
        Else
         If objRS.EOF Then ' check for end-of-file
          objRS.AbsolutePosition = objRS.RecordCount - objRS.PageSize
         Else
          objRS.AbsolutePosition = objRS.AbsolutePosition - objRS.PageSize
         End If
        End If
       End If
      %>
     </table>
     </td>
     </tr>
     </table>

     <!-- Display the "Next" and/or "Previous" paging options. -->
     <table border="0" cellspacing="2" cellpadding="2" align="right">
      <tr>
      <%'--- Determine if we should display a PREVIOUS page button.
      If (intCurrentPage > 1)  Then%>
       <td class="smallfont" align="center" width="55">
        <a href="javascript:MoveToPage(document.frmReport.lstPages.selectedIndex - 1)" onmouseover="window.status='Go to Previous Page';" onmouseout="window.status='';" title="Go to Previous Page">
        Previous</a>
       </td>
      <%End If%>
      <%'--- Determine if we should display a NEXT page button.
      If CInt(intCurrentPage) < CInt(objRS.PageCount) Then%>
       <td class="smallfont" align="center" width="35">
        <a href="javascript:MoveToPage(document.frmReport.lstPages.selectedIndex + 1)" onmouseover="window.status='Go to Next Page';" onmouseout="window.status='';" title="Go to Next Page">
        Next</a>
       </td>
      <%End If%>
      </tr>
     </table>
    <%Else%>
     <center>
     <!-- Display a message stating there were no records found. -->
     <table border="0" cellpadding="2">
      <tr>
       <td class="smallfont" align="center">
        
       </td>
      </tr>
     </table>
     </center>
    <%End If%>

    <!-- column to sort by -->
    <input type="hidden" name="SortBy" value="<%Response.Write(strSort)%>">

    <!-- should the columns be resorted -->
    <input type="hidden" name="ReSort">

    <!-- column to do find on  -->
    <input type="hidden" name="FindCol" value="<%Response.Write(intFindCol)%>">

    <!-- should a find be performed -->
    <input type="hidden" name="FindIt">

    <!-- should all of the records be displayed on one page -->
    <input type="hidden" name="AllRecs">

    </form>

    <%
    Set objRS = Nothing

    End Sub

    End Class
    %>

     

    Friday, May 3, 2013 7:55 PM

Answers