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 14 of 14
  1. #1
    New Coder
    Join Date
    Mar 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Very Hard one please help

    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.
    Code:
    <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
    <!--#include file="../../../Connections/lvcc.asp" -->
    <%
    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">
    <!--
    
    function doCalcs(form1){
      count = form1.lvavalbl.value;
      for(x=0; x<document.getElementsByName('jan4').length; x++){
        document.getElementsByName('jan4')[x].value = (count > 0) ? 'A' : 'D'
        count --;
      }
    }
    //-->
    </script>
    <style type="text/css">
    <!--
    .style8 {
    	font-size: 24px;
    	font-weight: bold;
    }
    body {
    	background-color: #0099FF;
    }
    .style11 {font-weight: bold}
    -->
    </style>
    </head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>jan4</title>
    <style type="text/css">
    <!--
    .style7 {font-size: 9px}
    .style9 {font-size: 10px}
    .style10 {font-size: 12px}
    -->
    </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)%>&nbsp; <%=(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();
    %>

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    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?

  • #3
    SSJ
    SSJ is offline
    Regular Coder
    Join Date
    Mar 2007
    Posts
    230
    Thanks
    0
    Thanked 4 Times in 4 Posts
    currently locked by user 'admin' on machine 'server'
    where your page located?
    On the same machine(Server)?

  • #4
    Smokes a Lot
    Join Date
    Jul 2003
    Location
    CA, USA
    Posts
    1,594
    Thanks
    5
    Thanked 20 Times in 20 Posts
    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.
    Last edited by Basscyst; 03-15-2007 at 10:13 PM.
    Helping to build a bigger box. - Adam Matthews

  • #5
    New Coder
    Join Date
    Mar 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #6
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Quote Originally Posted by Basscyst View Post
    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.
    Quote Originally Posted by landslide View Post
    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:

    Code:
    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() function.

    Oh, and BTW anytime you do this:

    Code:
    %>
    <%
    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.

  • #7
    New Coder
    Join Date
    Mar 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #8
    New Coder
    Join Date
    Mar 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Anyone have any ideas on this?

  • #9
    Smokes a Lot
    Join Date
    Jul 2003
    Location
    CA, USA
    Posts
    1,594
    Thanks
    5
    Thanked 20 Times in 20 Posts
    Quote Originally Posted by Spudhead View Post
    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:

    Code:
    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() function.

    Oh, and BTW anytime you do this:

    Code:
    %>
    <%
    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.
    Helping to build a bigger box. - Adam Matthews

  • #10
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    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/lib...0(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.

  • #11
    New Coder
    Join Date
    Mar 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #12
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    If you just use normal ADODB connections and recordsets without commands using VBScript, it will look something like this:
    Code:
    <%
    ' 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)

  • #13
    New Coder
    Join Date
    Mar 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can anyone give me an idea how it would look like in javascript?

  • #14
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    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?


  •  

    Posting Permissions

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