Convert Jet 4.0 code to x64 platform

MS Jet 4 conversion

[DocTaz23]

I am working on migrating an asp site to an x64 platform and need some help. The original developer of the site, about 8 years ago, has not been able to provide any assistance with this migration. I know MS Jet does not work on x64 hence my problem. Here is the code.

Sub retrieveDataFromFile()

'move the uploaded data from the .csv file to an array
set rst = server.CreateObject("ADODB.Recordset")
Set cnn = server.CreateObject("ADODB.Connection")
cnn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\upload;Extended Properties=Text;"
cnn.Open
strSQL = "Select * from " & strFileName
rst.Open strSQL, cnn
sArray = rst.getRows()
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

'call the method to save the data to a temp table in SQL server
saveDatatoDatabase()

End Sub

How can I have this asp page continue to read the csv file that is uploaded into the array?

I am knew to asp however a quick study. Any help would be greatly appreciated. I do have MS SQL 2005 Standard x64 installed on this machine. The line "saveDatatoDatabase()" saves the array into an SQL DB just fine, as this very code works on our aging x86 web server.

[marcel]

Hi DocTaz23, and welcome to WebmasterWorld [webmasterworld.com].

Depending on your version of IIS you can set the application pool to run 32 bit applications.

In IIS 7.x it goes as follows:

- Find the Application Pool for your Website/Web Application (under Server -> Application Pools)

- Right-Click and select Advanced Settings...

- Set Enable 32-Bit Applications to True.

This works fine for my 'older' websites which use JET 4.0 and MS Access databases. (not sure if it also works for opening .csv files, although I think it will.)

I don't have an install of Windows 2003 64 bit at the moment so I haven't been able to test this in IIS 6, maybe someone else has more info on this.

[DocTaz23]

Thanks for your reply. I should have stated the following
Server 2003 SP2 R2 x64, quad core 2.49Ghz CPU, 16GB RAM
IIS 6 x64 mode
SQL 2005 x64
WSUS 3.x x64

We are planning on having a second, identical, server setup with load balancing for IIS (because we perform time card collection via web portal), as well as SQL replication between both servers and back to our corporate office where the payroll processing occurs. These server will be sitting in a data center connected to our corporate office via a point to point T connection (I know, slow, but all that is currently available where our office is).

[marcel]

I'm downloading Win2003 x64 at the moment to test it there.

You could also try using an ODBC connection:

Provider=MSDASQL; Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=D:\upload\;

[DocTaz23]

I entered the code you suggested, changing the path as needed and received the following error when testing.

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

[marcel]

Here is some info on running 32 bit web applications on Windows 2003 x64 / IIS 6:
[microsoft.com...]

Sorry, I sent you on the wrong track with the Microsoft Text Driver, apparently this also no longer works in a 64 bit environment.

[Ocean10000]

I found some links which help setup Classic Asp & Access db to work in IIS 7.

[learn.iis.net...]

Acccess Error '80004005' [support.microsoft.com]

[DocTaz23]

Thank you for the info however I can not run IIS in 32 bit mode. I have to run it in x64 hence my problem. Does anyone know of a way to read a .csv file and place its contents into an array?

[Ocean10000]

Is there a reason why IIS has to be running in 64 bit mode and not 32 bit mode? You can run IIS in 32 bit mode even on the 64 bit OS version.

[DocTaz23]

We are running WSUS 3.x on this server as well. We had IIS set to x86 mode however the WSUS install failed because it detected the server as x64. We did not know at the time about Jet not working on x64 so the change to x64 on IIS allowed WSUS to install and function while stopping the Jet based tool we have from working.

[Ocean10000]

I have been reading and I am under the impression you can change from 64 to 32 bit per application pool in IIS 7 and 7.5. So while your WSUS is running in 64 bit mode your other web applications do not have to be.

Other Items of Reference:
There is a tool I have used a few times released by Microsoft call Logparser 2.2 [microsoft.com]. It does provide a COM interface so it should be able to be used via ASP Classic but it is still 32 bit from what I can read, with no 64 bit version available as of writing this.

[DocTaz23]

We are running IIS 6 on Server 2003 SP2 R2 x64, SQL 2005 x64, WSUS 3.x x64. I am not aware of how to change an IIS 6 application pool to x86 when IIS 6 is running in x64 mode. All of the MS articles I have read so far say you can only run one or the other, not both.

[marcel]

All of the MS articles I have read so far say you can only run one or the other, not both

Apparently this seems to be the case with IIS6 unfortunately. (in IIS 7.x you can run 32 and 64 bit Web apps side-by-side)

You mention the 'Jet based tool we have', is this a separate tool? Is rewriting it in ASP.Net an option?

Also, here is an older thread on Reading a CSV file into an Array with ASP [webmasterworld.com], this method uses a filestream instead of JET 4, may also be an option.

[DocTaz23]

We do have a programmer working on re-writing the tool for us however we need a solution quickly while our programmer writes an entirely new tool. Here is the entire asp file (server/db info changed) This tool was written a few years ago and is in dire need of an update. I do have limited communication with the author of it, however, he has not been able to assist with the updating of the tool since he has moved on to newer employment.

You will see that it uses Jet 4.0 and a connection to our SQL 2005 DB

<%@Language=VBScript%>
<%

Option Explicit
Response.Expires = 0
Response.Buffer = True
Server.ScriptTimeout = 180

%>
<!--#include virtual="/tools/folderlist.asp"-->
<%

'********************************************************************
' Script-Level (Global) Variables
'********************************************************************
Dim strTest, s_vArray, s_i, s_objAcumen, s_rightboxHeader, s_pageHeader, strUploadError, iRow, booleanValidateData
Dim s_strBROKERAGE, s_strDB, s_struserid, objAcumenUserSettings, s_action, s_strConfirmation, s_strErrors, strLogText
Dim objUpload, strFileSavePath, strGUID, strFileName, strExt, s_file_name, s_newsletter, deleteDate, deleteCheck, iCol
dim strStatus, fso, theFile, strLogFileName, sClientFirst, sClientLast
Dim cnn, rst, strSQL, sArray, y, x, newArray, isFound, aRow, thisClient, OldChar, Char, i, TempString
dim sEmployeeName, sDateOfBirth, sClientName, sClientNumber, sMedicareNumber, sBatchNumber, sHours, sUnitBilling, sBilling
dimsUnits, sUnitRate, sPeriodStart, sPeriodEnd, sPayRate, sServiceDate, sCheckNumber, sCheckDate, sServiceCode, sTaxes

'********************************************************************
' Page Execution
'********************************************************************
Call Initialize()

Call Main()

Sub Initialize()

On Error Resume Next

'what folder to save the files uploaded in
strFileSavePath = "D:\upload\"

'initialize the upload com+ component
Set objUpload = Server.CreateObject("Dundas.Upload.2")
objUpload.SaveToMemory

'get the action parameter from the submitted HTML form
s_action = objUpload.Form("action")

If Err.Number <> 0 Then
s_strErrors = s_strErrors & "Initialize - " & Err.Number & Err.Description & Err.Source & "^"
End If

End Sub

Sub Main()

Select Case s_action
Case "Save"
Call uploadFile()
response.Redirect("summarize.asp")
End Select

Call DisplayInterface()

End Sub

Sub DisplayInterface()
%>
<html>
<head>
<title>Web Tool</title>
</head>
<body>
<form name="uploadFile" method="post" enctype="multipart/form-data">
<table width="100%">
<tr>
<td width="100%">
<b>Web Toolkit</b>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<a href="index.asp">Main Menu</a>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<a href="javascript:window.print();">Print</a>
<hr/>
</td>
</tr>
<tr>
<td>
<strong>Billing Summary Upload</strong></font>
</td>
</tr>
</table>
<table>
<tr>
<td>
Browse to the .csv file to be uploaded:
</td>
</tr>
<tr>
<td>
<input name="path_name" type="file" class="button" value="" size="50">
</td>
</tr>
<tr>
<td>
<input type="hidden" name="action" id="action">
<input class="button" type="button" name="Save" value="Upload" onClick="savePage()">
</td>
</tr>
<tr>
<td>
<font color="red"><%response.Write(strStatus)%></font>
</td>
</tr>
</table>
</form>
</body>
</html>
<%
End Sub

Sub uploadFile()

'fix up the file name for allowed characters
strFileName = request.Cookies("acu")("userid") & "_" & date() & "_" & time() & ".csv"
strFileName = replace(strFileName, ":", "_")
strFileName = replace(strFileName, "/", "_")
strFileName = replace(strFileName, " ", "_")

'create schema.ini for the new filename
Set fso=CreateObject("Scripting.FileSystemObject")
Set theFile = fso.OpenTextFile(strFileSavePath & "schema.ini", 2, True)
theFile.WriteLine "[" & strFileName & "]"
theFile.WriteLine "Format=CSVDelimited"
theFile.WriteLine "CharacterSet=ANSI"
theFile.WriteLine "Col1=a Text Width 100"
theFile.WriteLine "Col2=b Text Width 100"
theFile.WriteLine "Col3=c Text Width 100"
theFile.WriteLine "Col4=d Text Width 100"
theFile.WriteLine "Col5=e Text Width 100"
theFile.WriteLine "Col6=f Text Width 100"
theFile.WriteLine "Col7=g Text Width 100"
theFile.WriteLine "Col8=h Text Width 100"
theFile.WriteLine "Col9=i Text Width 100"
theFile.WriteLine "Col10=j Text Width 100"
theFile.WriteLine "Col11=k Text Width 100"
theFile.WriteLine "Col12=l Text Width 100"
theFile.WriteLine "Col13=m Text Width 100"
theFile.WriteLine "Col14=n Text Width 100"
theFile.WriteLine "Col15=o Text Width 100"
theFile.WriteLine "Col16=p Text Width 100"
theFile.WriteLine "Col17=q Text Width 100"
theFile.WriteLine "Col18=r Text Width 100"
theFile.WriteLine "Col19=s Text Width 100"
theFile.Close
Set fso = Nothing
Set theFile = Nothing

'upload the file using the com+ upload component
If objUpload.Files.Count <> 0 Then
objUpload.Files(0).SaveAs (strFileSavePath & strFileName)
Else
strFileName =""
strExt =""
End If

strStatus = "File uploaded successfully at " & time() & " on " & date()

'release objUpload from memory
Set objUpload = Nothing

'call the retrieveDataFromFile() method to get the uploaded file's data into memory
retrieveDataFromFile()

End Sub

Sub retrieveDataFromFile()
'move the uploaded data from the .csv file to an array
set rst = server.CreateObject("ADODB.Recordset")
Set cnn = server.CreateObject("ADODB.Connection")
cnn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\upload;Extended Properties=Text;"
cnn.Open
strSQL = "Select * from " & strFileName
rst.Open strSQL, cnn
sArray = rst.getRows()
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

'call the method to save the data to a temp table in SQL server
saveDatatoDatabase()

End Sub

Sub saveDatatoDatabase()

'set up sql server connections
set rst = server.CreateObject("ADODB.Recordset")
Set cnn = server.CreateObject("ADODB.Connection")
cnn.ConnectionString = "Provider=SQLOLEDB;server=xyz;database=xyz;User ID=xyz;Password=xyz"
cnn.Open

'delete anything in the temp table
strSQL = "delete from BillingTemp"
rst.Open strSQL, cnn

'cycle through and validate the array of uploaded data.
For iRow = 0 To UBound(sArray,2)

'check each column in this row for nulls before saving fields to variables
For iCol = 0 to UBound(sArray,1)
'if any of the fields are null, set them to empty string so it doesn't crash
if isnull(sArray(iCol,iRow)) then
sArray(iCol,iRow) = ""
end if
Next

'save each field in this row of the array to a variable
sEmployeeName = sArray(0,iRow)
sDateOfBirth = sArray(1,iRow)
sClientName = sArray(2,iRow)
sClientNumber = sArray(3,iRow)
sMedicareNumber = sArray(4,iRow)
sBatchNumber = sArray(5,iRow)
sHours = sArray(6,iRow)
sUnitBilling = sArray(7,iRow)
sUnits = sArray(8,iRow)
sUnitRate = sArray(9,iRow)
sPeriodStart = sArray(10,iRow)
sPeriodEnd = sArray(11,iRow)
sPayRate = sArray(12,iRow)
sServiceDate = sArray(13,iRow)
sCheckNumber = sArray(14,iRow)
sCheckDate = sArray(15,iRow)
sServiceCode = sArray(16,iRow)
sTaxes = sArray(17,iRow)
sBilling = sArray(18,iRow)

'check this row of uploaded data for validation errors
checkRowForErrors()

'split the client name into first and last names
sClientLast = Left(sClientName, InStr(sClientName, ",") - 1)
sClientFirst = Right(sClientName, Len(sClientName) - InStr(sClientName, ",") - 1)

'if true then data validated successfully - insert the array row's data into the temp table
strSQL = "insert into BillingTemp values('" & sClientLast & "','" & sClientFirst & "','" & sMedicareNumber & "','" & sPeriodStart & "','" & sPeriodEnd & "','" & sServiceDate & "','" & sBilling & "','" & sServiceCode & "')"
'response.write(strSQL)
'response.end()
rst.Open strSQL, cnn

'move to the next row down
Next

'close and release connection and recordset
Set rst = Nothing
Set cnn = Nothing

End Sub

Function checkRowForErrors()

'validate the data and check for errors here

End Function
%>

<script language="JavaScript">
function savePage()
{
if(document.uploadFile.path_name.value == "")
{
alert("You must select a .csv file to upload");
}
else
{
document.uploadFile.action.value = "Save";
document.uploadFile.submit();
}
}
</script>