...
PDA

Form submit problem

enfleshjsc
02-13-2007, 08:42 PM
I have 2 pages that are nearly identical. The form on each page is supposed to update the same fields in a database table. One updates correctly and the other doesn't. Any ideas why this may happen?

Below is the code for the page that is not working:


<%@ Language=VBScript %>

<%
Server.ScriptTimeout = 180
%>

<html>
<form name="thisform" method="post">
<head><script type="text/javascript">setTimeout(' document.location=document.location' ,120000);</script>
<link rel="stylesheet" type="text/css" href="../innova.css">
<% Response.Buffer %>

<script id="DebugDirectives" runat="server" language="javascript">
// Set these to true to enable debugging or tracing
@set @debug=true
@set @trace=true
</script>
</head>
<body>
<table>
<tr>
<td width="250"><img height="95" src="../index_01.gif" width="237"></td>
<td><p><font size="6"><font face="Arial">DMR&nbsp;PLANNING&nbsp;EDIT</font></td><td>
<%
On error resume Next
Dim i,conn,rs,sortitem,sortorder,mykey,myarr(),cnt,myarr2(),k,myNotes,n,sql,c,href(12,3),f,j,approved(), myarr3(),hr,dups(),SString,firstKey
sortorder=""
sortitem=""
sql="SELECT username, password FROM dbo.DMR_LOGIN"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Techno_72","sa",""
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn
temp=0
do until rs.EOF or temp=1
if Request.Cookies("planninguser")=rs("username") and Request.Cookies("planningpassword")=rs("password") then
temp = 1
end if
rs.MoveNext
loop
conn.close
If temp <> 1 then
Response.Redirect "login.asp"
else
Response.Write("<td style='font-size:10pt'>Logged in as: "&Request.Cookies("planninguser")&"<br><a href='../DMR_Planning.asp?l=1&sortby=ActionTime%20desc'>Logout</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href='users.asp'>Manage Users</a></td></tr></table></tr></table>")
End If
sesarr = session("keys")
sesarr2 = session("status")
cnt2=1
k=0
l=0
m=0

If Request.QueryString("clear")>0 Then
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Techno_72","sa",""
if CStr(left(request.QueryString("clear"),2))=CStr("P-") then
part=right(request.QueryString("clear"),len(request.QueryString("clear"))-2)
lot=right(part,(len(part)-InStr(part, "$")))
part=left(part,(len(part)-len(lot))-1)
sql="SELECT DISTINCT dbo.IN_COMPONENTREQUEST.XLOT_ID FROM dbo.IN_COMPONENTREQUEST INNER JOIN dbo.QS_LOT ON dbo.IN_COMPONENTREQUEST.XLOT_ID = dbo.QS_LOT.XUNIQUEKEY WHERE (dbo.QS_LOT.XLOT = '"&lot&"')"
'Response.Write(sql)
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn
lot=rs.Fields.item("XLOT_ID")
rs.close
'GET THE LIST OF KEYS TO UPDATE
sql="SELECT XUNIQUEKEY FROM dbo.IN_COMPONENTREQUEST WHERE (XLOT_ID = '"&lot&"') AND (XPARTNUMBER = '"&part&"') AND (XSTATUS = "&Request.querystring("Status")&") ORDER BY DMR_NUMBER"
'Response.Write(sql)
rs.Open sql, conn
do until rs.EOF
sql="UPDATE IN_COMPONENTREQUEST SET XNOTES = '', XLASTUSER='"&Request.Cookies("planninguser")&"' WHERE XUNIQUEKEY = '"&rs.Fields.Item("XUNIQUEKEY")&"'"
'Response.Write("<br>"&sql&"<br>")
conn.Execute(sql)
rs.movenext
loop
rs.close
else
sql="UPDATE IN_COMPONENTREQUEST SET XNOTES = '', XLASTUSER='"&Request.Cookies("planninguser")&"' WHERE XUNIQUEKEY = '"&Request.querystring("clear")&"'"
'Response.Write(sql)
conn.Execute(sql)
End If
conn.close
if Request.QueryString("Separate") then
S="&Separate="&Request.QueryString("Separate")&"&Lot="&Request.QueryString("Lot")&"&Status="&left(request.QueryString("Status"),2)
else
S=""
end if
'Response.Redirect strURL&"?sortby="&Request.QueryString("sortby")&S
End if
'/\\/\/'\/\/\//\\//\\//\//\/\/\/\/\/\/\/\\//\\\\\\\\/\/\//\/\\/\//\/\/\/\/\/\/\/\
'/\//\/\/\/\/\/\/\/\\//\/\\//\/\\/\/\/\/\//\/\/\/\/\/\\//\/\\/\//\/\\/\//\/\/\/\/\
';\'\'\'\'\'\'\'\'\'\'\'\\///\\/\/\/\/\//\/\//\/\\/\/\//\/\\''\\''\\'\'\'\''\''\'\

If Request.Form.Count > 0 Then
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Techno_72","sa",""
' ////////////////// CREATE AN ARRAY OF ALL ORIGINAL VALUES

sql="SELECT XNOTES, XSTATUS, XUNIQUEKEY FROM dbo.IN_DMR_PLAN_VIEW2"
'Response.Write(sql)
rs.Open sql, conn
do until rs.EOF
l=l+1
redim preserve qArray(3,l)
qArray(1,l)=rs.fields.item("XUNIQUEKEY")
qArray(2,l)=rs.fields.item("XSTATUS")
' Response.Write(len(rs.fields.item("XNOTES")))
if len(rs.fields.item("XNOTES"))<1 then
qArray(3,l)=""
else
qArray(3,l)=rs.fields.item("XNOTES")
end if
rs.movenext
'Response.Write(qArray(1,l)&"<br>")
loop
rs.close
for i=1 to (UBound(sesarr))
' ////////////////// CREATE AN ARRAY OF ALL SUBMITTED KEYS
if CStr(left(sesarr(i),2))=CStr("P-") then
part=right(sesarr(i),len(sesarr(i))-2)
lot=right(part,(len(part)-InStr(part, "$")))
part=left(part,(len(part)-len(lot))-1)
sql="SELECT DISTINCT dbo.IN_COMPONENTREQUEST.XLOT_ID FROM dbo.IN_COMPONENTREQUEST INNER JOIN dbo.QS_LOT ON dbo.IN_COMPONENTREQUEST.XLOT_ID = dbo.QS_LOT.XUNIQUEKEY WHERE (dbo.QS_LOT.XLOT = '"&lot&"')"
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn
lot=rs.Fields.item("XLOT_ID")
rs.close
sql="SELECT XUNIQUEKEY FROM dbo.IN_COMPONENTREQUEST WHERE (XLOT_ID = '"&lot&"') AND (XPARTNUMBER = '"&part&"') AND (XSTATUS = "&sesarr2(i)&")"
'Response.Write("<bR>"&sql&"<br>")
rs.Open sql, conn
do until rs.EOF
k=k+1
redim preserve sArray(3,k)
sArray(1,k)=rs.fields.item("XUNIQUEKEY")
sArray(2,k)=Request.Form("XSTATUS")(i)
if len(cstr(Request.Form("XNOTES")(i)))<1 then
sArray(3,k)=""
else
sArray(3,k)=Request.Form("XNOTES")(i)
end if
rs.movenext
'Response.Write(sArray(1,k)&"<br>")
loop
rs.close
else
k=k+1
redim preserve sArray(3,k)
sArray(1,k)=sesarr(i)
sArray(2,k)=Request.Form("XSTATUS")(i)
if len(cstr(Request.Form("XNOTES")(i)))<1 then
sArray(3,k)=""
else
sArray(3,k)=Request.Form("XNOTES")(i)
end if
'Response.Write(sArray(1,k)&"<br>")
end if
next
'Response.Write(k&"---"&l)
for i=1 to k
for j=1 to l
if sArray(1,i)=qArray(1,j) then
'Response.Write("<br>"&sArray(1,i)&" - - - - - "&qArray(1,j)&"<br>")
'Response.Write("<br>"&sArray(2,i)&" - - - - - "&qArray(2,j)&"<br>")
'Response.Write("<br>"&sArray(3,i)&" - - - - - "&qArray(3,j)&"<br>")
if cint(sArray(2,i))<>cint(qArray(2,j)) or cstr(sArray(3,i))<>cstr(qArray(3,j)) and cstr(sArray(3,i)) > "" then
if cint(sArray(2,i))<>cint(qArray(2,j)) and cstr(sArray(3,i))<>cstr(qArray(3,j)) then
'Response.Write("BOTH")
sql="UPDATE IN_COMPONENTREQUEST SET XNOTES = '"&now&": "&sArray(3,i)&"'+char(13)+char(10)+'"&qArray(3,j)&"', XLASTUSER='"&Request.Cookies("planninguser")&"', XSTATUS='"&sArray(2,i)&"' WHERE XUNIQUEKEY = '" & sArray(1,i) & "'"
else
if cint(sArray(2,i))<>cint(qArray(2,j)) then
'Response.Write("status only")
sql="UPDATE IN_COMPONENTREQUEST SET XLASTUSER='"&Request.Cookies("planninguser")&"', XSTATUS='"&sArray(2,i)&"' WHERE XUNIQUEKEY = '" & sArray(1,i) & "'"
end if
if cstr(sArray(3,i))<>cstr(qArray(3,j)) then
'Response.Write("notes only")
sql="UPDATE IN_COMPONENTREQUEST SET XNOTES = '"&now&": "&sArray(3,i)&"'+char(13)+char(10)+'"&qArray(3,j)&"', XLASTUSER='"&Request.Cookies("planninguser")&"' WHERE XUNIQUEKEY = '" & sArray(1,i) & "'"
end if
end if
'Response.Write("<br>"&sql)
conn.Execute(sql)
end if
end if
next
next
conn.close

End If

%>
<table><tr><td><a href="../DMR_Request.asp?sortby=ActionTime%20desc">Stockroom Requests</a><td><a href="../DMR_Planning.asp?sortby=ActionTime%20desc">Planning Status</a> </td><td><a href="../DMR_Issued.asp?sortby=ActionTime%20desc">Issued Components</a></td><td><a href="../DMR_Deleted.asp?sortby=ActionTime%20desc">Deleted Requests</a></td><td><a href="DMR_Purchasing.asp?sortby=ActionTime%20desc">Purchasing</a></td><td><a href="DMR_RTV.asp?sortby=ActionTime%20desc">RTV</a></td></tr></table>
<table><tr><td width="20px"><h6><input type="submit" value=" Submit " class="submit" name="submit1"></h6></td><td><h6>Click <img src="../exp_plus.gif" WIDTH="10" HEIGHT="10"> below to Separate groups. To regroup click <a href="<%=Request.ServerVariables("URL")%>?sortby=<%=Request.QueryString("sortby")%>">here.</a></h6></td><td><a href='controls.asp'>Manage Fields</a></td><td><input type="button" value=" Manual DMR " onclick="location.href='//innova-8/webdmr/dmr_entry.asp'" name="submit1"></td></tr></table>
<input id="savedsql" name="savedsql" type="hidden" value="<%=sql2use%>">
<input id="sqlwhere2use" name="sqlwhere2use" type="hidden" value="<%=sqlwhere2use%>">

<%
' CREATE ARRAY OF DUPLICATES SO THAT THEY CAN BE COLLAPSED
sql="SELECT * FROM CollapsedQTY"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Techno_72","sa",""
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn
cnt=0
do until rs.EOF
' Response.Write(rs.Fields.Item("XPARTNUMBER")&Request.QueryString("Separate")&rs.Fields.Item("XSTATUS")&Request.Querystring("Status"))&rs.Fields.Item("XLOT")&Request.QueryString("Lot")&"<br>")
if NOT(rs.Fields.Item("XPARTNUMBER")=Request.QueryString("Separate") and rs.Fields.Item("XSTATUS")=cint(Request.Querystring("Status")) and rs.Fields.Item("XLOT")=Request.QueryString("Lot")) then
cnt=cnt+1
reDim preserve dups(5,cnt)
dups(1,cnt)=rs.Fields.Item("XPARTNUMBER")
dups(2,cnt)=rs.Fields.Item("XLOT")
dups(3,cnt)=rs.Fields.Item("MyQty")
dups(4,cnt)=0
dups(5,cnt)=rs.Fields.Item("XSTATUS")
end if
rs.MoveNext
' Response.Write(cnt&" ' ' ' "&dups(1,cnt)&" ' ' ' "&dups(2,cnt))
loop
dupsamt=cnt
rs.close
If Request.QueryString("SortBy") <> "" Then
sortitem = Left(trim(Request.querystring("sortby")),Instr(1,Request.querystring("sortby")," ")-1)
sortorder = right(trim(Request.querystring("sortby")),Len(Request.querystring("sortby")) - Instr(1,Request.querystring("sortby")," ")+1)
sql = "SELECT * FROM IN_DMR_PLAN_VIEW2" & " Order By "& Request.querystring("sortby")
else
sql="SELECT * FROM IN_DMR_PLAN_VIEW2"
End If
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Techno_72","sa",""
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn
' Response.Write "Current Sort Item: " & sortitem & ", Length: " & Len(sortitem) & "<BR>"
' Response.Write "Current Sort Order: " & sortorder & ", Length: " & Len(sortorder) & "<BR>"
' Response.Write sql
%><div class=m2><table cellpadding=2 cellspacing=0><%
b="./DMR_Planning_MOD.asp?"
if Request.QueryString("Separate") then
b="./DMR_Planning_MOD.asp?Separate="&Request.QueryString("Separate")&"&Lot="&Request.QueryString("Lot")&"&Status="&Request.QueryString("Status")&"&"
end if
sql2="SELECT dbo.IN_USER_COLUMNS.* FROM dbo.IN_USER_COLUMNS WHERE (USERNAME = '"&Request.Cookies("planninguser")&"')"
set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.Open sql2, conn
t=0
if NOT rs2.EOF then
if rs2.Fields.Item("DMR_NUMBER") then
t=t+1
href(t,1)="DMR_NUMBER"
href(t,3)="DMR#"
end if
if rs2.Fields.Item("XSTATUS") then
t=t+1
href(t,1)="XSTATUS"
href(t,3)="Action"
end if
if rs2.Fields.Item("ACTION_TIME") then
t=t+1
href(t,1)="ActionTime"
href(t,3)="Action Time"
end if
if rs2.Fields.Item("SUBMITTED") then
t=t+1
href(t,1)="XREQUESTTIME"
href(t,3)="Submitted"
end if
if rs2.Fields.Item("WO") then
t=t+1
href(t,1)="XLOT"
href(t,3)="WO"
end if
if rs2.Fields.Item("PART_NUMBER") then
t=t+1
href(t,1)="XPARTNUMBER"
href(t,3)="Part #"
end if
if rs2.Fields.Item("DESCRIPTION") then
t=t+1
href(t,1)="XDESCRIPTION"
href(t,3)="Description"
end if
if rs2.Fields.Item("SUBMITTED_BY") then
t=t+1
href(t,1)="XINSPECTUSER"
href(t,3)="By"
end if
if rs2.Fields.Item("LOCATION") then
t=t+1
href(t,1)="XSTATION"
href(t,3)="Location"
end if
if rs2.Fields.Item("SN") then
t=t+1
href(t,1)="XSERIALNUMBER"
href(t,3)="S/N"
end if
if rs2.Fields.Item("ASSEMBLY") then
t=t+1
href(t,1)="XPRODUCT"
href(t,3)="Assembly"
end if
if rs2.Fields.Item("REV") then
t=t+1
href(t,1)="XREVISION"
href(t,3)="Rev"
end if
rs2.close
else
Response.Redirect("http://innova-8/WEBDMR/planning/login.asp")
end if
for i=1 to 12
if sortitem = href(i,1) then
if sortorder = " desc" then
href(i,2)=b&"sortby="&href(i,1)&"%20asc"
f="<div class=desc>"
f2="</div>"
else
href(i,2)=b&"sortby="&href(i,1)&"%20desc"
f="<div class=asc>"
f2="</div>"
end if
else
href(i,2)=b&"sortby="&href(i,1)&"%20desc"
f=""
f2=""
end if
Response.Write("<th><A href='"&href(i,2)&"'>"&f&href(i,3)&"</a></th>")
next
Response.Write("</tr>")
cnt=1
cnt2=1
j=1
do until rs.EOF
' Collpase duplicates
act=0
for i=1 to dupsamt
'Response.Write(dups(1,i)&" ' ' ' '"&rs.Fields.Item("XPARTNUMBER")&" ' ' ' '"&dups(2,i)&" ' ' ' '"&rs.Fields.Item("XLOT"))
if rs.Fields.Item("XPARTNUMBER")=dups(1,i) AND rs.Fields.Item("XLOT")=dups(2,i) AND rs.Fields.Item("XSTATUS")=dups(5,i) then
if (dups(4,i)=0) then
' Response.Write(dups(1,i)&" ' ' ' '"&rs.Fields.Item("XPARTNUMBER")&" ' ' ' '"&dups(2,i)&" ' ' ' '"&rs.Fields.Item("XLOT"))
act=1
dups(4,i)=1
MQTY=dups(3,i)
else
if (dups(4,i)=1) then
act=2
end if
end if
end if
next
if act<2 then
if hr then
%>
<tr><td colspan="12"><br><hr></td></tr>
<%
end if
hr=1
reDim Preserve myarr2(cnt)
myarr2(cnt)=rs("XSTATUS")
cnt2=cnt2+2
if (rs.Fields.Item("XPARTNUMBER")=Request.querystring("Separate") AND rs.Fields.Item("XLOT")=Request.QueryString("Lot") and rs.Fields.Item("XSTATUS")=cint(Request.QueryString("Status"))) then
Response.Write("<tr class='high'>")
else
Response.Write("<tr>")
end if
mykey=rs("XUNIQUEKEY")
for j=1 to t
select case href(j,1)
case "DMR_NUMBER"
if act=1 then
Response.Write("<td><a href='//innova-8/webdmr/Planning/DMR_Planning_MOD.asp?Separate="&rs.Fields.Item("XPARTNUMBER")&"&Status="&rs.Fields.Item("XSTATUS")&"&Lot="&rs.Fields.Item("XLOT")&"&sortby="&Request.QueryString("sortby")&"'><img src='../exp_plus.gif'></a><a href='//innova-8/webdmr/dmr_retrieve_multi.asp?part="&rs.Fields.Item("XPARTNUMBER")&"&Lot="&rs.Fields.Item("XLOT")&"&Status="&rs.Fields.Item("XSTATUS")&"&sortby="&Request.QueryString("sortby")&"'>"&MQTY&"_DMRs</a></td>")
else
Response.Write("<td><a href='//innova-8/webdmr/dmr_retrieve.asp?dmr="&rs.Fields.Item(href(j,1))&"'>"&rs.Fields.Item(href(j,1))&"</a></td>")
end if
case "XSTATUS"
ReDim Preserve myarr(cnt)
if act=1 then
myarr(cnt)="P-"&rs.Fields.Item("XPARTNUMBER")&"$"&rs.Fields.Item("XLOT")
else
myarr(cnt)=mykey
end if
temp=myarr(cnt)
cnt=(1+cnt)
select case rs.Fields.Item(href(j,1))
case 6
%><td><select id="S_<%Response.Write(mykey)%>" name="XSTATUS" size="1"><option value="6"> HOLD </option> <option value="4"> APPROVE </option> <option value="7"> DELETE </option><option value="8">PURCHASING</option><option value="9">RTV</option></select></td><%
case 10
%><td><select id="S_<%Response.Write(mykey)%>" name="XSTATUS" size="1"><option value="10"> ORDERED </option> <option value="6"> HOLD </option> <option value="4"> APPROVE </option> <option value="7"> DELETE </option><option value="8">PURCHASING</option><option value="9">RTV</option> </select></td><%
case else
%><td><select id="S_<%Response.Write(mykey)%>" name="XSTATUS" size="1"><option value="5"> PLANNING </option> <option value="6"> HOLD </option> <option value="4"> APPROVE </option> <option value="7"> DELETE </option><option value="8">PURCHASING</option><option value="9">RTV</option> </select></td><%
end select
case "XNOTES"
if Request.QueryString("Separate") then
S="Separate="&Request.QueryString("Separate")&"&Lot="&Request.QueryString("Lot")&"&Status="&Request.QueryString("Status")
else
S="Status="&rs.Fields.Item("XSTATUS")&"&Lot="&Request.QueryString("Lot")
end if
if (rs.Fields.Item("XPARTNUMBER")=Request.querystring("Separate") AND rs.Fields.Item("XLOT")=Request.QueryString("Lot") and rs.Fields.Item("XSTATUS")=cint(Request.QueryString("Status"))) then
Response.Write("</tr><tr class='high'><td><p>Notes:</p></td><td colspan='8'><TEXTAREA name='XNOTES' style='WIDTH: 95%; HEIGHT: 38px' name=textarea1 cols=52>"&rs.Fields.Item(href(j,1))&"</TEXTAREA></td>")
else
Response.Write("</tr><tr><td><p>Notes:</p></td><td colspan='8'><TEXTAREA name='XNOTES' style='WIDTH: 95%; HEIGHT: 38px' name=textarea1 cols=52>"&rs.Fields.Item(href(j,1))&"</TEXTAREA></td>")
end if
case"XLASTUSER"
Response.Write("<td colspan=3><h5><br>Last Action By:<br>"&rs.Fields.Item(href(j,1))&"</h5></tr>")
case else
Response.Write("<td>")
Response.Write(rs.Fields.Item(href(j,1)))
Response.Write("</td>")
end select
next
if Request.QueryString("Separate") then
S="Separate="&Request.QueryString("Separate")&"&Lot="&Request.QueryString("Lot")&"&Status="&Request.QueryString("Status")
else
S=""
end if
if (rs.Fields.Item("XPARTNUMBER")=Request.querystring("Separate") AND rs.Fields.Item("XLOT")=Request.QueryString("Lot") and rs.Fields.Item("XSTATUS")=cint(Request.QueryString("Status"))) then
Response.Write("</tr><tr class='high'><td><p>Notes:</p></td><td colspan='8'><TEXTAREA name='XNOTES' style='WIDTH: 95%; HEIGHT: 38px' name=textarea1 cols=52>"&rs.Fields.Item("XNOTES")&"</TEXTAREA></td>")
else
Response.Write("</tr><tr><td><p>Notes:</p></td><td colspan='8'><TEXTAREA name='XNOTES' style='WIDTH: 95%; HEIGHT: 38px' name=textarea1 cols=52>"&rs.Fields.Item("XNOTES")&"</TEXTAREA></td>")
end if
Response.Write("<td colspan='3'><h5><br>Last Action By:<br>"&rs.Fields.Item("XLASTUSER")&"</h5></tr>")
end if
rs.MoveNext
loop
session("keys")=myarr
session("status")=myarr2

%>

</table>
<input type="submit" value=" Submit " class="submit" name="submit2">
<table>
</table></div>
<table><tr><td><a href="../DMR_Request.asp?sortby=ActionTime%20desc">Stockroom Requests</a><td><a href="../DMR_Planning.asp?sortby=ActionTime%20desc">Planning Status</a> </td><td><a href="../DMR_Issued.asp?sortby=ActionTime%20desc">Issued Components</a></td><td><a href="../DMR_Deleted.asp?sortby=ActionTime%20desc">Deleted Requests</a></td><td><a href="DMR_Purchasing.asp?sortby=ActionTime%20desc">Purchasing</a></td><td><a href="DMR_RTV.asp?sortby=ActionTime%20desc">RTV</a></td></tr></table>
</form>
</body>
</html>
Spudhead
02-14-2007, 04:13 PM
Could you give us just a little more information on what exactly is going wrong?

Is there an error message?
itHighway
02-14-2007, 11:39 PM
I don't think there would be any one who go through all the code you have written. Please specify what are you trying to do and what is the problem you are having.
enfleshjsc
02-15-2007, 03:41 PM
The code pulls information from a database view table. There is a dropdown where you can select a new status for each item, sending it to planning, purchasing, etc. There is also a notes field. These are the only two parts of the form that can be changed. When the submit button is clicked, the page reloads with all original information, and the database remains unchanged (which is the opposite of what needs to happen). There are, however, no error messages. Below is JUST the update code with the array information. I have several other pages that are almost exactly the same (they pull from a different view and have less status options), and they work just fine. Please let me know if there's any other information that would be helpful. I've been going back and forth on this for a week and I still haven't a clue what's going wrong. Thanks!


If Request.Form.Count > 0 Then
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Techno_72","sa",""
' ////////////////// CREATE AN ARRAY OF ALL ORIGINAL VALUES

sql="SELECT XNOTES, XSTATUS, XUNIQUEKEY FROM dbo.IN_DMR_PLAN_VIEW2"
'Response.Write(sql)
rs.Open sql, conn
do until rs.EOF
l=l+1
redim preserve qArray(3,l)
qArray(1,l)=rs.fields.item("XUNIQUEKEY")
qArray(2,l)=rs.fields.item("XSTATUS")
' Response.Write(len(rs.fields.item("XNOTES")))
if len(rs.fields.item("XNOTES"))<1 then
qArray(3,l)=""
else
qArray(3,l)=rs.fields.item("XNOTES")
end if
rs.movenext
'Response.Write(qArray(1,l)&"<br>")
loop
rs.close
for i=1 to (UBound(sesarr))
' ////////////////// CREATE AN ARRAY OF ALL SUBMITTED KEYS
if CStr(left(sesarr(i),2))=CStr("P-") then
part=right(sesarr(i),len(sesarr(i))-2)
lot=right(part,(len(part)-InStr(part, "$")))
part=left(part,(len(part)-len(lot))-1)
sql="SELECT DISTINCT dbo.IN_COMPONENTREQUEST.XLOT_ID FROM dbo.IN_COMPONENTREQUEST INNER JOIN dbo.QS_LOT ON dbo.IN_COMPONENTREQUEST.XLOT_ID = dbo.QS_LOT.XUNIQUEKEY WHERE (dbo.QS_LOT.XLOT = '"&lot&"')"
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn
lot=rs.Fields.item("XLOT_ID")
rs.close
sql="SELECT XUNIQUEKEY FROM dbo.IN_COMPONENTREQUEST WHERE (XLOT_ID = '"&lot&"') AND (XPARTNUMBER = '"&part&"') AND (XSTATUS = "&sesarr2(i)&")"
'Response.Write(sql)
rs.Open sql, conn
do until rs.EOF
k=k+1
redim preserve sArray(3,k)
sArray(1,k)=rs.fields.item("XUNIQUEKEY")
sArray(2,k)=cint(Request.Form("XSTATUS")(i))
if len(cstr(Request.Form("XNOTES")(i)))<1 then
sArray(3,k)=""
else
sArray(3,k)=Request.Form("XNOTES")(i)
end if
rs.movenext
'Response.Write(sArray(1,k)&"<br>")
loop
rs.close
else
k=k+1
redim preserve sArray(3,k)
sArray(1,k)=sesarr(i)
sArray(2,k)=cint(Request.Form("XSTATUS")(i))
if len(cstr(Request.Form("XNOTES")(i)))<1 then
sArray(3,k)=""
else
sArray(3,k)=Request.Form("XNOTES")(i)
end if
'Response.Write(sArray(1,k)&"<br>")
end if
next
'Response.Write(k&"---"&l)
for i=1 to k
for j=1 to l
if sArray(1,i)=qArray(1,j) then
'Response.Write("<br>"&sArray(1,i)&" - - - - - "&qArray(1,j)&"<br>")
'Response.Write("<br>"&sArray(2,i)&" - - - - - "&qArray(2,j)&"<br>")
'Response.Write("<br>"&sArray(3,i)&" - - - - - "&qArray(3,j)&"<br>")
if cint(sArray(2,i))<>cint(qArray(2,j)) or cstr(sArray(3,i))<>cstr(qArray(3,j)) and cstr(sArray(3,i)) > "" then
if cint(sArray(2,i))<>cint(qArray(2,j)) and cstr(sArray(3,i))<>cstr(qArray(3,j)) then
'Response.Write("BOTH")
sql="UPDATE IN_COMPONENTREQUEST SET XNOTES = '"&now&": "&sArray(3,i)&"'+char(13)+char(10)+'"&qArray(3,j)&"', XLASTUSER='"&Request.Cookies("planninguser")&"', XSTATUS='"&sArray(2,i)&"' WHERE XUNIQUEKEY = '" & sArray(1,i) & "'"
else
if cint(sArray(2,i))<>cint(qArray(2,j)) then
'Response.Write("status only")
sql="UPDATE IN_COMPONENTREQUEST SET XLASTUSER='"&Request.Cookies("planninguser")&"', XSTATUS='"&sArray(2,i)&"' WHERE XUNIQUEKEY = '" & sArray(1,i) & "'"
end if
if cstr(sArray(3,i))<>cstr(qArray(3,j)) then
'Response.Write("notes only")
sql="UPDATE IN_COMPONENTREQUEST SET XNOTES = '"&now&": "&sArray(3,i)&"'+char(13)+char(10)+'"&qArray(3,j)&"', XLASTUSER='"&Request.Cookies("planninguser")&"' WHERE XUNIQUEKEY = '" & sArray(1,i) & "'"
end if
end if
'Response.Write("<br>"&sql)
conn.Execute(sql)
end if
end if
next
next
conn.close
End If
Spudhead
02-15-2007, 04:10 PM
So the problem is basically that you've got a SQL UPDATE statement that isn't doing anything? The one that begins

sql="UPDATE IN_COMPONENTREQUEST SET XNOTES

Uncomment the line 'Response.Write("<br>"&sql) below it, and post an example of the full statement that you're trying to run.

Also - you're using SQLServer, right? Have you used Profiler to see exactly what the database thinks its getting and what it makes of it?
enfleshjsc
02-15-2007, 05:16 PM
Thanks for the suggestion to use SQL Profiler. I have inherited these pages and have only dealt with MySQL before, so I had no idea there was such a program. I ran a trace and discovered that there is some code referring to a table that apparently no longer exists. Which presents a whole new set of problems! Ha. But thanks for giving me a push in the right direction (or so I hope :))

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum