Update Mulitple Fields at a Time [Archive]

Click to See Complete Forum and Search --> : Update Mulitple Fields at a Time

jazzy639

06-20-2005, 05:35 PM

Hello,

I wish to have a page where it shows you a list of items and you can tick or untick them and it should update the database.

I came accross this webpage http://www.drdev.net/article11.asp which seems to tell you how to do it but when I try, the database isn't updated.

Where have I gone wrong?

Here is the code - I have taken out any unnessesary things.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>



<%
Dim dbclass
Dim dbclass_numRows

Set dbclass = Server.CreateObject("ADODB.Recordset")
dbclass.ActiveConnection = MM_dbconnect_STRING
dbclass.Source = "SELECT * FROM main WHERE day LIKE '" + Replace(dbclass__VarDay, "'", "''") + "' AND period LIKE '" + Replace(dbclass__VarPeriod, "'", "''") + "'"
dbclass.CursorType = 0
dbclass.CursorLocation = 2
dbclass.LockType = 1
dbclass.Open()

dbclass_numRows = 0
%>

<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
dbclass_numRows = dbclass_numRows + Repeat1__numRows
%>
<%
Dim Repeat2__numRows
Dim Repeat2__index

Repeat2__numRows = -1
Repeat2__index = 0
dbclass_numRows = dbclass_numRows + Repeat2__numRows
%>

<script language="JavaScript">
function RecUpdate(RecID){
var ThisID = "*" + (RecID) + "*"
if (document.form1.hidRecIDs.value == ""){
document.form1.hidRecIDs.value = (ThisID)
}
if (document.form1.hidRecIDs.value != ""){
var str = document.form1.hidRecIDs.value;
var pos = str.indexOf(ThisID);
if (pos == -1) {
document.form1.hidRecIDs.value = document.form1.hidRecIDs.value + ", " + (ThisID)
}
}
}
</script>
<%
If Request("Submit") <> "" Then
intRecID = Replace(Request("ID"), "*", "") ' remove all the asterisks, to create a list like this: 2, 5, 8, 9 etc.
arrRecID = Split(intRecID, ", ") ' Create an array, wich will contain just the IDs of the records we need to update
For i = 0 to Ubound(arrRecID) ' Loop trough the array
strClass = Replace(Request("checkbox" & arrRecID(i)), "'", "''")

set commUpdate = Server.CreateObject("ADODB.Command")
commUpdate.ActiveConnection = MM_dbconnect_STRING
commUpdate.CommandText = "UPDATE main SET class = '" & strClass & "' WHERE ID = " & arrRecID(i)
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
Next
Response.Redirect("editclasses.asp")
End If
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<div align="center" class="style8">
<p>Edit Classes </p>
<form name="form1" method="post" action="editclasses.asp">
<table width="90%" border="0" cellpadding="2" cellspacing="1">
<tr class="site">
<td><div align="center" class="style9">Day</div></td>
<td class="style9"><div align="center">Period</div></td>
<td class="style9"><div align="center">Room</div></td>
<td class="style9"><div align="center">Class</div></td>
<td class="style9"><div align="center"><em>Edit</em></div></td>
</tr>
<% While ((Repeat2__numRows <> 0) AND (NOT dbclass.EOF)) %>
<% intRecID =(dbclass.Fields.Item("ID").Value) %>
<tr onMouseOver="colorRow(this,1);" onMouseOut="colorRow(this,0);">

<td height="35" class="site"><div align="center">
<input name="hidRecID<%= intRecID %>" type="hidden" value="<%= intRecID %>">
<%=(dbclass.Fields.Item("day").Value)%></div></td>
<td class="site"><div align="center"><%=(dbclass.Fields.Item("period").Value)%></div></td>
<td><div align="center"><%=(dbclass.Fields.Item("room").Value)%></div></td>
<td><div align="center">
<input name="checkbox<%= intRecID %>" type="checkbox" onChange="RecUpdate('<%= intRecID %>" value="True" <%If (CStr((dbclass.Fields.Item("class").Value)) = CStr("True")) Then Response.Write("checked") : Response.Write("")%>>
</div></td>
</tr>
<%
Repeat2__index=Repeat2__index+1
Repeat2__numRows=Repeat2__numRows-1
dbclass.MoveNext()
Wend
%>
</table>
<br>

<input name="hidRecID" type="text">
<input type="submit" name="Submit" value="Update"> </form>
<p align="center"><a href="/mchs/login/adminuserpages/default.asp"><img src="/mchs/images/arrow_left.gif" width="40" height="34" border="0"></a></p>
</div></table>
</div>
</body>
</html>
<%
dbclass.Close()
Set dbclass = Nothing
%>

Thanks

simflex

06-20-2005, 10:23 PM

There is an easier way to do this.

First, split the pages into 2= input page and update page.

Loop through all the records you need to update for display.

Set objRS = objConn.execute("SELECT * FROM yourTable " )

'Display the FORM and the top of the TABLE
Response.Write "<FORM METHOD=POST ACTION=""doUpdate.asp"" target=""_new"">"
Response.Write "<TABLE BORDER=1 bordercolor='gainsboro' CELLSPACING=0>"
Dim iLoop
Response.Write "<TR>"
Response.Write "<TH>Action</TH>"
Response.Write "<TH nowrap>field 1</TH>"
Response.Write "<TH nowrap>Field2</TH>"
Response.Write "</TR>"

'Display each element in the table...
Do While Not objRS.EOF
Response.Write "<TR>" & vbCrLf

'Create a checkbox to check for updating, setting the checkboxes
'Value equal to the current items Product Number
Response.Write "<TD><INPUT TYPE=CHECKBOX NAME=Update"
Response.Write "VALUE=" & objRS("field1") & "></TD>"

'Display the number and name of the field
Response.Write vbCrLf & "<TD>" & objRS("field1") & "</TD>"
Response.Write vbCrLf & "<TD>" & objRS("field2")
Response.Write "</TD></TR>" & vbCrLf & vbCrLf

'Move to the next record...
objRS.MoveNext
Loop

'Print the end of the table, the submit button, and the
'the end of the form.
Response.Write "</TABLE>"
Response.Write "<P><INPUT TYPE=SUBMIT VALUE=""Update Selected Records"">"
Response.Write "</FORM>"

'Clean up our ADO objects
objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing
%>
</body>

Now let's create the second page that will process the updates.

<%@ Language=VBScript %>
<% Option Explicit %>
<%
'We want to update our products. The list of product Numbers that need
'to be updated are in a comma-delimited list...
Dim strUpdateList,rst,productNumber
strUpdateList= Request("Update")

if strUpdateList= "" then
'No items to update
Response.Write "You did not select any items to update!"
Else
'Open a connection to the database
Dim objConn
Set rst = Server.CreateObject("ADODB.Recordset")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"DATA SOURCE=" & server.mappath("db\dbName.mdb")
objConn.Open

'Now, use the SQL set notation to update all of the records
'specified by strUpdateList
Dim strSQL
strUpdateList= Replace( strUpdateList, ", ", "','" ) ' see below!

strSQL = "Update YourTable set field1 =formName1, field2= formName2) " & _
"cts WHERE ProjectNumber IN ('" & strUpdateList& "')"
'response.write strsql
'response.end
rst.Open strSQL, objConn

'Display to the user that the product has been updated.
Response.Write Request("Update").Count & " products were updated..."
End If
End If
%>

<form>
<input type=button value="Close Window" onClick="javascript:window.close();">
</form>

See?m Isn't this much simpler than the code you posted?

All you need to do is create form variable name for the fields to be updated.

This is right off of my head, it has not been tested but for the most part should be about ready to go.

Let us know what happens but don't count on me as I may not get back to this forum for awhile. Sorry, nature of my job.

jazzy639

06-22-2005, 12:16 PM

CLOSED