Login  | |
|
 |
ASP VBScript Update Database with Checkboxes - 8/20/2004 6:59:01 AM
|
|
 |
|
| |
GARM
Posts: 2
Score: 0
Joined: 8/20/2004
From:
Status: offline
|
Having this little problem for a couple of days now. Trying to update my database using checkboxes. So far I have been able to update the first record only, or the last record only, or simple nothing at all, but connectino is good.
This is my page:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/exceptiondb.asp" -->
<%
Response.Buffer = True
%>
<%
Dim rsStatus
rsStatus = "PendingTM"
%>
<%
Dim rsFindPendingForTM__MMColParam
rsFindPendingForTM__MMColParam = "1"
If (Request.Cookies("TMIdent") <> "") Then
rsFindPendingForTM__MMColParam = Request.Cookies("TMIdent")
End If
%>
<%
Dim rsFindPendingForTM
Dim rsFindPendingForTM_numRows
Set rsFindPendingForTM = Server.CreateObject("ADODB.Recordset")
rsFindPendingForTM.ActiveConnection = MM_exceptiondb_STRING
rsFindPendingForTM.Source = "SELECT * FROM tblHistoryCsr WHERE TMname = '" + Replace(rsFindPendingForTM__MMColParam, "'", "''") + "' AND Accept LIKE '%" + Replace(rsStatus, "'", "''") + "%' ORDER BY ActualDate ASC"
rsFindPendingForTM.CursorType = 0
rsFindPendingForTM.CursorLocation = 2
rsFindPendingForTM.LockType = 1
rsFindPendingForTM.Open()
rsFindPendingForTM_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = 10
Repeat1__index = 0
rsFindPendingForTM_numRows = rsFindPendingForTM_numRows + Repeat1__numRows
%>
<SCRIPT LANGUAGE="JavaScript">
<!--
// by Nannette Thacker
// http://www.shiningstar.net
// This script checks and unchecks boxes on a form
// Checks and unchecks unlimited number in the group...
// Pass the Checkbox group name...
// call buttons as so:
// <input type=button name="CheckAll" value="Check All"
//onClick="checkAll(document.myform.list)">
// <input type=button name="UnCheckAll" value="Uncheck All"
//onClick="uncheckAll(document.myform.list)">
// -->
<!-- Begin
function checkAll(field)
{
for (i = 0; i < field.length; i++)
field.checked = true ;
}
function uncheckAll(field)
{
for (i = 0; i < field.length; i++)
field.checked = false ;
}
End -->
</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Approved by TM</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
.style1 {font-weight: bold}
.style3 {
font-size: 18pt;
font-family: "Monotype Corsiva";
color: #000099;
font-weight: bold;
}
.style4 {color: #FFFFFF}
-->
</style>
</head>
<body>
<div align="center"><br>
<br>
<br>
<span class="style3">Hello <%Response.Write Request.Cookies("TMIdent")%>, you have the following exceptions to approve</span></div>
<br>
<br>
<br>
<br>
<br>
<br>
<form action="test1.asp" method="post" name="frmSubmitTM" id="frmSubmitTM">
<table align="center" frame="box" rules="rows">
<tr>
<td align="center" nowrap bgcolor="#0000CC" class="style1 style4">Date</div></td>
<td align="center" nowrap bgcolor="#0000CC" class="style1 style4">Csr Name</div></td>
<td align="center" nowrap bgcolor="#0000CC" class="style1 style4">Start Time</div></td>
<td align="center" nowrap bgcolor="#0000CC" class="style1 style4">End Time</div></td>
<td align="center" nowrap bgcolor="#0000CC" class="style1 style4">Code</div></td>
<td align="center" nowrap bgcolor="#0000CC" class="style1 style4">Csr Comment</div></td>
<td align="center" nowrap bgcolor="#0000CC" class="style1 style4">Approved</div></td>
</tr>
<div align="center">
<%
While ((Repeat1__numRows <> 0) AND (NOT rsFindPendingForTM.EOF))
count = count+1
%>
</div>
<tr>
<td><div align="center"><%=(rsFindPendingForTM.Fields.Item("ActualDate").Value)%></div></td>
<td><div align="left"><%=(rsFindPendingForTM.Fields.Item("CSRName").Value)%></div></td>
<td><div align="center"><%=(rsFindPendingForTM.Fields.Item("HourStart").Value)%>:<%=(rsFindPendingForTM.Fields.Item("MinStart").Value)%></div></td>
<td><div align="center"><%=(rsFindPendingForTM.Fields.Item("HourEnd").Value)%>:<%=(rsFindPendingForTM.Fields.Item("MinEnd").Value)%></div></td>
<td><div align="center"><%=(rsFindPendingForTM.Fields.Item("Code").Value)%></div></td>
<td><div align="left"><%=(rsFindPendingForTM.Fields.Item("Comments").Value)%></div></td>
<td><div align="center">
<input name="chkBoxValue" type="checkbox" value="<%=(rsFindPendingForTM.Fields.Item("TransactionID").Value)%>">
</div></td>
</tr>
<div align="center">
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsFindPendingForTM.MoveNext()
Wend
%>
<input name="txtTotalNum" type="hidden" value=<%=count%> readonly="true">
</div>
</table>
<div align="center">
<input type="button" name="CheckAll" value="Check All"
onClick="checkAll(document.frmSubmitTM.chkBoxValue)">
<input type="button" name="UnCheckAll" value="Uncheck All"
onClick="uncheckAll(document.frmSubmitTM.chkBoxValue)">
</div>
<div align="right">
<input name="Submit" type="submit" value="Submit">
</div>
</form>
</body>
</html>
<%
rsFindPendingForTM.Close()
Set rsFindPendingForTM = Nothing
%>
I then call my test1.asp page:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>test1</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<%
Response.Buffer = True
choices = Request.Form("chkBoxValue")
choices = Split(choices,",")
Dim i
Dim srtText
Dim strMessage
strText = "yes"
For i = 0 to Ubound(choices)
set commUpdate = Server.CreateObject("ADODB.Command")
commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=i:\websites\bmportail\db\exceptionmanagement.mdb;" & ";Persist Security Info=False"
commUpdate.CommandText = "UPDATE tblHistoryCsr SET TMAccept = '" & strText & "' WHERE numberID = " & choices(i)
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
' rs.MoveFirst
' rs("TMAccept")="yes"
' rs.Update
Next
strMessage = i & " Records Updated"
Response.Write(strMessage)
%>
</body>
</html>
------------------------------------
So what happens now, is that it is aware that it must update 7 records, but does nothing at all.
When I replace the my connection script with this:
Dim strSQL
strSQL = "UPDATE tblHistoryCsr SET TMAccept = '" & strText & "' WHERE TransactionID = '" + member(i) + "' "
objConn.Execute strSQL
Then it inserts one record into the database, and that being the last one - or the rist number in the array.
Basically all I want to do is take the information from the table and update the database with yes/no or 1/0 or just a word, I don't care, so that I can sort the information later whether the person has approved it or no.
Thanks for your help :)
|
|
| |
|
|
|
|
Re: ASP VBScript Update Database with Checkboxes - 8/25/2004 7:47:58 AM
|
|
|
|
| |
GARM
Posts: 2
Score: 0
Joined: 8/20/2004
From:
Status: offline
|
I've tried two other things now, see the following:
asp page with my table of information...
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/exceptiondb.asp" -->
<%
Dim rsStatus
rsStatus = "PendingTM"
%>
<%
Dim rsFindPendingForTM__MMColParam
rsFindPendingForTM__MMColParam = "1"
If (Request.Cookies("TMIdent") <> "") Then
rsFindPendingForTM__MMColParam = Request.Cookies("TMIdent")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_exceptiondb_STRING
Recordset1.Source = "SELECT * FROM tblHistoryCsr WHERE TMname = '" + Replace(rsFindPendingForTM__MMColParam, "'", "''") + "' AND Accept LIKE '%" + Replace(rsStatus, "'", "''") + "%' ORDER BY ActualDate ASC"
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
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>testdocument2</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script type="text/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>
<br>
<form name="form1" method="post" action="testdocument.asp">
<table width="500" border="0" cellpadding="0" cellspacing="1" bgcolor="#666699">
<tr>
<td><table width="500" border="0" cellpadding="0" cellspacing="6" bgcolor="#EEEFF2">
<tr>
<td width="37"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">RecID</font></strong></td>
<td width="217"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">Date</font></strong></td>
<td width="226"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">CSR Name</font></strong></td>
<td width="226"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">Start Time</font></strong></td>
<td width="226"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">End Time</font></strong></td>
<td width="226"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">Code</font></strong></td>
<td width="226"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">CSR Comment</font></strong></td>
<td width="226"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">Approved</font></strong></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<% intRecID =(Recordset1.Fields.Item("numberID").Value) ' Store the current RecordID in a variable %>
<tr>
<td nowrap><font size="1" face="Arial, Helvetica, sans-serif"><%= intRecID %><input name="hidRecID<%= intRecID %>" type="hidden" value="<%= intRecID %>" size="5"></font></td>
<td nowrap><font size="1" face="Arial, Helvetica, sans-serif"><input name="txtDate<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("ActualDate").Value)%>" size="20"></font></td>
<td nowrap><font size="1" face="Arial, Helvetica, sans-serif"><input name="txtCsrName<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("CSRName").Value)%>" size="20"></font></td>
|
|
| |
|
|
|
| |
|
|
 |
|
 |
|
|
Printable Version
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread