www.webdeveloper.com
Results 1 to 4 of 4

Thread: Stuck on Updating Multiple Records with Checkboxes

  1. #1
    Join Date
    Apr 2005
    Posts
    46

    Unhappy Stuck on Updating Multiple Records with Checkboxes

    Hello,

    I have a page which lists when rooms are booked. A checkbox is dynamically selected showing when the room is booked.

    I am trying to change update the records I change by ticking or unticking the checkbox.

    I am using dreamweavers terrible code!

    When I do this, I get an error on line 113


    Microsoft JET Database Engine error '80040e14'

    Syntax error (comma) in query expression 'ID = 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143'.

    /login/adminuserpages/editclasses5.asp, line 113


    [CODE]<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include file="../../Connections/users.asp" -->
    <!--#include file="../../Connections/dbconnect.asp" -->
    <!--#include file="../../Connections/design.asp" -->
    <%
    ' *** Edit Operations: declare variables

    Dim MM_editAction
    Dim MM_abortEdit
    Dim MM_editQuery
    Dim MM_editCmd

    Dim MM_editConnection
    Dim MM_editTable
    Dim MM_editRedirectUrl
    Dim MM_editColumn
    Dim MM_recordId

    Dim MM_fieldsStr
    Dim MM_columnsStr
    Dim MM_fields
    Dim MM_columns
    Dim MM_typeArray
    Dim MM_formVal
    Dim MM_delim
    Dim MM_altVal
    Dim MM_emptyVal
    Dim MM_i

    MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
    If (Request.QueryString <> "") Then
    MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
    End If

    ' boolean to abort record edit
    MM_abortEdit = false

    ' query string to execute
    MM_editQuery = ""
    %>
    <%
    ' *** Update Record: set variables

    If (CStr(Request("MM_update")) = "form2" And CStr(Request("MM_recordId")) <> "") Then

    MM_editConnection = MM_dbconnect_STRING
    MM_editTable = "main"
    MM_editColumn = "ID"
    MM_recordId = "" + Request.Form("MM_recordId") + ""
    MM_editRedirectUrl = "/login/adminuserpages/editclasses.asp"
    MM_fieldsStr = "checkbox|value"
    MM_columnsStr = "class|none,Yes,No"

    ' create the MM_fields and MM_columns arrays
    MM_fields = Split(MM_fieldsStr, "|")
    MM_columns = Split(MM_columnsStr, "|")

    ' set the form values
    For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
    Next

    ' append the query string to the redirect URL
    If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
    MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
    Else
    MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
    End If

    End If
    %>
    <%
    ' *** Update Record: construct a sql update statement and execute it

    If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "") Then

    ' create the sql update statement
    MM_editQuery = "update " & MM_editTable & " set "
    For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
    MM_formVal = MM_emptyVal
    Else
    If (MM_altVal <> "") Then
    MM_formVal = MM_altVal
    ElseIf (MM_delim = "'") Then ' escape quotes
    MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
    Else
    MM_formVal = MM_delim + MM_formVal + MM_delim
    End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
    MM_editQuery = MM_editQuery & ","
    End If
    MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
    Next
    MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId

    If (Not MM_abortEdit) Then
    ' execute the update
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    >> MM_editCmd.Execute <<<<<<<<<<<<<<<< Line 113
    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
    Response.Redirect(MM_editRedirectUrl)
    End If
    End If

    End If
    %>[CODE]

    Code:
          <div align="center" class="style8">
            <p>Edit Classes </p>
            <form action="/login/adminuserpages/editclasses.asp" method="get" name="form1" class="site">
              Filter
              <table width="90%"  border="0" cellspacing="2" cellpadding="2">
       <tr>
         <td width="23%"><div align="right">Day:
         </div></td>
         <td width="23%">       <div align="left">
           <select name="Day" id="Day">
                 <option value="Monday">Monday</option>
                 <option value="Tuesday">Tuesday</option>
                 <option value="Wednesday">Wednesday</option>
                 <option value="Thursday">Thursday</option>
                 <option value="Friday">Friday</option>
             </select>
         </div></td>
         <td width="18%"><div align="right">Period:</div></td>
         <td width="16%"><div align="left">
           <select name="Period" id="Period">
               <option value="1">1</option>
               <option value="2">2</option>
               <option value="3">3</option>
               <option value="4">4</option>
               <option value="5">5</option>
               <option value="6">6</option>
           </select>
         </div></td>
         <td width="20%"><div align="center">
           <input type="submit" name="Submit" value="Go">
         </div></td>
       </tr>
     </table>
                <br>
            </form>
            <form name="form2" method="POST" action="<%=MM_editAction%>">
              <table width="90%" border="0" cellpadding="2" cellspacing="1">
                <tr class="site">
                  <td><div align="center">Day</div></td>
                  <td><div align="center">Period</div></td>
                  <td><div align="center">Room</div></td>
                  <td><div align="center">Class</div></td>
                  </tr>
                <% While ((Repeat2__numRows <> 0) AND (NOT dbclass.EOF)) %>
                <tr onMouseOver="colorRow(this,1);" onMouseOut="colorRow(this,0);">
                  <td class="site"><div align="center"><%=(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" type="checkbox" disabled="true" value="checkbox" <%If (CStr((dbclass.Fields.Item("class").Value)) = CStr("True")) Then Response.Write("checked") : Response.Write("")%>>
                      <input name="MM_recordId" type="hidden" id="MM_recordId" value="<%=(dbclass.Fields.Item("ID").Value)%>">
                  </div></td>
                  </tr>
                <% 
      Repeat2__index=Repeat2__index+1
      Repeat2__numRows=Repeat2__numRows-1
      dbclass.MoveNext()
    Wend
    %>
              </table>
              <p>
                <input type="submit" name="Submit" value="Submit">
                <br>
                  <input type="hidden" name="MM_update" value="form2">
              </p>
              </form>
            <p align="center"><a href="/login/adminuserpages/default.asp"><img src="/images/arrow_left.gif" width="40" height="34" border="0"></a></p>
          </div>

    It doesn't seem to be updating so I put in response.write(MM_editQuery) and then on the next line response.end() to display the SQL statement it was executing:

    update main set class = No where ID = 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154

    This is wrong because some checkboxes are selected. It is trying to update all of them.

    Please help, Thanks

  2. #2
    Join Date
    Apr 2005
    Posts
    46
    Does anyone have any ideas?

    Thanks

  3. #3
    Join Date
    Dec 2004
    Posts
    8,637
    Using an unchecked checkbox as a signal to perform an update is not really a good plan for action. The reason is that an unchecked checkbox is not retransmitted back to the server side. Thus, your code has to determine that the checkbox was unchecked by first knowing that the checkbox was originally checked and is now not being returned from the client side in the POST data. Get it?
    Last edited by phpnovice; 05-16-2005 at 03:11 PM. Reason: Changed bold "now" from "not" to correct.

  4. #4
    Join Date
    Apr 2005
    Posts
    46
    Sort of, I'll just keep it as updating htem separately.

    Thanks for your wisdom!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles