Very Hard one please help

landslide

03-13-2007, 04:34 PM

Ok this is my problem. I have a page that pulls alot of records from a table and updates them all at the same time. They all have different values when updated. It works perfect but if thier are lot of records it will somtimes give me this message:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Could not update; currently locked by user 'admin' on machine 'server'.
I have to press submit mutiple times before it will update. Now I already check that no one else is in the database. I am the only one on this server and it does it at random only when I update a large number of records. Could it be my code? Please help! Thanks.
<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>

<%
var Recordset1 = Server.CreateObject("ADODB.Recordset");
Recordset1.ActiveConnection = MM_lvcc_STRING;
Recordset1.Source = "SELECT tblJanuary.SSN, tblJanuary.EOD, [tblJanuary.Last four], tblJanuary.firstname, tblJanuary.lastname, tblJanuary.ORGCODE, tblTollFreeEmployees.SCD, tblJanuary.SEID, tblJanuary.jan4, tblJanuary.extra2, tblJanuary.timestamp1 FROM tblJanuary, tblTollFreeEmployees WHERE [tblTollFreeEmployees.SEID] = [tblJanuary.SEID] and tblJanuary.jan4 LIKE ('R%')ORDER BY tblJanuary.EOD, SCD, [Last Four]";
Recordset1.CursorType = 0;
Recordset1.CursorLocation = 2;
Recordset1.LockType = 1;
Recordset1.Open();
var Recordset1_numRows = 0;

arjan4 = new Array();
arSeid = new Array();

var f=new Enumerator(Request.Form);
var el;
var start;
var len;
var idx;

for (; !f.atEnd(); f.moveNext()) {
el = f.item().toString().toLowerCase();
start = el.indexOf("_");

if(start > -1) {
start++; len = el.length - start;
idx = parseInt(el.substr(start, len));

if(!isNaN(idx)) {
if(el.indexOf("jan4")!=-1) arjan4[idx] = Request.Form(el);
if(el.indexOf("seid")!=-1) arSeid[idx] = Request.Form(el);
}
}
}

for(var i=0;i<arSeid.length;i++) {
if(arjan4[i] && arSeid[i]) {
updateDB (arjan4[i], arSeid[i]);
}
else {
Response.Write("");
}
}

function updateDB(param_jan4, param_seid) {
var update = Server.CreateObject("ADODB.Command");
update.ActiveConnection = MM_lvcc_STRING;
update.CommandText = update.CommandText = "UPDATE tblJanuary SET Jan4 = '" + param_jan4 + "' WHERE seid = '" + param_seid + "'";
update.CommandType = 1;
update.CommandTimeout = 0;
update.Prepared = true;
update.Execute();
}

var Repeat1__numRows = -1;
var Repeat1__index = 0;
Recordset1_numRows += Repeat1__numRows;
%>
<%
var Recordset2__MMColParam = "1";
if (String(Session("MM_Username")) != "undefined" &&
String(Session("MM_Username")) != "") {
Recordset2__MMColParam = String(Session("MM_Username"));
}
%>
<%
var Recordset2 = Server.CreateObject("ADODB.Recordset");
Recordset2.ActiveConnection = MM_lvcc_STRING;
Recordset2.Source = "SELECT LASTNAME, FIRSTNAME, SEID FROM Adminlogin WHERE SEID = '"+ Recordset2__MMColParam.replace(/'/g, "''") + "'";
Recordset2.CursorType = 0;
Recordset2.CursorLocation = 2;
Recordset2.LockType = 1;
Recordset2.Open();
var Recordset2_numRows = 0;
%>
<!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>
<script type="text/javascript">

</script>
<style type="text/css">

</style>
</head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>jan4</title>
<style type="text/css">

</style>
</head>

<body>
<p align="right"><a href="Jan5approve.asp">Click here to continue to the next day. </a></p>
<p align="center" class="style9">Approved by <span class="style11"><%=(Recordset2.Fields.Item("FIRSTNAME").Value)%>  <%=(Recordset2.Fields.Item("LASTNAME").Value)%></span></p>
<form method="post" id="form1" name="form1" action="">
<p align="center" class="style8">January 4th </p>
<p align="center">Input the amount of leave that can be given
<input name="lvavalbl" type="text" id="lvavalbl" size="2" onblur="doCalcs(form1)"/>
</P>
<table border="1" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
<tr>
<td><div align="center"><span class="style7">ID</span></div></td>
<td><div align="center"><span class="style7">EOD</span></div></td>
<td><div align="center"><span class="style7">SCD</span></div></td>
<td><div align="center"><span class="style7">Last four</span></div></td>
<td><div align="center"><span class="style7">First Name</span></div></td>
<td><div align="center"><span class="style7">Last Name</span></div></td>
<td><div align="center"><span class="style7">ORGCODE</span></div></td>
<td><div align="center"><span class="style7">SEID</span></div></td>
<td><div align="center"><span class="style7">jan4</span></div></td>
<td><div align="center"><span class="style7">timestamp1</span></div></td>
</tr>
<% while ((Repeat1__numRows-- != 0) && (!Recordset1.EOF)) { %>
<tr>
<td><span class="style7"><%=(Recordset1.Fields.Item("SSN").Value)%></span></td>
<td><span class="style10"><%=(Recordset1.Fields.Item("EOD").Value)%></span></td>
<td><span class="style10"><%=(Recordset1.Fields.Item("SCD").Value)%></span></td>
<td><span class="style7"><%=(Recordset1.Fields.Item("Last four").Value)%></span></td>
<td><span class="style10"><%=(Recordset1.Fields.Item("firstname").Value)%></span></td>
<td><span class="style10"><%=(Recordset1.Fields.Item("lastname").Value)%></span></td>
<td><span class="style7"><%=(Recordset1.Fields.Item("ORGCODE").Value)%></span></td>
<td><input name="seid_<%=Repeat1__index%>" type="text" id="seid_<%=Repeat1__index%>" value="<%=(Recordset1.Fields.Item("SEID").Value)%>" size="5" maxlength="5" readonly="<%=(Recordset1.Fields.Item("SEID").Value)%>" /></td>
<td><input name="jan4_<%=Repeat1__index%>" type="text" id="jan4" value="<%=(Recordset1.Fields.Item("jan4").Value)%>" size="4" /></td>
<td><span class="style7"><%=(Recordset1.Fields.Item("timestamp1").Value)%></span></td>
</tr>
<%
Repeat1__index++;
Recordset1.MoveNext();
}
%>
</table>
<p align="center">
<input name="Submit2" type="submit" value="Submit" />
</p>
</form>
</body>
</html>
<%
Recordset1.Close();
%>
<%
Recordset2.Close();
%>

Spudhead

03-14-2007, 01:24 PM

There are some ways in which your code could be quicker, but I reckon your main problem is in using Access. How many records at a time are you trying to udate? How many concurrent users do you expect to support?

SSJ

03-15-2007, 01:27 PM

currently locked by user 'admin' on machine 'server'

where your page located?
On the same machine(Server)?

Basscyst

03-15-2007, 10:10 PM

Try closing the record set and the connection objects each time it calls updateDB. I think what is happening is that you are creating a new connection with each call of the function, when you have numerous records, it eventually reached the limit of allowed connections. You may also just try creating the connection object once and reusing it.

landslide

03-16-2007, 10:24 PM

Im real new at this how would I write it to reuse the connection established? It is alot of records at once. I say about 50 to 100 max. Thanks for your help.

Spudhead

03-19-2007, 01:37 PM

I think what is happening is that you are creating a new connection with each call of the function, when you have numerous records, it eventually reached the limit of allowed connections. You may also just try creating the connection object once and reusing it.

function updateDB(param_jan4, param_seid) {
var update = Server.CreateObject("ADODB.Command");
update.ActiveConnection = MM_lvcc_STRING;
update.CommandText = update.CommandText = "UPDATE tblJanuary SET Jan4 = '" + param_jan4 + "' WHERE seid = '" + param_seid + "'";
update.CommandType = 1;
update.CommandTimeout = 0;
update.Prepared = true;
update.Execute();
}

That's not creating a new connection each time. A new command object - which isn't explicitly destroyed once it's finished with - but the connection does get reused.

50-100 records at a time is nothing. Even for Access.

Personally, I wouldn't bother with the Command object. Just:

function updateDB(param_jan4, param_seid) {
strSQL = "UPDATE tblJanuary SET Jan4 = '" + param_jan4 + "' WHERE seid = '" + param_seid + "'";
MM_lvcc_STRING.Execute();
}

But I really don't think those two recordsets being open are helping matters. Open the recordset, get the data out, close the recordset and THEN start looping through the data or whatever it is you're doing with it. Don't work with live recordsets. Have a look at the Recordset.GetRows() (http://www.devguru.com/technologies/ado/8678.asp) function.

Oh, and BTW anytime you do this:

%>
<%

You're wasting time. Jumping in and out of ASP is hard work for your server. Doing it without having anything in between is pointless.

landslide

03-19-2007, 04:51 PM

I changed the update statment but i Am still getting the error . Any other ideas what can be cauing this in my code? Thanks in advance

landslide

03-20-2007, 08:01 PM

Anyone have any ideas on this?

Basscyst

03-21-2007, 01:43 AM

That's not creating a new connection each time. A new command object - which isn't explicitly destroyed once it's finished with - but the connection does get reused.

50-100 records at a time is nothing. Even for Access.

Personally, I wouldn't bother with the Command object. Just:

function updateDB(param_jan4, param_seid) {
strSQL = "UPDATE tblJanuary SET Jan4 = '" + param_jan4 + "' WHERE seid = '" + param_seid + "'";
MM_lvcc_STRING.Execute();
}

But I really don't think those two recordsets being open are helping matters. Open the recordset, get the data out, close the recordset and THEN start looping through the data or whatever it is you're doing with it. Don't work with live recordsets. Have a look at the Recordset.GetRows() (http://www.devguru.com/technologies/ado/8678.asp) function.

Oh, and BTW anytime you do this:

%>
<%

You're wasting time. Jumping in and out of ASP is hard work for your server. Doing it without having anything in between is pointless.

Maybe you are right, but it seems to me he is creating a new instance of the object with each function call, I'm just guessing that ACCESS is thinking it is reaching it's max number of simultanious connections as that is what the description of the error seems to point to.

Try creating the object outside the function, then it would be being reused, AFAIK.

ghell

03-22-2007, 06:31 PM

Since its an ADODB.Command and has a prepared attribute set to True, it may be best to prepare the statement once and pass it different parameters each time.

See http://msdn2.microsoft.com/en-us/library/aa905910(SQL.80).aspx

I have never used prepared commands in ASP with MS Access but I assume it is possible and more efficient. This should also defeat sql injection.

landslide

03-23-2007, 03:16 AM

Sorry im really new at this so im sure if I can write that. Can some one give me an example of what I should change in my code? That would be the most helpful. :(

ghell

03-23-2007, 01:10 PM

If you just use normal ADODB connections and recordsets without commands using VBScript, it will look something like this:
<%
' Create and open the connection.
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "..."

' Simple example of reading from the database
Set rsRead = objConn.Execute("SELECT ... FROM ... WHERE ... ORDER BY ...")
' here you can use a do-while loop with a rsRead.movenext, or GetRows which is better.
Set rsRead = Nothing

' Example of writing to the database
objConn.Execute("UPDATE ... SET ... = ... WHERE ...")

' Close and destroy the connection as soon as you are done with it
objConn.Close
Set objConn = Nothing
%>So, if you have the objConn object already defined globally, you just need to call objConn.Execute("UPDATE .... ") in your updatedb function.

An example of prepared statements was given in the link I posted last time (but you have to translate it from VB)

landslide

03-23-2007, 06:57 PM

Can anyone give me an idea how it would look like in javascript?

ghell

03-23-2007, 10:07 PM

Hardly anyone writes ASP in JavaScript, but it won't look much different I expect. Nothing probably changes to null, there are curly braces around the place and use of "new"

There's no advantage to using javascript anyway, why are you using it?