troubleshooting Question

Update/Insert query help

Avatar of AmitavaCh
AmitavaChFlag for India asked on
Microsoft AccessASPHTML
7 Comments1 Solution660 ViewsLast Modified:
Hi!
I have developed a screen to capture my team member's attendance, where they need to log in, check the details and select the appropriate options (in Remarks field) & update records.

All these fields are database driven and only 'Remarks' fields are editable. Though it is working fine to some extent, I would like to add few functionality in the form. For example, if the Status is 'P', Remarks fields should automatically populate Present (value 'P' in DB) and Allow select only in case the status fields are 'S' or 'A'.

Secondly, whenever anyone update, remarks fields should also display the value, instead of 'select' all the time.

Can you help me please.

****

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
If Request("Submit") <> "" Then
      intRecIDs = Replace(Request("hidRecIDs"), "*", "")  ' remove all the asterisks, to create a list like this: 2, 5, 8, 9 etc.
      arrRecIDs = Split(intRecIDs, ", ")                        ' Create an array, wich will contain just the IDs of the records we need to update
      For i = 0 to Ubound(arrRecIDs)                              ' Loop trough the array

            strTexa = Replace(Request("txtRem1" & arrRecIDs(i)), "'", "''")
            strTexb = Replace(Request("txtRem2" & arrRecIDs(i)), "'", "''")
            strTexc = Replace(Request("txtRem3" & arrRecIDs(i)), "'", "''")

            set commUpdate = Server.CreateObject("ADODB.Command")
            commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("DB/AttendanceT.mdb") & ";Persist Security Info=False"
            commUpdate.CommandText = "UPDATE AttendanceT  SET RecRem1 = '" & strTexa & "', RecRem2 = '" & strTexb & "', RecRem3 = '" & strTexc & "' WHERE RecID = " & arrRecIDs(i)
            commUpdate.CommandType = 1
            commUpdate.CommandTimeout = 0
            commUpdate.Prepared = true
            commUpdate.Execute()
      Next
      strMessage = i & " Records Updated"
      Response.Redirect("ExpExchange.asp?Message=" & strMessage)
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("DB/AttendanceT.mdb") & ";Persist Security Info=False"
Recordset1.Source = "SELECT RecID, RecCode, city, RecName, RecDep, RecIN1, RecOut1, RecHrs1, RecSta1, RecRem1, RecIN2, RecOut2, RecHrs2, RecSta2, RecRem2, RecIN3, RecOut3, RecHrs3, RecSta3, RecRem3 FROM AttendanceT where RecCode='10409' ORDER BY RecID ASC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Attendance Management</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<LINK href="../../../../emaar.css" type=text/css rel=stylesheet>
<script language="JavaScript">
<!--
//  When the value in a textfield is changed, notice the onChange="RecUpdate('<%= intRecID %>')"
//  on each of the textfields,  the value of the Record ID associated with that field
//  is passed to the RecUpdate function. First the value is surounded with 2 asterisks e.g. *6*
//  This is so that *1* can be distinguished from *10*, *11* etc.
function RecUpdate(RecID){
var ThisID = "*" + (RecID) + "*"
if (document.form1.hidRecIDs.value == ""){      // If the hidden field is empty
document.form1.hidRecIDs.value = (ThisID)      // Store the value in the hidden field (hidRecIDs) as it is.
}
if (document.form1.hidRecIDs.value != ""){  // If the hidden field isn't empty
var str = document.form1.hidRecIDs.value;      // Store the contents of the hidden field in the variable str
var pos = str.indexOf(ThisID);                        // Search str to see if this RecID is allready in it.
if (pos == -1) {                                          // If the position returned is -1 it isn't allredy in there,  
document.form1.hidRecIDs.value = document.form1.hidRecIDs.value + ", " + (ThisID)  
}                                                                   // so add ", " and this ID to what is already in hidRecIDs
}                                                                  // to create a list like this *2*, *5*, *8* etc.      
}
//-->
</script>
</head>
<body>
<script language="JavaScript1.2">dqm__codebase = "../../../../" //script folder location</script>
<font size="2" face="Arial, Helvetica, sans-serif"><%= Request.QueryString("Message") %></font>
<form name="form1" method="post" action="ExpExchange.asp">
<table width="774" border="0" cellpadding="0" cellspacing="0">
      <%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<% intRecID =(Recordset1.Fields.Item("RecID").Value) ' Store the current RecordID in a variable %>  <tr>
    <td colspan="6" bgcolor="#FFCC99">
      <p align="right"><font class="label" size="2" face="Arial">
                        <font class="label" size="2" color="#FF0000" face="Arial"><a href="javascript:history.go(-1)" orgurl="javascript:history.go(-1)" class="Mouseover">Back</a></font></td>
  </tr>
            <tr>
    <td colspan="6" bgcolor="#FFCC99">&nbsp;<font size="2" face="Arial"> Card No.:<%= intRecID %><input name="hidRecID<%= intRecID %>" type="hidden" value="<%= intRecID %>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
      <!--a href="javascript:pop1()" alt="Password Change" class="mouseover">Change Password</a--></font></td>
  </tr>
  <tr>
    <td colspan="6" bgcolor="#FFCC99">&nbsp;<font face="Arial" size="2"> Name:<b> &nbsp;&nbsp;&nbsp;&nbsp;</b></font><font size="1" face="Arial, Helvetica, sans-serif"><input name="txtName<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecName").Value)%>" size="25" style="border-style:solid; border-width:0; font-family: Arial; font-size: 8pt; padding-left:4px; padding-right:4px; padding-top:1px; padding-bottom:1px; background-color:#FFCC99"></font><b><font face="Arial" size="2">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </b>
      Employee Code:<b>&nbsp;&nbsp; </b> </font><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtCode<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecCode").Value)%>" size="5" style="border-style:solid; border-width:0; font-family: Arial; font-size: 8pt; padding-left:4px; padding-right:4px; padding-top:1px; padding-bottom:1px; background-color:#FFCC99">&nbsp;&nbsp;&nbsp;&nbsp;</font><font face="Arial" size="2">Department:<b> </b> </font><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtDep<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecDep").Value)%>" size="20" style="border-style:solid; border-width:0; font-family: Arial; font-size: 8pt; padding-left:4px; padding-right:4px; padding-top:1px; padding-bottom:1px; background-color:#FFCC99"></font></td>
  </tr>
  <tr>
    <!--td colspan="6" height="22" bgcolor="#FFCC99"><b><font face="Arial" size="2"> &nbsp;
      COO:</font></b><font size="1" face="Arial, Helvetica, sans-serif"><input name="txtCoo<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("city").Value)%>" size="25" style="border-style:solid; border-width:0; font-family: Arial; font-size: 8pt; padding-left:4px; padding-right:4px; padding-top:1px; padding-bottom:1px; background-color:#FFCC99"></font></td-->
  </tr>

  <tr>
    <td align="center" width="253" bgcolor="#CCFFCC">
      <font size="1" face="Arial">Date</font></td>
    <td align="center" width="72" bgcolor="#CCFFCC">
      <font size="1" face="Arial">IN</font></td>
    <td align="center" width="55" bgcolor="#CCFFCC">
      <font size="1" face="Arial">OUT</font></td>
    <td align="center" width="50" bgcolor="#CCFFCC">
      <font size="1" face="Arial">Hrs.</font></td>
    <td align="center" width="50" bgcolor="#CCFFCC">
      <font size="1" face="Arial">Status</font></td>
    <td align="center" width="294" bgcolor="#CCFFCC">
      <font size="1" face="Arial">Remarks</font></td>
  </tr>
  <tr>
    <td align="center" width="253" bgcolor="#CCFFCC">&nbsp;</td>
    <td align="center" width="72" bgcolor="#CCFFCC">&nbsp;</td>
    <td align="center" width="55" bgcolor="#CCFFCC">&nbsp;</td>
    <td align="center" width="50" bgcolor="#CCFFCC">&nbsp;</td>
    <td align="center" width="50" bgcolor="#CCFFCC">&nbsp;</td>
    <td align="center" width="294" bgcolor="#CCFFCC">&nbsp;</td>
  </tr>
  <tr>
    <td width="253" bgcolor="#CCFFCC">
      <font size="1" face="Arial">&nbsp; Monday, January 16, 2012</font></td>
    <td width="72" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtIn1<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecIN1").Value)%>" size="8" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC"></font></td>
    <td width="55" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtOut1<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecOut1").Value)%>" size="6" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC"></font></td>
    <td width="50" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtHrs1<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecHrs1").Value)%>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC"></font></td>
    <td width="50" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtSta1<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecSta1").Value)%>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC; text-align:center"></font></td>
    <td width="294" align="center" bgcolor="#CCCC00">
      <font color="#FFFFFF" face="Arial" size="2">
             <SELECT name="txtRem1<%= intRecID %>" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecRem1").Value)%>">
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="Select">Select</option>
                  <Option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Present
                  <Option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="L">Leave
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="Half-day">Half-day
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Forgot to Carry Card
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Forgot to swipe card
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Came Late
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Left Early
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Site Visit
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Meeting
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Outdoor/Travel
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="A">Absent
                  </SELECT></font></td>
  </tr>
   <tr>
    <td width="253" bgcolor="#CCFFCC">
      <font size="1" face="Arial">&nbsp; Tuesday, January 17, 2012</font></td>
    <td width="72" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtIn2<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecIN2").Value)%>" size="8" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC"></font></td>
    <td width="55" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtOut2<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecOut2").Value)%>" size="6" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC"></font></td>
    <td width="50" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtHrs2<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecHrs2").Value)%>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC"></font></td>
    <td width="50" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtSta2<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecSta2").Value)%>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC; text-align:center"></font></td>
    <td width="294" align="center" bgcolor="#CCCC00">
      <font color="#FFFFFF" face="Arial" size="2">
             <SELECT name="txtRem2<%= intRecID %>" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecRem2").Value)%>">
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="Select">Select</option>
                  <Option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Present
                  <Option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="L">Leave
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="Half-day">Half-day
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Forgot to Carry Card
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Forgot to swipe card
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Came Late
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Left Early
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Site Visit
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Meeting
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Outdoor/Travel
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="A">Absent
                  </SELECT></font></td>
  </tr>
   <tr>
    <td width="253" bgcolor="#CCFFCC">
      <font size="1" face="Arial">&nbsp; Wednesday, January 18, 2012</font></td>
    <td width="72" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtIn3<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecIN3").Value)%>" size="8" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC"></font></td>
    <td width="55" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtOut3<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecOut3").Value)%>" size="6" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC"></font></td>
    <td width="50" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtHrs3<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecHrs3").Value)%>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC"></font></td>
    <td width="50" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
            <input name="txtSta3<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecSta3").Value)%>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC; text-align:center"></font></td>
    <td width="294" align="center" bgcolor="#CCCC00">
      <font color="#FFFFFF" face="Arial" size="2">
             <SELECT name="txtRem3<%= intRecID %>" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecRem3").Value)%>">
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="Select">Select</option>
                  <Option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Present
                  <Option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="L">Leave
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="Half-day">Half-day
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Forgot to Carry Card
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Forgot to swipe card
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Came Late
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Left Early
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Site Visit
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Meeting
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="P">Outdoor/Travel
                  <option type=select-one onChange="RecUpdate('<%= intRecID %>')" value="A">Absent
                  </SELECT></font></td>
  </tr>
  <tr>
    <td width="774" colspan="6"><b>&nbsp; </b></td>
  </tr>
      <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>
</table>
<br>
<input name="hidRecIDs" type="text" size="40">
<font size="2" face="Arial, Helvetica, sans-serif">Selected Records</font><br>
<br>
<font size="2" face="Arial, Helvetica, sans-serif">
<input type="submit" name="Submit" value="Update">
</font>
<input name="hidRecID<%= intRecID %>" type="hidden" value="<%= intRecID %>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC">
</form>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
ExpExchange.zip
AttendanceT.mdb
ExpExchange.txt
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros