arendt73
asked on
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
ENTIRE PAGE
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
%>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"> </td>
<td> </td>
</tr>
<tr>
<td> </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 "</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">
<input type="hidden" name="MM_update" value="form1">
</p></td>
</tr>
<tr>
<td height="20"> </td>
</tr>
</table>
<% End If ' end Not Recordset1.EOF Or NOT Recordset1.BOF %>
</form>
</td>
</tr>
</table></td>
</tr>
</table>
</body>
</html>
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 ?
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 ?
ASKER
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
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")
I have again included the updated code (update of table) for review and suggestion. Thanks for the assistance.
<input name="cmNotes<%=Recordset1
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")
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
%>
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")
mquery="update tpaorders set processed='Yes', Status='Accepted', cmNotes='" & Request.Form("cmNotes" & mrec) & "', Who='"&Request.Form("Who")
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(
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.
So, line 220 of your page should be this instead:
<td height="25"><input id="cmNotes_<%=Recordset1(
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.
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("
<td height="25"><input id="cmNotes<%=Recordset1("
ASKER
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.
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.
ASKER
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
%>
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")
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.
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")
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.
ASKER
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.
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.
Line 91. Just replace the single mquery line with the above lines.
ASKER
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
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
ASKER
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
%>
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.]
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
%>ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Partial solutions given but assisted with resolving main issue.
can you write out the sql produced and then paste it here, so we can see if its "malformed"