|
|
I am working on an update statement which will only update the fields that have actually been changed however i cant get it working, if anyone could help me then i would ve very grateful as i have to show it next week working and i know i am soooooo close to cracking it but just need a nudge in the right direction.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
If Request("Submit") <> "" Then
intRecIDs = Replace(Request("hidRecIDs"), "*", "") ' remove all the asterisks, to create a list like this: 2, 5, 8, 9 etc.
arrRecIDs = Split(intRecIDs, ", ") ' Create an array, wich will contain just the IDs of the records we need to update
For i = 0 to Ubound(arrRecIDs) ' Loop trough the array
intNum = Replace(Request("txtNum" & arrRecIDs(i)), "'", "''")
set commUpdate = Server.CreateObject("ADODB.Command")
commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/matrix.mdb") & ";Persist Security Info=False"
commUpdate.CommandText = "UPDATE community_councils SET text1 = " & intCBM_PS & ", text2 = " & intCBM & ", text3 = " & intPCSO & ", text4 = " & intR_PCSO & ", text5 = " & intCSAS & ", text6 = " & intSC & ", text7 = " & intadmin_support & ", text8 = " & intCD_coordinator & ", text9 = " & intbeat_profile & ", text10 = " & intbeat_action_plan & ", text11 = " & intCBM_CPA & ", text12 = " & intCCDB & ", text13 = " & intTraining & ", text14 = " & intdevelopment_review & ", text15 = " & intresearch & ", text16 = " & intengagement & ", text17 = " & intpublic_preferences & ", text18 = " & intinv_analysis & ", text19 = " & intpublic_choices & ", text20 = " & intplan_action & ", text21 = " & intreview & ", text22 = " & intdyna_ddigon & ", text23 = " & intcommunities_first & " WHERE RecID = " & arrRecIDs(i)
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
Next
strMessage = i & " Records Updated"
Response.Redirect("/forum/vb_update_test_Message_.html" & strMessage)
End If
%>
<!--#include file="/forum/Connections/safematrix.html" -->
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_safematrix_STRING
Recordset1.Source = "SELECT * FROM Community_Councils ORDER BY RecID"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = 10
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="/forum/community_matrix_styles.css" rel="stylesheet" type="text/css">
<title>Untitled Document</title>
<script language="JavaScript">
<!--
// When the value in a textfield is changed, notice the onChange="RecUpdate('<%= intRecID %>')"
// on each of the textfields, the value of the Record ID associated with that field
// is passed to the RecUpdate function. First the value is surounded with 2 asterisks e.g. *6*
// This is so that *1* can be distinguished from *10*, *11* etc.
function RecUpdate(RecID){
var ThisID = "*" + (RecID) + "*"
if (document.form1.hidRecIDs.value == ""){ // If the hidden field is empty
document.form1.hidRecIDs.value = (ThisID) // Store the value in the hidden field (hidRecIDs) as it is.
}
if (document.form1.hidRecIDs.value != ""){ // If the hidden field isn't empty
var str = document.form1.hidRecIDs.value; // Store the contents of the hidden field in the variable str
var pos = str.indexOf(ThisID); // Search str to see if this RecID is allready in it.
if (pos == -1) { // If the position returned is -1 it isn't allredy in there,
document.form1.hidRecIDs.value = document.form1.hidRecIDs.value + ", " + (ThisID)
} // so add ", " and this ID to what is already in hidRecIDs
} // to create a list like this *2*, *5*, *8* etc.
}
//-->
</script>
</head>
<body>
<form name="form1" method="post" action="">
<table width="95%" border="0" align="center" cellpadding="0" cellspacing="2" class="border">
<tr class="border">
<td width="31%" rowspan="2" class="style2"><div align="center">Test Area </div></td>
<td colspan="8" bgcolor="#eaeaea"><div align="center">C.N.P.T</div></td>
<td colspan="6" bgcolor="#eaeaea"><div align="center">Infrastructure</div></td>
<td colspan="7" bgcolor="#eaeaea"><div align="center">Engagement</div></td>
<td bgcolor="#eaeaea">Â </td>
<td bgcolor="#eaeaea">Â </td>
</tr>
<tr>
<td width="3%" bgcolor="#66FFFF"><div align="center">C<br>
B<br>
M<br>
<br>
P<br>
S</div></td>
<td width="3%" bgcolor="#66FFFF"><div align="center">C<br>
B<br>
M</div></td>
<td width="3%" bgcolor="#66FFFF"><div align="center">P<br>
C<br>
S<br>
O</div></td>
<td width="3%" bgcolor="#FFCC66"><div align="center">R<br>
<br>
P<br>
C<br>
S<br>
O</div></td>
<td width="3%" bgcolor="#FFCC66"><div align="center">C<br>
S<br>
A<br>
S</div></td>
<td width="3%" bgcolor="#FFCC66"><div align="center">S<br>
C</div></td>
<td width="3%" bgcolor="#FFCC66"><div align="center">A<br>
d<br>
m<br>
i<br>
n<br>
<br>
S<br>
u<br>
p<br>
p<br>
o<br>
r<br>
t </div></td>
<td width="3%" bgcolor="#FFCC66"><div align="center">C<br>
D<br>
<br>
C<br>
o<br>
o<br>
r<br>
d<br>
i<br>
n<br>
a<br>
t<br>
o<br>
r</div></td>
<td width="3%" bgcolor="#66FF66"><p align="center">B<br>
e<br>
a<br>
t<br>
<br>
P<br>
r<br>
o<br>
f<br>
i<br>
l<br>
e </p></td>
<td width="3%" bgcolor="#66FF66"><div align="center">B<br>
e<br>
a<br>
t<br>
<br>
A<br>
c<br>
t<br>
i<br>
o<br>
n<br>
<br>
P<br>
l<br>
a<br>
n</div></td>
<td width="3%" bgcolor="#66FF66"><div align="center">C<br>
B<br>
M<br>
<br>
C<br>
P<br>
A</div></td>
<td width="3%" bgcolor="#66FF66"><div align="center">C<br>
C<br>
D<br>
B</div></td>
<td width="3%" bgcolor="#66FF66"><div align="center">T<br>
r<br>
a<br>
i<br>
n<br>
i<br>
n<br>
g</div></td>
<td width="3%" bgcolor="#66FF66"><div align="center">D<br>
e<br>
v<br>
e<br>
l<br>
o<br>
p<br>
m<br>
e<br>
n<br>
t<br>
<br>
R<br>
e<br>
v<br>
i<br>
e<br>
w<br>
</div></td>
<td width="3%" bgcolor="#FFFF66"><div align="center">R<br>
e<br>
s<br>
e<br>
a<br>
r<br>
c<br>
h</div></td>
<td width="3%" bgcolor="#FFFF66"><div align="center">E<br>
n<br>
g<br>
a<br>
g<br>
e<br>
m<br>
e<br>
n<br>
t</div></td>
<td width="3%" bgcolor="#FFFF66"><div align="center">P<br>
u<br>
b<br>
l<br>
i<br>
c<br>
<br>
P<br>
r<br>
e<br>
f<br>
e<br>
r<br>
e<br>
n<br>
c<br>
e<br>
s</div></td>
<td width="3%" bgcolor="#FFFF66"><p align="center">I<br>
n<br>
v<br>
<br>
&<br>
<br>
A<br>
n<br>
a<br>
l<br>
y<br>
s<br>
i<br>
s </p></td>
<td width="3%" bgcolor="#FFFF66"><div align="center">P<br>
u<br>
b<br>
l<br>
i<br>
c<br>
<br>
C<br>
h<br>
o<br>
i<br>
c<br>
e<br>
s</div></td>
<td width="3%" bgcolor="#FFFF66"><div align="center">P<br>
l<br>
a<br>
n<br>
<br>
&<br>
<br>
A<br>
c<br>
t<br>
i<br>
o<br>
n</div></td>
<td width="3%" bgcolor="#FFFF66"><div align="center">R<br>
e<br>
v<br>
i<br>
e<br>
w</div></td>
<td width="3%" bgcolor="#FFCC66"><div align="center">D<br>
y<br>
n<br>
a<br>
<br>
D<br>
d<br>
i<br>
g<br>
o<br>
n</div></td>
<td width="3%" bgcolor="#FFCC66"><div align="center">C<br>
o<br>
m<br>
m<br>
u<br>
n<br>
i<br>
t<br>
i<br>
e<br>
s<br>
<br>
F<br>
i<br>
r<br>
s<br>
t</div></td>
</tr>
<% intRecID =(Recordset1.Fields.Item("RecID").Value) ' Store the current RecordID in a variable %>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<tr>
<td nowrap bgcolor="#eaeaea" class="style3"><%= varRecID %>
<input name="hidRecID<%= intRecID %>" type="hidden" value="<%= intRecID %>" size="1">
<%=(Recordset1.Fields.Item("council_name").Value)%></td>
<td nowrap class="style3"><input name="text1<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("CBM_PS").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text2<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("CBM").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text3<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("PCSO").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text4<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("R_PCSO").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text5<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("CSAS").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text6<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("SC").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text7<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("admin_support").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text8<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("CD_coordinator").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text9<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("beat_profile").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text10<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("beat_action_plan").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text11<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("CBM_CPA").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text12<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("CCDB").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text13<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("Training").Value)%>" size="1" ></td>
<td nowrap class="style3"><input name="text14<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("development_review").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text15<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("research").Value)%>" size="1" ></td>
<td nowrap class="style3"><input name="text16<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("engagement").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text17<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')"value="<%=(Recordset1.Fields.Item("public_preferences").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text18<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')"value="<%=(Recordset1.Fields.Item("inv_analysis").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text19<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("public_choices").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text20<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("plan_action").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text21<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("review").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text22<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("dyna_ddigon").Value)%>" size="1"></td>
<td nowrap class="style3"><input name="text23<%= intRecID %>" type="text" onChange="RecUpdate('<%= varRecID %>')" value="<%=(Recordset1.Fields.Item("communities_first").Value)%>" size="1"></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>
<p>
<input type="submit" name="Submit" value="Submit">
</p>
</form>
<p>Â </p>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%> |
|
|
|
|
|
|
|
|
// |
elenton
