Open Excel in Readonly mode ASP Recordset

sunvalley · 09-29-2010, 02:29 PM

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>


<head>
<title>ROW IT Inventory Search</title>
<link href="row_stylesheet.css" rel="stylesheet" type="text/css" media="screen" />


<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>


<script src="sorttable.js"></script>


<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;
}
}
//-->

</script>


</head>


<body bgcolor="#EDEDED" onload="document.search_btn.search.focus();"><a name="top" style="visibility:hidden">top</a>


<%
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 id="wrapper" align="center" style="position:absolute; width:100%; top:0px; visibility: visible;">


<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>


<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>


<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 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'">    
<input type="submit" style="display:; margin-top:5px" value="Submit" title="Click to Search for Inventory Records">
</form>


<h2 align="center" style="margin-top:5px; font-family:Arial; font-size:11px; font-weight:600; color:#0000B0;">Need Help?  <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>


<%If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then%>


<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>


<%Else%>


<%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 class="sortable" width="98%" align="center" border="0" cellspacing="1" cellpadding="2" bgcolor="#FFFFFF" style="margin-top:0px;">


<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>


<%Do While Not rsGlobalWeb.EOF%>


<%If iRecordCount Mod 2=0 Then%>
<tr bgcolor="#BBCCE4">
<%Else%>
<tr bgcolor="#BBCCE4">
<%End If%>


<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>


<%
iRecordCount=iRecordCount + 1
rsGlobalWeb.MoveNext
Loop
%>


</table>


<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>


<%End If%>
<%End If%>


<%
rsGlobalWeb.Close
dbGlobalWeb.Close
%>


</div>
</body>
</html>

Old Pedant · 09-29-2010, 07:07 PM

*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 )

sunvalley · 09-29-2010, 09:09 PM

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?

Old Pedant · 09-29-2010, 09:22 PM

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.

Old Pedant · 09-29-2010, 09:24 PM

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.

sunvalley · 09-29-2010, 10:06 PM

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
%>

Old Pedant · 09-29-2010, 10:18 PM

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 ...

sunvalley · 09-29-2010, 10:29 PM

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
%>

Old Pedant · 09-29-2010, 10:37 PM

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.

Old Pedant · 09-29-2010, 10:40 PM

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".

sunvalley · 09-30-2010, 04:06 AM

Thanks A Bunch Old Pedant for all your help, I found another solution.