update records in the database by checkbox

Sakina · September 9th, 2006, 01:56 AM

Hello
I'm trying to update database by cliking the checkbox.
I know I have to use array but I don't know how and where to place the code:
this is the code of my page
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim rsAnswers3
Dim rsAnswers3_numRows

Set rsAnswers3 = Server.CreateObject("ADODB.Recordset")
rsAnswers3.ActiveConnection = MM_survey_conn_STRING
rsAnswers3.Source = "SELECT answers.answer_no, answers.Question_No, answers.answers, answers.counts FROM answers, questions WHERE questions.Question_No= answers.Question_No and questions.question_no=3"
rsAnswers3.CursorType = 0
rsAnswers3.CursorLocation = 2
rsAnswers3.LockType = 1
rsAnswers3.Open()

rsAnswers3_numRows = 0
%>
<%
Dim rsQues3
Dim rsQues3_numRows

Set rsQues3 = Server.CreateObject("ADODB.Recordset")
rsQues3.ActiveConnection = MM_survey_conn_STRING
rsQues3.Source = "SELECT * FROM questions WHERE question_no =3"
rsQues3.CursorType = 0
rsQues3.CursorLocation = 2
rsQues3.LockType = 1
rsQues3.Open()

rsQues3_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

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

Repeat2__numRows = -1
Repeat2__index = 0
rsAnswers3_numRows = rsAnswers3_numRows + Repeat2__numRows
%>
<html>
<head>
<title>Question3</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width="100%" border="0">
  <tr>
    <td><p><strong><%=(rsQues3.Fields.Item("About").Va lue)%>:</strong></p>
      <p><%=(rsQues3.Fields.Item("Question").Value)%></p></td>
  </tr>
  <tr>
    <td height="152"> <form method="get" action="http://localhost:88/survey_page/check.asp" name="form1">
        <table width="476" >
          <tr valign="baseline">
            <td width="81" align="right" nowrap> </td>
            <td colspan="2"> </td>
          </tr>
          <tr valign="baseline">
            <td nowrap align="right">Question No:</td>
            <td width="249"><%=(rsAnswers3.Fields.Item("Question_N o").Value)%></td>
            <td width="130">Counts:</td>
          </tr>
          <%
While ((Repeat1__numRows <> 0) AND (NOT rsAnswers3.EOF))

%>
          <tr valign="baseline">
            <td nowrap align="right"> </td>
            <td valign="top">
              <input type="checkbox" name="answers" value="<%=(rsAnswers3.Fields.Item("answer_no").Val ue)%>" >
              <option value="<%=(rsAnswers3.Fields.Item("answer_no").Val ue)%>"><%=(rsAnswers3.Fields.Item("answers").Value )%></option>       </br> </td>
            <td valign="top"><%=(rsAnswers3.Fields.Item("counts"). Value)%></td>
          </tr>
          <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsAnswers3.MoveNext()
Wend
%>
          <tr valign="baseline">
            <td nowrap align="right"> </td>
            <td colspan="2"><input type="submit" name="Submit" value="Submit">
            </td>
          </tr>
        </table>
        <input type="hidden" name="MM_update" value="form1">
         <input type="hidden" name="allBoxes" value="
        <%
While ((Repeat2__numRows <> 0) AND (NOT rsAnswers3.EOF))
%>
       <%=(rsAnswers3.Fields.Item("answer_no").Value)%>
        <%
  Repeat2__index=Repeat2__index+1
  Repeat2__numRows=Repeat2__numRows-1
  rsAnswers3.MoveNext()
Wend
%>">
      </form></td>
  </tr>
  <tr>
    <td height="29"> <div align="center"><a href="http://localhost:88/survey_page/question2.asp">Previous</a>
          <a href="http://localhost:88/survey_page/test.asp">Next</a></div></td>
  </tr>
</table>

<p> </p>
</body>
</html>

<%
rsQues3.Close()
Set rsQues3 = Nothing
%>
<%
rsAnswers3.Close()
Set rsAnswers3 = Nothing
%>

thank you

mat41 · September 9th, 2006, 10:12 PM

Rather that go through you many lines of code, did you know:
>If you name your checkboxes the same and the values are ID's. These values will be posted as a commer delimited string? Therfore you you run an:
"UPDATE tbleNem SET fieldName = someValue WHERE ID IN (request.form("checkBoxName"));"

mmmmm. I just read you post again. You want to execute an update if a checkbox is clicked? If so; when its checkeed, unchecked or both?

I do something like this:

<input type="checkbox" name="Categories" onClick="updateDB(this,<%= RS("ID") %>);" ...>

once this box is checked or unchecked it call this JS function:

function delCat(oneelement, catId)
{
var status = 'unChecked';
if (oneelement.checked)
{
     //box is checked
     //alert('You have ' + status + ': ' + catId);
     status = 'checked';
    }
    else
    {
     //box unchecked
     //alert('You have ' + status + ': ' + catId);
     theForm.id.value = catId;
     theForm.removeCat.value = "yes";
     theForm.action = 'pageToDoYourUpdate.asp?someQSnAME=SomeValue;
     theFprm.submit();
}
}

This enables you to do someting if checked or somwethig elae if unchecked. I use it, works well...

Wind is your friend
Matt

Sakina · September 9th, 2006, 11:38 PM

Heloo again,
thank you very much for ur time.
I followed ur code but in the update page how can i call the checkboxes
I used this code but It update all the answers not only the selected one
<%
set cmdUpdateAns = Server.CreateObject("ADODB.Command")
cmdUpdateAns.ActiveConnection = MM_survey_conn_STRING
cmdUpdateAns.CommandText = "UPDATE answers SET counts = counts + 1 WHERE answer_no = " & request.QueryString("answers")
cmdUpdateAns.CommandType = 1
cmdUpdateAns.CommandTimeout = 0
cmdUpdateAns.Prepared = true
cmdUpdateAns.Execute()
%>
so what should i put instead of & request.querystring('answrs')

thank you again