update multiple rows in a table in dreamweaver / asp

Here is the problem:

When using the repeat region on dreamweaver, It wont allow me to update multiple record. I can see them, I can edit them, but when I hit submit, nothing happens.

here is my page:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/nelson.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")) = "form1" And CStr(Request("MM_recordId")) <> "") Then

  MM_editConnection = MM_nelson_STRING
  MM_editTable = "dbo.answerstable"
  MM_editColumn = "answersID"
  MM_recordId = "" + Request.Form("MM_recordId") + ""
  MM_editRedirectUrl = "updatecomplete.asp"
  MM_fieldsStr  = "answerstext|value"
  MM_columnsStr = "answers|',none,''"

  ' 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
    MM_editCmd.ActiveConnection.Close

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

End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_nelson_STRING
Recordset1.Source = "SELECT * FROM dbo.answerstable"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 20
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="POST" action="<%=MM_editAction%>">
  <table width="100%" border="1">
    <tr>
      <td width="16%">ID</td>
      <td width="84%">Answer</td>
    </tr>
    <%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
      <tr>
        <td><%=(Recordset1.Fields.Item("answersID").Value)%></td>
        <td><label>
          <input name="answerstext" type="text" id="answerstext" value="<%=((Recordset1.Fields.Item("answers").Value))%>" size="120" />
        </label></td>
      </tr>
      <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>

    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><label>
        <input type="submit" name="Submit" value="Submit" />
      </label></td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p>&nbsp;</p>

  <input type="hidden" name="MM_update" value="form1">
  <input type="hidden" name="MM_recordId" value="<%= Recordset1.Fields.Item("answersID").Value %>">
</form>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

-----------------------------------------------

Can someone tell me what i can do to fix this where it will update multiple records at one time???

Please be specific based on my code.

Judson
LVL 3
judsonmusicAsked:
Who is Participating?
 
carlmahonConnect With a Mentor Commented:
Your original code was set to display all the records but update only one.

As mentioned above
<input type="hidden" name="MM_recordId" value="<%= Recordset1.Fields.Item("answersID").Value %>">

Was the value the form was looking for and used to build the “where” clause in the SQL query. But this value was set outside the loop and would contain only one value.


Changes on the Form
1.      added the answersID as a hidden field for each row.
2.      changed the field names to include the loop count. This is so each field was unique and could be called separately.
3.      added the total loop count to another hidden field so we could later know how many records we need to update.

 <td><input type="hidden" name="rec_<%=Repeat1__index%>" value="<%=recordID%>" />
          <%=recordID%>
          </td>
        <td><label>
          <input name="answers_<%=Repeat1__index%>" type="text" value="<%=((Recordset1.Fields.Item("answers").Value))%>" size="120" />
       
Changes to the update code
1.      Checked for the total row count and if it is > 0 then process the update.
2.      Loop through 0 to the total count and pull the form value for each.
3.      Build a SQL statement for each row and update the table

For MM_i = 0 to MM_recordCount
       ' create the sql update statement
       recordID = Request.Form("rec_"&MM_i)
       answertext = Request.Form("answers_"&MM_i)
     
      If Len(recordID) > 0 then
           MM_editQuery = "update " & MM_editTable & " set answers = '" & answertext & "' where answersID = " & recordID
          MM_editCmd.CommandText = MM_editQuery
         MM_editCmd.Execute


To fix the redirect, uncomment the Response.Redirect line in the code.

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
0
 
iscodeCommented:
start with looking at your MM_editQuery by spiting it out
add this line --> response.write MM_editQuery

'before this

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
    MM_editCmd.ActiveConnection.Close
0
 
iscodeCommented:
'if it does not work do it this way

If (Not MM_abortEdit) Then
      Response.Write MM_editQuery
       
      'everything else below here is commented.  This will enable the query string
      '(MM_editQuery) to be shown in the browser window so you can check your
      'syntax and determine where the problem is in the query/command.
      '
      'When you've fixed what's causing the issue, you can then unremark/uncomment
      'all of the code below and delete this comment block.
      '
      ' execute the update
      'Set MM_editCmd = Server.CreateObject("ADODB.Command")
      'MM_editCmd.ActiveConnection = MM_editConnection
      'MM_editCmd.CommandText = MM_editQuery
      'MM_editCmd.Execute
      'MM_editCmd.ActiveConnection.Close
      '
      'If (MM_editRedirectUrl <> "") Then Response.Redirect(MM_editRedirectUrl)
   End If

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
carlmahonCommented:
The issue is that you want to update each record “answer” but the form is checking and building an update query based on one ID:

<input type="hidden" name="MM_recordId" value="<%= Recordset1.Fields.Item("answersID").Value %>">

To change to multiple update it would take a lot of rework. I will try and see if some changes will work while retaining the proper Dreamweaver format.
0
 
iscodeCommented:
Multiple Update/Delete of Records    
Completed pages and code for this tutorial.
http://www.sensiomedia.nl/page14403532.aspx
0
 
carlmahonCommented:
here is the same style applied to your code. Unfortunately I started before I saw iscode's post

<%@LANGUAGE="VBSCRIPT"%>%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/nelson.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_recordCount
Dim recordID
Dim answertext

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")) = "form1" And CStr(Request("MM_recordCount")) <> "") Then

  MM_editConnection = MM_nelson_STRING
  MM_editTable = "dbo.answerstable"
  MM_editColumn = "answersID"
  MM_recordCount = Request.Form("MM_recordCount")
  MM_editRedirectUrl = "updatecomplete.asp"
  'MM_fieldsStr  = "answerstext|value"
  'MM_columnsStr = "answers|',none,''"

  ' 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_recordCount")) <> "") Then
  Set MM_editCmd = Server.CreateObject("ADODB.Command")
  MM_editCmd.ActiveConnection = MM_editConnection
  For MM_i = 0 to MM_recordCount
        ' create the sql update statement
        recordID = Request.Form("rec_"&MM_i)
        answertext = Request.Form("answers_"&MM_i)
      
       If Len(recordID) > 0 then
             MM_editQuery = "update " & MM_editTable & " set answers = '" & answertext & "' where answersID = " recordID
      
          ' execute the update
          
          
          MM_editCmd.CommandText = MM_editQuery
          MM_editCmd.Execute
          
      End If
  Next 'MM_i
  MM_editCmd.ActiveConnection.Close
  Set MM_editCmd = Nothing
 
            If (MM_editRedirectUrl <> "") Then
            Response.Redirect(MM_editRedirectUrl)
          End If
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_nelson_STRING
Recordset1_cmd.CommandText = "SELECT * FROM dbo.answerstable"
Recordset1_cmd.Prepared = true

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 20
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="POST" action="<%=MM_editAction%>">
  <table width="100%" border="1">
    <tr>
      <td width="16%">ID</td>
      <td width="84%">Answer</td>
    </tr>
    <%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
      recordID = Recordset1.Fields.Item("answersID").Value
%>
      <tr>
        <td><input type="hidden" name="rec_<%=Repeat1__numRows%>" value="<%=recordID%>" />
            <%=recordID%>
            </td>
        <td><label>
          <input name="answers_<%=Repeat1__numRows%>" type="text" value="<%=((Recordset1.Fields.Item("answers").Value))%>" size="120" />
        </label></td>
      </tr>
      <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>

    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><label>
        <input type="submit" name="Submit" value="Submit" />
      </label></td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p>&nbsp;</p>

  <input type="hidden" name="MM_update" value="form1">
  <input type="hidden" name="MM_recordCount" value="<%=Repeat1__numRows%>" />
</form>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
0
 
judsonmusicAuthor Commented:
carl, your code that you sent me is not working.

I removed all of the notes '

It doesnt work, its asking for end statements, etc.

Please re-check and resend. If you could, do me a favor and send the actual working product. Thanks

Judson

I reeally need a solution here.
0
 
carlmahonCommented:
Since I don't have your connection/database it is hard to test completely.

I went through the code, fixed the errors and now it runs on my system with multiple updates.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/nelson.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_recordCount
Dim recordID
Dim answertext

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")) = "form1" And Request("MM_recordCount")>0) Then

  MM_editConnection = MM_nelson_STRING
  MM_editTable = "dbo.answerstable"
  MM_editColumn = "answersID"
  MM_recordCount = Request.Form("MM_recordCount")
  MM_editRedirectUrl = "updatecomplete.asp"

  ' 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 Request("MM_recordCount")>0) Then
  Set MM_editCmd = Server.CreateObject("ADODB.Command")
  MM_editCmd.ActiveConnection = MM_editConnection
  MM_i = 0
  For MM_i = 0 to MM_recordCount
        ' create the sql update statement
        recordID = Request.Form("rec_"&MM_i)
        answertext = Request.Form("answers_"&MM_i)
      
       If Len(recordID) > 0 then
             MM_editQuery = "update " & MM_editTable & " set answers = '" & answertext & "' where answersID = " & recordID
            MM_editCmd.CommandText = MM_editQuery
          MM_editCmd.Execute
      End If
  Next 'MM_i
  MM_editCmd.ActiveConnection.Close
  Set MM_editCmd = Nothing
 
            If (MM_editRedirectUrl <> "") Then
          ' Response.Redirect(MM_editRedirectUrl)
          End If
End If

%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_nelson_STRING
Recordset1_cmd.CommandText = "SELECT * FROM dbo.answerstable"
Recordset1_cmd.Prepared = true

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 20
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="POST" action="<%=MM_editAction%>">
  <table width="100%" border="1">
    <tr>
      <td width="16%">ID</td>
      <td width="84%">Answer</td>
    </tr>
    <%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
      recordID = Recordset1.Fields.Item("answersID").Value
%>
      <tr>
        <td><input type="hidden" name="rec_<%=Repeat1__index%>" value="<%=recordID%>" />
            <%=recordID%>
            </td>
        <td><label>
          <input name="answers_<%=Repeat1__index%>" type="text" value="<%=((Recordset1.Fields.Item("answers").Value))%>" size="120" />
        </label></td>
      </tr>
      <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>

    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><label>
        <input type="submit" name="Submit" value="Submit" />
      </label></td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <p>&nbsp;</p>

  <input type="hidden" name="MM_update" value="form1">
  <input type="hidden" name="MM_recordCount" value="<%=Repeat1__index%>" />
</form>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
0
 
judsonmusicAuthor Commented:
That works. Can you explain to me breifly what you did?

Also, I need to know where I can put code to direct to another page when  user clicks submit. I tried the onclick but then the code stopped working for me.


Judson
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.