arendt73
asked on
Record does not update after submission
I am trying to update individual records with a standard ASP update form. I am able to select the record I want to update on the selection page, but the update page does not update the record. When I enter/select my new information and hit submit, I am redirected to the Thank You page, but the record is not updated.
Below is the code for the update page for review. Any assistance is greatly appreciated in correcting the issue. Thank you.
Below is the code for the update page for review. Any assistance is greatly appreciated in correcting the issue. Thank you.
<%
Dim Recordset1
Dim Recordset1_numRows
pID=Request.Querystring("ID")
pIDX=Request.Form("IDX")
Set Recordset1 = Server.CreateObject("ADODB.recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\proc.mdb"
Recordset1.Source = "SELECT * FROM procedures WHERE proc_title = '"&Request.Form("proc_title")&"' " & pID
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
If request("Update") = "Update" Then
Set Recordset1 = Server.CreateObject("ADODB.connection")
Recordset1.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\proc.mdb"
sql=""
If Request.Form("proc_title_new") & "" <> "" Then
sql=sql & ",proc_title='" & Request.Form("proc_title_new") & "'"
End IF
If Request.Form("proc_file_new") & "" <> "" Then
sql=sql & ",proc_file='" & Request.Form("proc_file_new") & "'"
End IF
If Request.Form("date_effective") & "" <> "" Then
sql=sql & ",date_effective='" & Request.Form("date_effective_new") & "'"
End IF
If Request.Form("proc_info_new") & "" <> "" Then
sql=sql & ",proc_info='" & Request.Form("proc_info_new") & "'"
End IF
sql="UPDATE procedures SET " & Mid(sql, 2, 10000)
sql=sql & " WHERE ID = " & pIDX& ""
'response.write sql
rs.Execute sql
Response.Redirect "thankyou.htm"
ELSE
If request("Delete") = "Delete" Then
Set Recordset1 = Server.CreateObject("ADODB.connection")
Recordset1.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\procedures\db\proc.mdb"
sql="DELETE * FROM procedures"
sql=sql & " WHERE ID = " & pIDX & ""
Recordset1.Execute sql
Response.Redirect "thankyou.htm"
end if
end if
%>
<html>
<head>
<title>Procedures Submission Page</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="styles/web.css" rel="stylesheet" type="text/css">
<link rel="shortcut icon" type="image/x-icon" href="favicon.ico">
<script language="javascript" src="scripts/datetimepicker.js"></script>
<script language="javascript" src="scripts/additional.js"></script>
</head>
<body leftmargin="0" topmargin="0" rightmargin="0">
<table border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td height="65" bgcolor="#000000">
<div align="center"></div></td>
</tr>
<tr>
<td><div align="center">
<table width="600" border="0" cellpadding="0" cellspacing="0" class="text-large">
<tr>
<td><form action="thankyou.asp" method="post" name="form1">
<table width="600" border="0" cellpadding="0" cellspacing="0" class="text-large">
<tr>
<td> </td>
</tr>
<tr>
<td><div align="center"><strong><font color="#FF0000">MODIFICATIONS/UPDATE
PAGE</font></strong></div></td>
</tr>
<tr>
<td> </td>
</tr>
</table>
<table width="600" border="0" cellpadding="0" cellspacing="0" class="text-large">
<tr>
<td width="200" height="25"><font color="#000000"><strong>Current
Procedures Title</strong></font></td>
<td><font color="#000000"><%=(Recordset1.Fields.Item("proc_title").Value)%></font></td>
<td width="10"> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="25"><font color="#FF0000"><strong>New Title</strong></font></td>
<td><input name="proc_title_new" type="text" id="proc_title_new" size="55" maxlength="100"></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="25"><strong>Current PDF/Document</strong></td>
<td><font color="#000000"><%=(Recordset1.Fields.Item("proc_file").Value)%></font></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="25"><font color="#FF0000"><strong>New File to
be uploaded (PDF)</strong></font></td>
<td><input name="proc_file_new" type="file" id="proc_file_new" size="41"></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="25"><strong>Current Effective Date</strong></td>
<td><font color="#000000"><%=(Recordset1.Fields.Item("date_effective").Value)%></font></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td><strong><font color="#FF0000">New Effective Date</font></strong></td>
<td><input name="date_effective_new" type="text" id="date_effective_new" value="<%=date()%>" size="10" maxlength="10" readonly="true">
<a href="javascript:NewCal('date_effective_new','mmddyyyy')"><img src="images/calnew.gif" alt="Select an end date" width="22" height="16" border="0" align="absmiddle"></a>
<font color="#585858">Use calendar to change date</font></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td><strong>Current Procedures Info</strong></td>
<td><font color="#000000"><%=(Recordset1.Fields.Item("proc_info").Value)%></font></td>
<td> </td>
</tr>
<tr>
<td><font color="#000000"> </font></td>
<td> </td>
<td> </td>
</tr>
<tr>
<td valign="top"><font color="#FF0000"><strong>New Information</strong></font></td>
<td><textarea name="proc_info_new" cols="42" rows="10" id="proc_info_new"></textarea></td>
<td> </td>
</tr>
<tr>
<td height="17"><font color="#000000"> </font></td>
<td> </td>
<td> </td>
</tr>
<tr>
<td><font color="#000000"> </font></td>
<td><input name="date_sub" type="hidden" id="date_sub" value="<%=date()%>">
<strong>
<input name="IDX" type="hidden" id="IDX" value="<%=(Recordset1.Fields.Item("ID").Value)%>">
</strong> </td>
<td> </td>
</tr>
<tr>
<td><font color="#000000"> </font></td>
<td><input name="Update" type="submit" id="Update" value="Update">
<input name="Clear" type="reset" id="Clear" value="Clear">
<input name="Delete" type="submit" id="Delete" onClick="GP_popupConfirmMsg('YOU ARE ABOUT TO DELETE THE CURRENT RECORD. CLICK OK TO CONTINUE OR CANCEL TO ABORT.');return document.MM_returnValue" value="Delete">
<input name="button" type="button" onClick="location.href='submission_new.asp';" value="Go Back"></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</form></td>
</tr>
</table>
</div></td>
</tr>
</table>
</body>
</html>ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What is the purpose of this?
Mid(sql, 2, 10000)
To me it looks like a work around for something that is not right to start with.
I see this code in your sql
sql=sql & ",proc_title='" & Request.Form("proc_title_n
where you start out with a comma. Just take out the comma.
What you probably want to end up with is something like
UPDATE procedures SET proc_title="whatever_reqfo
Mid(sql, 2, 10000)
To me it looks like a work around for something that is not right to start with.
I see this code in your sql
sql=sql & ",proc_title='" & Request.Form("proc_title_n
where you start out with a comma. Just take out the comma.
What you probably want to end up with is something like
UPDATE procedures SET proc_title="whatever_reqfo
Since you are already using dreamweaver to create most of your asp, I think it would be good for you to start with the update and insert commands dreamweaver creates. Once that works, then hand code. I know you said before you think dreamweaver ads extra code. But that is just chatter from the mx days. You are already using 90% of what dreamweaver creates as it is. The big advantage for you is the newest versions of dreamweaver are using their parameter quires and their own flavor of stored procedures. This helps safeguard your data input.
http://support.microsoft.c
In addition you should at least start scrubbing your input with a minimal.
x=replace(request.form("x"
If you are not expecting html then
x=replace(request.form("x"
x=replace(x,"<","")
x=replace(x,">","")
If you are expecting a number then
x=replace(request.form("x"
x=replace(x,"<","")
x=replace(x,">","")
if not isnumeric(x) then
x=0 'if not a number, use zero or Null
end if
http://support.microsoft.c
In addition you should at least start scrubbing your input with a minimal.
x=replace(request.form("x"
If you are not expecting html then
x=replace(request.form("x"
x=replace(x,"<","")
x=replace(x,">","")
If you are expecting a number then
x=replace(request.form("x"
x=replace(x,"<","")
x=replace(x,">","")
if not isnumeric(x) then
x=0 'if not a number, use zero or Null
end if
sql="UPDATE procedures SET " & Mid(sql, 2, 10000)
sql=sql & " WHERE ID = " & pIDX& ""
'response.write sql
rs.Execute sql
Response.Redirect "thankyou.htm"
to
sql="UPDATE procedures SET " & Mid(sql, 2, 10000)
sql=sql & " WHERE ID = " & pIDX& ""
response.write sql
rs.Execute sql
response.end
'Response.Redirect "thankyou.htm"
Run the page and report back what response.write sql is.