Database Locking up

M

Mark A. Sam

Hello,

I have a Access Db on a website. I have a procedure of FTP'ing a database
(clientDB.mdb) to the site then running an active server page to transfer
the data to the main database (Truckloads.mdb). After the transfer I close
the ftp program (wsFTP Pro v8.01).

Next I run the ASP. A couple weeks ago the company which hosts my site,
upgraded from Win2000 to Win2003. Immediately there were problems. The
active server pages stopped working intermittantly. I discovered that there
was an .ldb file ( Truckloads.ldb) in the Database directory which was
causing the problem. The company said that they tracked the problem to
coincide with the time I ran the ftp program and my asp. Since I
transferred clientDB.ldb, I don't get the connection. Nothing changed on my
website, but the server software changed, so logically, it would be
something in Win2003 or the configuration that is the problem. Does that
seem feasible?

The company also claimed that my Active server pages were hosing his server.
I don't know which pages, but here is the page I run to do the transfer. Is
ther something in that which could cause memory problems? I only run it a
half dozen times per day. I'll post other code in another thread.

Thanks for any help.

<%@ Language=VBScript %>
<html>

<head>
<title></title>
</head>

<body>
<!-- #include virtual="/Upload_Pages/adovbs.inc" -->
<%


Response.Write "Uploading data... <br>"
Dim DSNConn, DSNClient
Dim ServerConn, ServerRst, ClientConn, ClientRst
Dim vSQL, ConnErr, fld, fldname
DSNConn = "DSN=TruckloadsDev"
DSNClient = "DSN=Clientdb"

Set ServerConn = server.CreateObject("ADODB.Connection")
ServerConn.Open DSNConn

Set ClientConn = server.CreateObject("ADODB.Connection")
ClientConn.Open DSNClient

ServerConn.BeginTrans
If UploadTrucks = True then
if UploadLoads = true then
Response.Write "<br><br>All OK!"
ServerConn.CommitTrans%>
<form action=ViewData.asp method=post>
<input type=submit value="View Revised Data">
</form>
<%else
Response.Write "<br><br>Due to errors, no uploading has occurred."
ServerConn.RollbackTrans
end if
else
Response.Write "<br><br>Due to errors, no uploading has occurred."
ServerConn.RollbackTrans
end if


set ClientRst = nothing
set ClientConn = nothing

set ServerRst = nothing
set ServerConn = nothing
%>

<p>&nbsp;</p>
<%

function UploadLoads

on error resume next
Response.Write "<br> Uploading Loads..."
Set ServerRst = server.CreateObject("ADODB.Recordset")
Set ClientRst = server.CreateObject("ADODB.Recordset")
Set ClientRst = ClientConn.Execute("Select * from LOADS")

If ClientConn.Errors.Count = 0 then 'there is a Loads table and it's now
open
'Open Loads table on server database
vSQL = "Select * from LOADS Where 1=0"
ServerRst.Open vSQL, ServerConn, adOpenKeyset, adLockBatchOptimistic,
adCmdText
If ServerConn.Errors.Count = 0 then'success
do until ClientRst.Eof 'loop through records on client db
ServerRst.addnew
for each fld in Clientrst.Fields 'loop through fields
fldname = fld.name
if fldname = "ID" or fldname = "posted" or fldname = "archive" or
fldname = "delete" then
'do nothing
Else
serverrst(fldname) = clientrst(fldname)
end if
next
serverrst("Uploaded")=Now()
'ServerRst.addnew arFields, arValues
ClientRst.movenext
loop
ServerRst.UpdateBatch
if err.number = 0 then
Response.Write "<br>Loads successfully updated!"
UploadLoads = True
else
Response.Write "<br>Error uploading 'Load' information: <br>" &
err.number & ", " & err.description
UploadLoads = False
end if
Else 'error opening Loads in Server db
Response.write "<br>Error(s) in server database:"
for each ConnErr in ServerConn.Errors
Response.write ConnErr.Number & ", " & ConnErr.Description & "; <br>"
next
UploadLoads = False
end if
else 'error opening Loads table in client db
if ClientConn.Errors(1).Number = -2147217865 then 'table isn't there
Response.Write "<br>No 'Loads' records to upload.<br>"
UploadLoads = True
else 'Some other error
Response.write "<br>Error(s) in client database:"
for each ConnErr in ClientConn.Errors
Response.write ConnErr.Number & ", " & ConnErr.Description & "; <br>"
next
UploadLoads = False
end if
end if
ServerRst.Close
set ServerRst = nothing
ClientRst.close
set ClientRst = nothing
end function

function UploadTrucks
on error resume next
dim i
i=0
Response.Write "<br><br> Uploading Trucks..."
Set ServerRst = server.CreateObject("ADODB.Recordset")
Set ClientRst = server.CreateObject("ADODB.Recordset")

Set ClientRst = ClientConn.Execute("Select * from TRUCKS")


If ClientConn.Errors.Count = 0 then 'there is a Trucks table and it's now
open

'Open Trucks table on server database; return no records to improve speed
vSQL = "Select * from Trucks where 1=0"
ServerRst.Open vSQL, ServerConn, adOpenKeyset, adLockBatchOptimistic,
adCmdText
If ServerConn.Errors.Count = 0 then'success
do until ClientRst.Eof 'loop through records on client db
ServerRst.addnew
for each fld in Clientrst.Fields 'loop through fields
fldname = fld.name
if fldname = "ID" or fldname = "posted" or fldname = "archive" or
fldname = "delete" then
'do nothing
Else
serverrst(fldname) = clientrst(fldname)
end if
next
Serverrst("Uploaded")=Now()
'ServerRst.addnew arFields, arValues
ClientRst.movenext
loop
ServerRst.UpdateBatch
if err.number = 0 then
UploadTrucks = True
Response.Write "<br>Trucks successfully updated!"
else
Response.write "<br>Error uploading 'Truck' information: <br>" &
err.number & ", " & err.description
UploadTrucks = False
end if
Else 'error opening Loads in Server db
Response.write "<br>Error(s) in server database:"
for each ConnErr in ServerConn.Errors
Response.write = UploadTrucks & ConnErr.Number & ", " &
ConnErr.Description & "; <br>"
next
UploadTrucks = False
end if
else 'error opening Loads table in client db
if ClientConn.Errors(1).Number = -2147217865 then 'table isn't there
Response.Write "<br>No 'Trucks' records to upload.<br>"
UploadTrucks=True
else 'Some other error
Response.write "<br>Error(s) in client database:"
for each ConnErr in ClientConn.Errors
Response.write ConnErr.Number & ", " & ConnErr.Description & "; <br>"
next
UploadTrucks = False
end if
end if
ServerRst.Close
set ServerRst = nothing
ClientRst.close
set ClientRst = nothing

end function%>
</body>
</html>
 
K

Kevin Spencer

Hard to say for sure. However, you're using "on error resume next" which is
a very dangerous directive. If you don't handle errors correctly, the
processing resumes, and who knows what might happen, particularly in the
context of a transaction. There is a reason that certain errors stop
processing by default.

Your other code in the other message looks safe.

As to what might have changed, well, Windows Server 2003 has very tight
security, and may not be as forgiving as earlier operating systems. In
addition, it has the latest MDAC (Microsoft Data Access Components), and
it's hard to say whether the MDAC your app was using previously was the same
version.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
 
M

Mark A. Sam

Hello Kevin,

I didn't ignor your response, my reply never got posted for some reason. I
don't recall what I wrote, but thanks for answering.

God Bless,

Mark
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

stop execution 1
ASP - send email after pure ASP upload 4
Working outlook via vba code with xlsx - error 0
Path reference for (say) mydb.mdb in myfile.asp ? 2
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
ASP Script to Send an email which generates into a HTML format 2
Another VTI-Group question 7
Update form not functioning correctly (VB/SQL) 3

Top