[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

Information in textfield does not update record

I am having issues updating a field within records queried.  The text typed into the textfield (cmNotes) does not update the cmNotes field in the Access table.

I have attached the query and update portion of my code. I admit that the code looks awful, but functions. I am willing to take any suggestions in improving the query and update portion.

The second attached code contains the entire results page. You will see that the records returned from the query are in a repeating region.

Any assistance with my update issue or possible rewrite of the query/update section is greatly appreciated.

Thank you!

QUERY & UPDATE

<%@LANGUAGE="VBSCRIPT"%> 
<%
Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%

If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then
MM_editRedirectUrl = "thankyoucm.asp"


  If (Not MM_abortEdit) Then
    ' execute the update
	' Bill's code below - start of
dat=cstr(request.form("processed"))
mres=split(dat,",")
mrec=""
mrec1=""
for t=lbound(mres) to ubound(mres)
	dt=trim(mres(t))
	if left(dt,3)="Yes" then
	mrec=mrec+mid(dt,5)
		if t<>ubound(mres) then
		mrec=mrec+","
		end if
	end if
	if left(dt,3)="Rej" then
	mrec1=mrec1+mid(dt,5)
'	if t<>ubound(mres) then
		mrec1=mrec1+","
'		end if
	end if	
next

mresx=split(mrec1,",")
h=ubound(mresx) 
if h>0 then
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders.mdb"
for t=lbound(mresx) to h
'Response.Write mresx(t)
	if mresx(t)<>"" then
	tp=Trim(Request.Form("RejectedExp"+mresx(t)))
	tpx=tp
	if tp="0" then
		tpx=Request.Form("TextExp"+mresx(t))
	end if
	MM_editCmd.CommandText ="update tpaorders set rejected='SUBMITTED ORDER REJECTED',processed='Yes', Status='Rejected', RejectedExp='"+tpx+"', cmNotes='"&Request.Form("cmNotes"+mresx(t))&"', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID in ("+mresx(t)+")"
	'response.write "<br>"&MM_editCmd.CommandText&"<br>"
	MM_editCmd.Execute
	end if	
next
MM_editCmd.ActiveConnection.Close

end if

mquery="update tpaorders set processed='Yes', Status='Accepted', cmNotes='"&Request.Form("cmNotes")&"', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID in ("+mrec+")"	
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders.mdb"
	If mrec<>"" Then 
    MM_editCmd.CommandText = mquery 'MM_editQuery
    MM_editCmd.Execute
	End If

    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders.mdb"
Recordset1.Source = "SELECT *  FROM tpaorders  WHERE mid(Boss1, 1, 3) = '"&Request.Form("Boss1")&"' AND mid(CaseNumber1, 4, 2) BETWEEN'"&Request.Form("StartRange")&"' AND '"&Request.Form("EndRange")&"' AND Processed Is Null"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window



ENTIRE PAGE

<%@LANGUAGE="VBSCRIPT"%> 
<%
Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%

If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then
MM_editRedirectUrl = "thankyoucm.asp"


  If (Not MM_abortEdit) Then
    ' execute the update
	' Bill's code below - start of
dat=cstr(request.form("processed"))
mres=split(dat,",")
mrec=""
mrec1=""
for t=lbound(mres) to ubound(mres)
	dt=trim(mres(t))
	if left(dt,3)="Yes" then
	mrec=mrec+mid(dt,5)
		if t<>ubound(mres) then
		mrec=mrec+","
		end if
	end if
	if left(dt,3)="Rej" then
	mrec1=mrec1+mid(dt,5)
'	if t<>ubound(mres) then
		mrec1=mrec1+","
'		end if
	end if	
next

mresx=split(mrec1,",")
h=ubound(mresx) 
if h>0 then
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders.mdb"
for t=lbound(mresx) to h
'Response.Write mresx(t)
	if mresx(t)<>"" then
	tp=Trim(Request.Form("RejectedExp"+mresx(t)))
	tpx=tp
	if tp="0" then
		tpx=Request.Form("TextExp"+mresx(t))
	end if
	MM_editCmd.CommandText ="update tpaorders set rejected='SUBMITTED ORDER REJECTED',processed='Yes', Status='Rejected', RejectedExp='"+tpx+"', cmNotes='"&Request.Form("cmNotes"+mresx(t))&"', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID in ("+mresx(t)+")"
	'response.write "<br>"&MM_editCmd.CommandText&"<br>"
	MM_editCmd.Execute
	end if	
next
MM_editCmd.ActiveConnection.Close

end if

mquery="update tpaorders set processed='Yes', Status='Accepted', cmNotes='"&Request.Form("cmNotes")&"', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID in ("+mrec+")"	
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders.mdb"
	If mrec<>"" Then 
    MM_editCmd.CommandText = mquery 'MM_editQuery
    MM_editCmd.Execute
	End If

    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders.mdb"
Recordset1.Source = "SELECT *  FROM tpaorders  WHERE mid(Boss1, 1, 3) = '"&Request.Form("Boss1")&"' AND mid(CaseNumber1, 4, 2) BETWEEN'"&Request.Form("StartRange")&"' AND '"&Request.Form("EndRange")&"' AND Processed Is Null"
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>Case Manager eOrders update</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="Upload.css" rel="stylesheet" type="text/css">

<script language="javascript">
function chk(c,n){
  var ib = document.getElementById("cmNotes_"+n);
  if (!c.checked) ib.value="";
  ib.style.display=(c.checked)?"":"none";
}

function Handle_Required(val){
	str='document.form1.RejectedExp'+val.substr(4);
	itm=eval(str);
	if (val.substr(0,3) == "Rej"){
		itm.style.visibility = 'visible';
		itm.value="";//reset field;
	}
	else{
		itm.style.visibility ='hidden';
	}
}

function Handle_Explanation(val,mid){
var required = eval('document.form1.TextExp'+mid);
if (val == "0"){
required.style.visibility = 'visible';
required.value="";//reset field;
}
else{
required.style.visibility ='hidden';
	}
}

function apply()
{
  document.form1.submit.disabled=true;
  if(document.form1.chk.checked==true)
  {
    document.form1.submit.disabled=false;
  }
  if(document.form1.chk.checked==false)
  {
    document.form1.submit.enabled=false;
  }
}
</script>

</head>

<body leftmargin="0" rightmargin="0" topmargin="0">
<table width="100%" border="0" cellpadding="0" cellspacing="0" class="text-medium">
  <tr> 
    <td height="50" bgcolor="#000066" class="text-large">
<div align="center"><span class="text-xlarge"><strong><font color="#FFFFFF">ORDERS 
        UPDATE</font></strong></span></div></td>
  </tr>
  <tr> 
    <td><table width="100%" border="0" cellpadding="0" cellspacing="0" class="text-medium">
        <tr> 
          <td width="10">&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
        <tr> 
          <td>&nbsp;</td>
          <td><form name="form1" method="post" action="">
		  	<% 
			While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) 
			%>
              <table width="800" border="0" cellpadding="0" cellspacing="0" class="text-medium">
                <tr> 
                  <td width="800"> <table width="800" border="0" cellpadding="0" cellspacing="0" class="text-large">
                      <tr> 
                        <td width="170" height="28"><strong>Submitted By: 
                          <input type="hidden" name="MM_recordId" value="<%= Recordset1.Fields.Item("ID").Value %>">
                          </strong></td>
                        <td height="28"><%=(Recordset1.Fields.Item("YourName").Value)%></td>
                      </tr>
                      <tr> 
                        <td height="28"><strong>Date Submitted:</strong></td>
                        <td height="28"><%=(Recordset1.Fields.Item("SubDate").Value)%></td>
                      </tr>
                      <tr> 
                        <td height="28"><strong>Case Number:</strong></td>
                        <td height="28"><%=(Recordset1.Fields.Item("CaseYear1").Value)%>-<%=(Recordset1.Fields.Item("CaseNumber1").Value)%></td>
                      </tr>
                      <tr> 
                        <td height="28"><strong>Caption/Title:</strong></td>
                        <td height="28"><%=(Recordset1.Fields.Item("OrderCaption1").Value)%></td>
                      </tr>
                      <tr> 
                        <td height="28"><strong>Tracking Number:</strong></td>
                        <td height="28"><%=(Recordset1.Fields.Item("Tracking").Value)%></td>
                      </tr>
                      <tr> 
                        <td height="28"><strong>Proposed Order:</strong></td>
                        <td height="28" class="text-medium"><a href="<%=(Recordset1.Fields.Item("Link").Value)%>" target="_blank" class="text-medium">Review 
                          Order</a></td>
                      </tr>
                      <tr> 
                        <td height="30"><table width="170" height="30" border="0" cellpadding="0" cellspacing="0" class="text-medium">
                            <tr> 
                              <td><strong>Note to Supervisor:</strong></td>
                              <td width="40" valign="middle"><input type=checkbox onClick="chk(this,<%=Recordset1.Fields.Item("ID").Value %>)"></td>
                            </tr>
                          </table></td>
                        <td height="25"><input id=cmNotes_<%=Recordset1.Fields.Item("ID").Value %> name="cmNotes" type="text" class="text-medium" size="65" maxlength="65" style="display:none"></td>
                      </tr>
                      <tr> 
                        <td height="30"><strong>Status of Order:</strong></td>
                        <td height="25"> <select name="Processed" class="text-medium" id="Processed" onChange="Handle_Required(this.value);">
                            <option value="Sta-<%= Recordset1.Fields.Item("ID").Value %>">ORDER 
                            STATUS?</option>
                            <option value="Yes-<%= Recordset1.Fields.Item("ID").Value %>">ACCEPT 
                            ORDER</option>
                            <option value="Rej-<%= Recordset1.Fields.Item("ID").Value %>">REJECT 
                            ORDER</option>
                          </select></td>
                      </tr>
                    </table></td>
                </tr>
                <tr> 
                  <td height="25"><select name="RejectedExp<%=Recordset1.Fields.Item("ID")%>" class="text-medium" id="select2" style="visibility:hidden;" onChange="Handle_Explanation(this.value,'<%=Recordset1.Fields.Item("ID")%>');">
                      <option value="Order was rejected">ORDER REJECTED? PLEASE 
                      SELECT AN EXPLANATION BELOW.</option>
                      <option value="Item submitted is not in correct format">Item 
                      submitted is not in correct format</option>
                      <option value="Widow Clause - must include some text on signature page">Widow 
                      Clause - must include some text on signature page</option>
                      <option value="0">Select this option to enter/type a rejection 
                      below in the text area - DO NOT USE any characters such 
                      as ' . , or &quot;</option>
                    </select></td>
                </tr>
                <tr> 
                  <td height="25"><input name="TextExp<%=Recordset1.Fields.Item("ID")%>" type="text" id="TextExp<%=Recordset1.Fields.Item("ID")%>" style="visibility:hidden;" size="90" maxlength="90"></td>
                </tr>
                <tr> 
                  <td> <hr align="left" width="650" size="1"> </td>
                </tr>
                <tr> 
                  <td height="15" colspan="1"></td>
                </tr>
              </table>
			  <%
  			Repeat1__index=Repeat1__index+1
  			Repeat1__numRows=Repeat1__numRows-1
  			Recordset1.MoveNext()
  			Wend
			%>
			  <br>
              <% If Not Recordset1.EOF Or Not Recordset1.BOF Then %>
			  <table width="800" border="0" cellpadding="0" cellspacing="0" class="text-medium">
                <tr> 
                  <td height="20"><font color="#FF0000"><strong>PLEASE ENTER YOUR 
                    INTIALS BELOW BEFORE CLICKING THE UPDATE BUTTON</strong></font></td>
                </tr>
                <tr> 
                  <td height="30" valign="bottom"> <input name="Who" type="text" class="text-large" id="Who3" size="2" maxlength="3"> 
                  </td>
                </tr>
                <tr> 
                  <td height="30" valign="bottom">
<p>
                      <input name="Submit" type="submit" class="text-small" value="UPDATE REVIEWED ORDERS">
                      &nbsp; 
                      <input type="hidden" name="MM_update" value="form1">
                    </p></td>
                </tr>
                <tr> 
                  <td height="20">&nbsp;</td>
                </tr>
              </table>
			  <% End If ' end Not Recordset1.EOF Or NOT Recordset1.BOF %>
            </form>
            </td>
        </tr>
      </table></td>
  </tr>
</table>
</body>
</html>

Open in new window

0
arendt73
Asked:
arendt73
2 Solutions
 
Simon BallCommented:
Is it the two updates both not working - line 78 and line 87?

can you write out the sql produced and then paste it here, so we can see if its "malformed"
0
 
GundogTrainerCommented:
Its not easy to try and read through without access to a working example of the mdb file but here is a suggestion. I am making the ussumption that the update part that your refering to is the section in the "Entire Page" listing between line 87 & 95.
If you can insert this before line 91:
response.write "#<b>" & mquery & "</b>#"
(I tend to add a start and end marker just so I can be sure i dont get confused with anything else that is displayed)

This section will only run if there is both an update and if 'h' is greater than 0;
where 'h' is indirectly the number of items returned by the processed field minus 1

If there is no "," in the process field then spliting it will just creat a single item array(0) so ubound array would = 0 rather than be greater - could this be the issue ?


0
 
arendt73Author Commented:
The cmNotes field is now being updated in the table for REJECTED items only. I had to modify the cmNotes field in order for the update to work correctly. But it is only working for REJECTED items.

<input name="cmNotes<%=Recordset1.Fields.Item("ID").Value %>" id="cmNotes<%=Recordset1.Fields.Item("ID").Value %>"

I'm having issues with updating the cmNotes field if the ACCEPTED option is selected.  I am getting the following error:

Microsoft VBScript runtime  error '800a0009'

Subscript out of range: 't'

/orders/cm_review.asp, line 90


Line 90 is:

mquery="update tpaorders set processed='Yes', Status='Accepted', cmNotes='" & Request.Form("cmNotes" & mrec & "', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID in ("&mrec&")"

I have again included the updated code (update of table) for review and suggestion. Thanks for the assistance.

<%@LANGUAGE="VBSCRIPT"%> 
<%
Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%

If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then
MM_editRedirectUrl = "thankyoucm.asp"


  If (Not MM_abortEdit) Then
    ' execute the update
	' Bill's code below - start of
dat=cstr(request.form("processed"))
mres=split(dat,",")
mrec=""
mrec1=""
for t=lbound(mres) to ubound(mres)
	dt=trim(mres(t))
	if left(dt,3)="Yes" then
	mrec=mrec+mid(dt,5)
		if t<>ubound(mres) then
		mrec=mrec+","
		end if
	end if
	if left(dt,3)="Rej" then
	mrec1=mrec1+mid(dt,5)
'	if t<>ubound(mres) then
		mrec1=mrec1+","
'		end if
	end if	
next

mresx=split(mrec1,",")
h=ubound(mresx) 
if h>0 then
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders.mdb"
for t=lbound(mresx) to h
'Response.Write mresx(t)
	if mresx(t)<>"" then
	tp=Trim(Request.Form("RejectedExp"+mresx(t)))
	tpx=tp
	if tp="0" then
		tpx=Request.Form("TextExp"+mresx(t))
	end if
	MM_editCmd.CommandText ="update tpaorders set rejected='SUBMITTED ORDER REJECTED',processed='Yes', Status='Rejected', RejectedExp='" & tpx & "', cmNotes='" & Request.Form("cmNotes" & mresx(t)) & "', Who='" & Request.Form("Who") & "', ViewDate=now(), ViewDate1=date() where ID in ("&mresx(t)&")"
	
	'response.write MM_editCmd.CommandText 
	'response.end
	
	MM_editCmd.Execute
	end if	
next
MM_editCmd.ActiveConnection.Close

end if

mquery="update tpaorders set processed='Yes', Status='Accepted', cmNotes='" & Request.Form("cmNotes" & mrec & "', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID in ("&mrec&")"	
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders.mdb"
	If mrec<>"" Then 
    MM_editCmd.CommandText = mquery 'MM_editQuery
    
	'response.write mquery 
	'response.end
	
	MM_editCmd.Execute
	End If
	
	MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
worthyking1Commented:
You're missing a closing parenthesis on your cmNotes value. Your correct query should be:

mquery="update tpaorders set processed='Yes', Status='Accepted', cmNotes='" & Request.Form("cmNotes" & mrec) & "', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID in ("&mrec&")"
0
 
worthyking1Commented:
Also, you have the input field named incorrectly in your html code (line 220 of original posted full page code). You're naming it just CMNotes but then trying to access the request.form value with the ID on the end. ASP does not access request object items based upon their html id, but their name.

So, line 220 of your page should be this instead:

                        <td height="25"><input id="cmNotes_<%=Recordset1("ID")%>" name="cmNotes_<%=Recordset1("ID")%>" type="text" class="text-medium" size="65" maxlength="65" style="display:none"></td>

BTW, you don't need the Recordset1.fields.item crap.  I would highly recommend learning to code your own database queries rather than using the DW created code...it ads so much crap that you just don't need. Your above code could be done in probably half as many lines of code, if not less.
0
 
worthyking1Commented:
Sorry, looks like you're not using the underscore anymore since you posted the first page, so that line would be this instead:

                        <td height="25"><input id="cmNotes<%=Recordset1("ID")%>" name="cmNotes<%=Recordset1("ID")%>" type="text" class="text-medium" size="65" maxlength="65" style="display:none"></td>
0
 
arendt73Author Commented:
Thank you for the suggested recommendations.  My issue is now with ACCEPTED records and the cmNotes field containing text.  If the first record is ACCEPTED and the cmNotes field contains text, the record is updated in the table.

However, if other records are ACCEPTED and contain text, then these records are updated, but the cmNotes field are blank in the table. Only the first ACCEPTED record with cmNotes is updated/written to the table.

There are times when a record is ACCEPTED that the cmNotes fields may be blank, and other times it may contain text.  But right now, only the first ACCEPTED record updates correctly.

0
 
arendt73Author Commented:
Attached is the updated code for review and suggested changes.

<%@LANGUAGE="VBSCRIPT"%> 
<%
Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%

If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then
MM_editRedirectUrl = "thankyoucm.asp"

  If (Not MM_abortEdit) Then
    ' execute the update
	' Bill's code below - start of
dat=cstr(request.form("processed"))
mres=split(dat,",")
mrec=""
mrec1=""
for t=lbound(mres) to ubound(mres)
	dt=trim(mres(t))
	if left(dt,3)="Yes" then
	mrec=mrec+mid(dt,5)
		if t<>ubound(mres) then
		mrec=mrec+","
		end if
	end if
	if left(dt,3)="Rej" then
	mrec1=mrec1+mid(dt,5)
'	if t<>ubound(mres) then
		mrec1=mrec1+","
'		end if
	end if	
next

mresx=split(mrec1,",")
h=ubound(mresx) 
if h>0 then
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders100.mdb"
for t=lbound(mresx) to h
'Response.Write mresx(t)
	if mresx(t)<>"" then
	tp=Trim(Request.Form("RejectedExp"+mresx(t)))
	tpx=tp
	if tp="0" then
		tpx=Request.Form("TextExp"+mresx(t))
	end if

	MM_editCmd.CommandText ="update tpaorders set rejected='SUBMITTED ORDER REJECTED',processed='Yes', Status='Rejected', RejectedExp='" & tpx & "', cmNotes='" & Request.Form("cmNotes" & mresx(t)) & "', Who='" & Request.Form("Who") & "', ViewDate=now(), ViewDate1=date() where ID in ("&mresx(t)&")"
	
	'response.write MM_editCmd.CommandText 
	'response.end
	
	MM_editCmd.Execute
	end if	
next
MM_editCmd.ActiveConnection.Close

end if

	'mquery="update tpaorders set processed='Yes', Status='Accepted', cmNotes='" & Request.Form("cmNotes" & mrec) & "', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID in ("&mrec&")"	
    mquery="update tpaorders set processed='Yes', Status='Accepted', cmNotes='" & Request.Form("cmNotes" & mrec) & "', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID in ("&mrec&")" 
	Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders100.mdb"
	If mrec<>"" Then 
    MM_editCmd.CommandText = mquery 'MM_editQuery
    
	'response.write mquery 
	'response.end
	
	MM_editCmd.Execute
	End If
	
	MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>

Open in new window

0
 
worthyking1Commented:
Let me get this straight: you're doing a bulk update of multiple records with the same data in all of them, but you want different values to be updated in the cmNotes column?

If you want different data for each record in the cmNotes column then you need to have separate update statements, not just one. Do a loop like you do with the rejected orders or concatenate a query (as long as you don't process large quantities of records it won't affect performance).

Like so:

arrMrec=split(mrec,",")
for i = 0 to ubound(arrMrec)
    mquery=mquery & "update tpaorders set processed='Yes', Status='Accepted', cmNotes='" & Request.Form("cmNotes" & arrMrec(i)) & "', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID = "& arrMrec(i) &";"
next

If you're going to process  thousands of orders at a time then the above string concatenation of mquery will slow the server down, but for a few dozen or even a hundred at a time you won't notice it.
0
 
arendt73Author Commented:
If I understand your question correctly, yes, I will will many orders at one time.  But never reaching hundreds or thousands.  Maybe 20 or 30 at the most at one time.

If an order is ACCEPTED, the user may either enter a comment in the cmNotes field or may not.  It depends on the information (record).  

Likewise, if an order is REJECTED, the user again may either enter a comment into the cmNotes field or may not.

I will attempt your suggestion.  What line should I insert your suggestion?  What should be taken out?

Thank you.
0
 
worthyking1Commented:
Line 91. Just replace the single mquery line with the above lines.
0
 
arendt73Author Commented:
I'm getting the following error after applying your code:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.

/orders/cm_review.asp, line 105

Line 105 is:       MM_editCmd.Execute
0
 
arendt73Author Commented:
I have attached the current code with suggested recommendations.

 
<%@LANGUAGE="VBSCRIPT"%> 
<%
Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%

If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then
MM_editRedirectUrl = "thankyoucm.asp"

  If (Not MM_abortEdit) Then
    ' execute the update
	' Bill's code below - start of
dat=cstr(request.form("processed"))
mres=split(dat,",")
mrec=""
mrec1=""
for t=lbound(mres) to ubound(mres)
	dt=trim(mres(t))
	if left(dt,3)="Yes" then
	mrec=mrec+mid(dt,5)
		if t<>ubound(mres) then
		mrec=mrec+","
		end if
	end if
	if left(dt,3)="Rej" then
	mrec1=mrec1+mid(dt,5)
'	if t<>ubound(mres) then
		mrec1=mrec1+","
'		end if
	end if	
next

mresx=split(mrec1,",")
h=ubound(mresx) 
if h>0 then
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders100.mdb"
for t=lbound(mresx) to h
'Response.Write mresx(t)
	if mresx(t)<>"" then
	tp=Trim(Request.Form("RejectedExp"+mresx(t)))
	tpx=tp
	if tp="0" then
		tpx=Request.Form("TextExp"+mresx(t))
	end if

	MM_editCmd.CommandText ="update tpaorders set rejected='SUBMITTED ORDER REJECTED',processed='Yes', Status='Rejected', RejectedExp='" & tpx & "', cmNotes='" & Request.Form("cmNotes" & mresx(t)) & "', Who='" & Request.Form("Who") & "', ViewDate=now(), ViewDate1=date() where ID in ("&mresx(t)&")"
	
	'response.write MM_editCmd.CommandText 
	'response.end
	
	MM_editCmd.Execute
	end if	
next
MM_editCmd.ActiveConnection.Close

end if


arrMrec=split(mrec,",")
for i = 0 to ubound(arrMrec)
    mquery=mquery & "update tpaorders set processed='Yes', Status='Accepted', cmNotes='" & Request.Form("cmNotes" & arrMrec(i)) & "', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID = "& arrMrec(i) &";" 
next

    'mquery="update tpaorders set processed='Yes', Status='Accepted', cmNotes='" & Request.Form("cmNotes" & mrec) & "', Who='"&Request.Form("Who")&"', ViewDate=now(), ViewDate1=date() where ID in ("&mrec&")" 
	Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders100.mdb"
	If mrec<>"" Then 
    MM_editCmd.CommandText = mquery 'MM_editQuery
    
	'response.write mquery 
	'response.end
	
	MM_editCmd.Execute
	End If
	
	MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>

Open in new window

0
 
worthyking1Commented:
Oh, you're using MS Access rather than SQL then? Ouch! That's so 1990's ;)

Anyway, Access does not support multiple sql statements in a single query so I guess you'll have to execute each query one at a time. See the code below (had to paste the whole thing because it changes quite a few lines between 90 to 106.

I commented the code liberally so you can follow along.

[BTW, you really should learn to code if you're gonna be in the line of work that you apparently are.]




     
<%@LANGUAGE="VBSCRIPT"%> 
<%
Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%

If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then
MM_editRedirectUrl = "thankyoucm.asp"

  If (Not MM_abortEdit) Then
    ' execute the update
	' Bill's code below - start of
dat=cstr(request.form("processed"))
mres=split(dat,",")
mrec=""
mrec1=""
for t=lbound(mres) to ubound(mres)
	dt=trim(mres(t))
	if left(dt,3)="Yes" then
	mrec=mrec+mid(dt,5)
		if t<>ubound(mres) then
		mrec=mrec+","
		end if
	end if
	if left(dt,3)="Rej" then
	mrec1=mrec1+mid(dt,5)
'	if t<>ubound(mres) then
		mrec1=mrec1+","
'		end if
	end if	
next

mresx=split(mrec1,",")
h=ubound(mresx) 
if h>0 then
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders100.mdb"
for t=lbound(mresx) to h
'Response.Write mresx(t)
	if mresx(t)<>"" then
	tp=Trim(Request.Form("RejectedExp"+mresx(t)))
	tpx=tp
	if tp="0" then
		tpx=Request.Form("TextExp"+mresx(t))
	end if

	MM_editCmd.CommandText ="update tpaorders set rejected='SUBMITTED ORDER REJECTED',processed='Yes', Status='Rejected', RejectedExp='" & tpx & "', cmNotes='" & Request.Form("cmNotes" & mresx(t)) & "', Who='" & Request.Form("Who") & "', ViewDate=now(), ViewDate1=date() where ID in ("&mresx(t)&")"
	
	'response.write MM_editCmd.CommandText 
	'response.end
	
	MM_editCmd.Execute
	end if	
next
MM_editCmd.ActiveConnection.Close

end if

If mrec<>"" Then	' if accepted orders exist

	' open the db connection
	Set MM_editCmd = Server.CreateObject("ADODB.Command")
        MM_editCmd.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\Inetpub\wwwroot\orders\tpaorders100.mdb"

	' Loop through mrec ID's and update each accepted order
	arrMrec=split(mrec,",")	' split mrec on the commas into an array
	for i = 0 to ubound(arrMrec)	' loop thru the array
		' build the sql query
	        mquery= "update tpaorders set processed='Yes', Status='Accepted', cmNotes='" & _
				Request.Form("cmNotes" & arrMrec(i)) & "', Who='"&Request.Form("Who")& _
				"', ViewDate=now(), ViewDate1=date() where ID = "& arrMrec(i) &";" 
		' set the command text
    	        MM_editCmd.CommandText = mquery
		' execute the query
		MM_editCmd.Execute
	next
	
    ' close the connection
	MM_editCmd.ActiveConnection.Close

End If
	

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>

Open in new window

0
 
arendt73Author Commented:
Thank you for the detail.  I am encountering one issue where if an order is ACCEPTED, but no comments are in the cmNotes field, I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ID ='.

/orders/cm_review.asp, line 106


There may be times when an order is accepted, but no notes/comments are typed into the cmNotes field.
0
 
worthyking1Commented:
You probably have a trailing comma on the end of your mrec array because when building your arrays (lines 48-62) you're adding a comma if the UBound of mres has not been reached, but there could be a rejected order after the last accepted order in which case you would end up with a trailing comma and thus a blank array value which will blow out the update loop.  Same is true for your mrec1 array.

I would suggest checking this with a response.write after each, say at line 63...

response.write "mrec: " & mrec & "<br>mrec1: " & mrec1

check it with different page values ie. some rejected, some accepted and see what your vaslues look like. If you have trailing commas on either you need to trim them. Well, you need to trim them anyway to be sure (or change the logic in lines 48-62).

But a simple fix if that's the issue would be to check for, and trim if necessary, any trailing commas.

At line 63, do this:

If right(mrec,1) = "," then mrec = left(mrec,len(mrec)-1)
If right(mrec1,1) = "," then mrec1 = left(mrec1,len(mrec1)-1)
0
 
worthyking1Commented:
I also noticed (in your original page posted at the top) that your "chk" javascript function uses the underscore in the ID of the cmNotes field, but you have since changed the fieldname. Not sure if you updated your function to reflect that, but if not make sure you change the second line of that function to read:

  var ib = document.getElementById("cmNotes"+n);

instead of the old line which was:

  var ib = document.getElementById("cmNotes_"+n);

0
 
arendt73Author Commented:
Partial solutions given but assisted with resolving main issue.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now