Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Apr 2010
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Open Excel in Readonly mode ASP Recordset

    Hi,

    What do I have to add to my asp code shown below so that when the recordset pulls data from the excel file it will do it in READONLY mode, then I could open the excel file and make changes to it while the asp code is showing data from it? Right now when the asp code is pulling data from the excel file I can't open the excel file because it is locked by the asp code.

    Any help is appreciated, Thanks to all that reply.

    My Code is Shown Below

    <%@ LANGUAGE="VBSCRIPT" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns=http://www.w3.org/1999/xhtml>


    <!--start of head tag that contains under it scripts and css style codes-->
    <head>
    <title>ROW IT Inventory Search</title>
    <link href="row_stylesheet.css" rel="stylesheet" type="text/css" media="screen" />


    <!--start and closing of css style codes to give appearance to other elements on page-->
    <style type="text/css">
    a:active {outline:none;}
    a:focus {outline:none;}
    a:focus {-moz-outline-style:none;}
    /* Hyperlinks Default Format */
    a:link {
    color: #B40000;
    text-decoration: none;
    }
    a:visited {
    text-decoration: none;
    color: #B40000;
    }
    a:hover {
    text-decoration: underline;
    color: #000097;
    }
    a:active {
    text-decoration: none;

    }
    /* Table Records Format */
    .tds {
    text-transform: capitalize;
    color: #000000;
    height: 25px;
    letter-spacing: 1px;
    text-align: center;
    vertical-align: middle;
    font-family: Arial;
    font-size: 12px;
    font-weight: regular;
    }
    /* Sortable tables */
    .sortable thead {
    background-color:#eee;
    color:#666666;
    font-weight: bold;
    cursor: hand;
    }
    </style>


    <!--start and closing of script code for sorting recordset data in a table - linked to another file-->
    <script src="sorttable.js"></script>


    <!--start and closing of javascript codes - will check if search text box is empty then display message-->
    <script type="text/javascript">
    function checkForm()
    {
    if(document.search_btn.search.value.length==0) {
    alert("Please Enter Property ID or Short Description or User Name and press Submit");
    document.search_btn.search.focus();
    return false;
    } else {
    return true;
    }
    }
    //-->
    <!--
    function MM_goToURL() { //v3.0
    var i, args=MM_goToURL.arguments; document.MM_returnValue = false;
    for (i=0; i<(args.length-1); i+=2) eval(args[i]+".location='"+args[i+1]+"'");
    }
    //-->
    </script>


    <!--closing head tag that contains above it scripts and css style codes-->
    </head>


    <!--start of html body code with back to top anchor-->
    <body bgcolor="#EDEDED" onload="document.search_btn.search.focus();"><a name="top" style="visibility:hidden">top</a>


    <!--recordset code to connect, pull data from one excel spreadsheet, and display, sort, bold searched results-->
    <%
    Dim Results
    Dim iRecordCount

    iRecordCount = 0

    Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
    dbGlobalWeb.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"&_
    "DBQ=" & Server.MapPath("inventory-09.xlsx") & ";"

    Results = "SELECT * FROM [assets by end user for 84-01$]"
    Results = Results & " WHERE property_id+name+short_desc LIKE '%" & Request.Form("search") & "%' order by property_id ASC;"

    Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
    rsGlobalWeb.Open Results, dbGlobalWeb, 3,3

    dim newSearch
    newSearch = request.form("search")

    private function makeBold(FieldString, SearchString)
    dim FieldStringLength
    dim SearchStringLength
    dim newString
    dim TempString
    dim StartStringLength
    dim EndStringLength
    dim StartBoldStyle
    dim EndBoldStyle
    dim BoldStyleLength

    newString = ""

    StartBoldStyle = "<span style='color:#990000; font-weight:bold;'>"
    EndBoldStyle = "</span>"
    BoldStyleLength = len(StartBoldStyle) + len(EndBoldStyle)
    FieldStringLength = len(FieldString)
    SearchStringLength = len(SearchString)
    StartLocation = instr(LCase(FieldString), LCase(SearchString))

    if(StartLocation <> 0) then
    if(StartLocation <> 1) then
    StartStringLength = StartLocation - 1
    newString = newString & Left(FieldString, StartStringLength)
    end if
    TempString = StartBoldStyle & Mid(FieldString, StartLocation, SearchStringLength) & EndBoldStyle
    newString = newString & TempString
    if(len(newString) < (FieldStringLength + BoldStyleLength)) then
    EndStringLength = FieldStringLength - (len(newString) - BoldStyleLength)
    newString = newString & Right(FieldString, EndStringLength)
    end if
    else
    newString = FieldString
    end if

    makeBold = newString

    end function
    %>


    <!--div used to wrap around all contents on page-->
    <div id="wrapper" align="center" style="position:absolute; width:100%; top:0px; visibility: visible;">

    <!--div hyperlink to go to right of way home site-->
    <div align="left" style="margin-left:10px; margin-top:5px;"><a href="http://rowsrv002/" title="Go to Right of Way Home Site"><< Return to Right of Way Home</a></div>

    <!--title of asp page-->
    <h2 align="center" style="margin-top:10px; text-transform: capitalize; font-family:Verdana; font-weight:bold; font-size:18px; text-decoration: none; color:#0066CC;">ROW IT Inventory Search</h2>


    <!--instructions that explain how to use search to find excel data-->
    <h2 align="center" style="margin-top:-10px; font-family:Arial; font-size:13px; font-weight:600; color:#313131; #0000B0#;">Enter <u>Property ID</u> or <u>Short Description</u> or <u>User Name</u> and press Submit<br> (example: <u>72000111</u> or <u>printer</u> or <u>john</u> )</h2>


    <!--form code text box and submit button to start recordset findings of matched data from search values-->
    <form action="search_row-it_inventory.asp" target="_self" method="post" name="search_btn" id="search_btn" style="width:150px;" onsubmit="return checkForm(this);">
    <input name="search" type="text" id="search" style="background-color:#fff; margin-left:-50px; height:18px; width:250px; border-style:solid; border-width:1px; border-color:"#000000" title="Please Enter Information as Stated Above" maxlength="25" autocomplete="off">
    <input type="button" style="display:; margin-top:5px" value="Reset" title="Reset Page to Default" onClick="parent.location='http://localhost:80/search_row-it_inventory.asp'">&nbsp;&nbsp;&nbsp;&nbsp;
    <input type="submit" style="display:; margin-top:5px" value="Submit" title="Click to Search for Inventory Records">
    </form>


    <!--mail to address code for users to contact help about this asp page-->
    <h2 align="center" style="margin-top:5px; font-family:Arial; font-size:11px; font-weight:600; color:#0000B0;">Need Help? &nbsp;<u><a href="mailto:rowhelpdesk@mdot.state.ms.us&cc=spatel@mdot.state.ms.us?subject=Need Help with ROW IT Inventory Search" onMouseOver="window.status='Contact ROW Help Desk';return true" onMouseOut="window.status='';return true">Click Here</a></u></h2>


    <!--start if clause to display data if found or else display message-->
    <%If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then%>


    <!--recordset display 0 records if no matched is found in excel data-->
    <h2 align="center" style="margin-top:30px; font-family:Arial; text-transform:capitalize; font-size:16px; font-weight:bold; color:#FE0000;">No Records Found: <u><%response.write(rsGlobalWeb.RecordCount)%></u></h2>


    <!--determines what to do next if records are found in excel spreadsheet-->
    <%Else%>


    <!--recordset display total number of records if match is found in excel data-->
    <%If Not rsGlobalWeb.BOF Then%>
    <h2 align="center" style="margin-top:30px; font-family:Arial; font-size:16px; font-weight:600; color:#0000B0;">Total Records: <u><%response.write(rsGlobalWeb.RecordCount)%></u></h2>


    <!--table display format - linkable to a sorting code stated above-->
    <table class="sortable" width="98%" align="center" border="0" cellspacing="1" cellpadding="2" bgcolor="#FFFFFF" style="margin-top:0px;">


    <!--table headers display format - clickable to sort records in each column -->
    <tr align="center" title="Click Header to Sort Column" bgcolor="#4F81BD" style="margin-top:8px; font-family:Arial; font-size:14px; font-weight:bolder; text-transform:capitalize; color:#FFFFFF; letter-spacing:1px;">
    <th onMouseover="this.style.textDecoration='underline';" onMouseout="this.style.textDecoration='none';">Property <br>ID</th>
    <th onMouseover="this.style.textDecoration='underline';" onMouseout="this.style.textDecoration='none';">Short <br>Description</th>
    <th onMouseover="this.style.textDecoration='underline';" onMouseout="this.style.textDecoration='none';">SubLocation <br>Code</th>
    <th onMouseover="this.style.textDecoration='underline';" onMouseout="this.style.textDecoration='none';">User <br>Name</th>
    <th onMouseover="this.style.textDecoration='underline';" onMouseout="this.style.textDecoration='none';">Manufacturing <br>Date</th>
    <th onMouseover="this.style.textDecoration='underline';" onMouseout="this.style.textDecoration='none';">Model <br>Number</th>
    <th onMouseover="this.style.textDecoration='underline';" onMouseout="this.style.textDecoration='none';">Serial <br>Number</th>
    <th onMouseover="this.style.textDecoration='underline';" onMouseout="this.style.textDecoration='none';">Price</th>
    </tr>


    <!--recordset processing how many records are in the excel spreadsheet to display-->
    <%Do While Not rsGlobalWeb.EOF%>


    <!--record count display and table row alternate color format-->
    <%If iRecordCount Mod 2=0 Then%>
    <tr bgcolor="#BBCCE4">
    <%Else%>
    <tr bgcolor="#BBCCE4">
    <%End If%>


    <!--recordset display excel data in table cells-->
    <td class="tds"><%=makeBold(rsGlobalWeb("property_id"),newSearch)%></td>
    <td class="tds"><%=makeBold(rsGlobalWeb("short_desc"),newSearch)%></td>
    <td class="tds"><%=rsGlobalWeb("sublocation_code")%></td>
    <td class="tds"><%=makeBold(rsGlobalWeb("name"),newSearch)%></td>
    <td class="tds"><%=rsGlobalWeb("manufacturing date")%></td>
    <td class="tds"><%=rsGlobalWeb("model number")%></td>
    <td class="tds"><%=rsGlobalWeb("serial number")%></td>
    <td class="tds"><%=rsGlobalWeb("price")%></td>
    </tr>


    <!--record count and looping through excel data to display in table-->
    <%
    iRecordCount=iRecordCount + 1
    rsGlobalWeb.MoveNext
    Loop
    %>


    <!--closing tag for table code-->
    </table>

    <!--back to top anchor link - takes users back to top of page-->
    <center style="font-family:Arial; font-size:12px;"><a href="#top" onMouseOver="window.status='Go Back To Top';return true" onMouseOut="window.status='';return true">Back to Top</a><br><span style="font-size:10px; font-weight:bold;">Copyright © 2010 Right of Way Division, MDOT.</span></center>


    <!--closing if clauses of matched records from search-->
    <%End If%>
    <%End If%>


    <!--closing recordset connection to excel spreadsheet-->
    <%
    rsGlobalWeb.Close
    dbGlobalWeb.Close
    %>


    <!--closing tags for div wrapper, body and html codes-->
    </div>
    </body>
    </html>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    *POSSIBLY* you can do it by changing the connection to read-only:
    Code:
    Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
    dbGlobalWeb.Mode = 1 ' adReadOnly
    dbGlobalWeb.Open ...
    Then you also need to STOP opening the recordset for UPDATE! Which is what this code does:
    Code:
    Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
    rsGlobalWeb.Open Results, dbGlobalWeb, 3,3
    The "3,3" there means "adOpenStatic, adLockOptimistic" which is the most expensive kind of recordset you can use, in addition to ensuring that the resource is locked for writing.

    Replace those two lines with just this:
    Code:
    Set rsGlobalWeb = dbGlobalWeb.Execute( Results )
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Apr 2010
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi again, thanks for helping me...but the code provided doesn't stop Excel from locking during the recordset search. It not opening excel in readonly mode because when I try to open the excel file I get the message below...

    inventory-09.xlsx is locked for editing by 'another user'

    Any other ideals?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    Well, it probably won't work, but it's worth trying to use the JET OLEDB driver instead of that (really ancient...vintage 1998) Excel driver.

    Change your connection stuff to:
    Code:
    Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
    dbGlobalWeb.Mode = 1 ' adReadOnly
    dbGlobalWeb.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & Server.MapPath("inventory-09.xlsx") & _
               "Extended Properties=""Excel 9.0;HDR=Yes""" 
    dbGlobalWeb.Mode = 1 ' try putting this after the open as well as before
    No guarantees, at all. Just things to try.

    And try "Excel 8.0" as well as "Excel 9.0". Experiment.

    ****************

    I just had a really dumb idea: Use the FileSystemObject to *COPY* the ".xslx" file to a temporary working copy and then open *that* instead of the main file.

    So trivially easy to do, and guarantees that you won't be locking the original file.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    What I am *REALLY* surprised about is that the file is still locked even though you were careful to close the recordset and connection:
    Code:
    <%
    rsGlobalWeb.Close
    dbGlobalWeb.Close
    %>
    I don't understand why that would keep the lock open, at all.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New Coder
    Join Date
    Apr 2010
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I too would have thought closing the connections would have not caused this problem but since I'm not a programmer I don't know what else to put in this code.

    The code suggest I also tried and it didn't work, below is what I have changed it to...

    Thank Again

    Code is Below
    <%
    Dim Results
    Dim iRecordCount

    iRecordCount = 0

    Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
    dbGlobalWeb.Mode = 1 ' adReadOnly
    dbGlobalWeb.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
    "Data Source=" & Server.MapPath("inventory-09.xlsx") &_
    "Extended Properties=""Excel 9.0;HDR=Yes"""
    dbGlobalWeb.Mode = 1

    Results = "SELECT * FROM [assets by end user for 84-01$]"
    Results = Results & " WHERE property_id+name+short_desc LIKE '%" & Request.Form("search") & "%' order by property_id ASC;"

    Set rsGlobalWeb = dbGlobalWeb.Execute( Results )

    dim newSearch
    newSearch = request.form("search")

    private function makeBold(FieldString, SearchString)
    dim FieldStringLength
    dim SearchStringLength
    dim newString
    dim TempString
    dim StartStringLength
    dim EndStringLength
    dim StartBoldStyle
    dim EndBoldStyle
    dim BoldStyleLength

    newString = ""

    StartBoldStyle = "<span style='color:#990000; font-weight:bold;'>"
    EndBoldStyle = "</span>"
    BoldStyleLength = len(StartBoldStyle) + len(EndBoldStyle)
    FieldStringLength = len(FieldString)
    SearchStringLength = len(SearchString)
    StartLocation = instr(LCase(FieldString), LCase(SearchString))

    if(StartLocation <> 0) then
    if(StartLocation <> 1) then
    StartStringLength = StartLocation - 1
    newString = newString & Left(FieldString, StartStringLength)
    end if
    TempString = StartBoldStyle & Mid(FieldString, StartLocation, SearchStringLength) & EndBoldStyle
    newString = newString & TempString
    if(len(newString) < (FieldStringLength + BoldStyleLength)) then
    EndStringLength = FieldStringLength - (len(newString) - BoldStyleLength)
    newString = newString & Right(FieldString, EndStringLength)
    end if
    else
    newString = FieldString
    end if

    makeBold = newString

    end function
    %>

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    Okay, this is off the top of my head, as I don't have my references handy. But give it a shot.
    Code:
    <%
    Dim Results
    Dim iRecordCount
    Dim tempfile, fso
    
    Set fso = Server.CreateObject("Scripting.FileSystemObject")
    tempfile = Server.MapPath("tempinv.xlsx")
    fso.CopyFile Server.MapPath("inventory-09.xlsx"), tempfile
    
    iRecordCount = 0
    
    Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
    dbGlobalWeb.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
    "Data Source=" & tempfile &_
    "Extended Properties=""Excel 9.0;HDR=Yes"""
    
    ... all code after this same ...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New Coder
    Join Date
    Apr 2010
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Nope, didn't work...my site would not open at all.

    I do really appreciate your input, I'll try linking excel file to an access database and recordset my asp code to access db that way I can open and edit the excel file.

    BTW; just want to show you what I have now changed it to...


    <%
    Dim Results
    Dim iRecordCount
    Dim tempfile, fso

    Set fso = Server.CreateObject("Scripting.FileSystemObject")
    tempfile = Server.MapPath("tempinv.xlsx")
    fso.CopyFile Server.MapPath("inventory-09.xlsx"), tempfile

    iRecordCount = 0

    Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
    dbGlobalWeb.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
    "Data Source=" & tempfile &_
    "Extended Properties=""Excel 9.0;HDR=Yes"""

    Results = "SELECT * FROM [assets by end user for 84-01$]"
    Results = Results & " WHERE property_id+name+short_desc LIKE '%" & Request.Form("search") & "%' order by property_id ASC;"

    Set rsGlobalWeb = dbGlobalWeb.Execute( Results )

    dim newSearch
    newSearch = request.form("search")

    private function makeBold(FieldString, SearchString)
    dim FieldStringLength
    dim SearchStringLength
    dim newString
    dim TempString
    dim StartStringLength
    dim EndStringLength
    dim StartBoldStyle
    dim EndBoldStyle
    dim BoldStyleLength

    newString = ""

    StartBoldStyle = "<span style='color:#990000; font-weight:bold;'>"
    EndBoldStyle = "</span>"
    BoldStyleLength = len(StartBoldStyle) + len(EndBoldStyle)
    FieldStringLength = len(FieldString)
    SearchStringLength = len(SearchString)
    StartLocation = instr(LCase(FieldString), LCase(SearchString))

    if(StartLocation <> 0) then
    if(StartLocation <> 1) then
    StartStringLength = StartLocation - 1
    newString = newString & Left(FieldString, StartStringLength)
    end if
    TempString = StartBoldStyle & Mid(FieldString, StartLocation, SearchStringLength) & EndBoldStyle
    newString = newString & TempString
    if(len(newString) < (FieldStringLength + BoldStyleLength)) then
    EndStringLength = FieldStringLength - (len(newString) - BoldStyleLength)
    newString = newString & Right(FieldString, EndStringLength)
    end if
    else
    newString = FieldString
    end if

    makeBold = newString

    end function
    %>

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    First, fix your browser:

    Click on TOOLS menu
    Click on INTERNET OPTIONS menu item
    Click on ADVANCED tab
    *UN*check "Show friendly HTTP error messages"
    OK

    Now see if you get a real error message as to what the problem might be.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    Oh...and that CopyFile will only work the first time! To make it work on subsequent hits of this page, you need to do
    Code:
    fso.CopyFile Server.MapPath("inventory-09.xlsx"), tempfile, True
    The TRUE says "do this even if the tempfile already exists".
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New Coder
    Join Date
    Apr 2010
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks A Bunch Old Pedant for all your help, I found another solution.


  •  

    Posting Permissions

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