Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

update multiple rows in a table in dreamweaver / asp

Posted on 2006-11-15
9
Medium Priority
?
531 Views
Last Modified: 2010-05-04
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
0
Comment
Question by:judsonmusic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 6

Expert Comment

by:iscode
ID: 17950165
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
 
LVL 6

Expert Comment

by:iscode
ID: 17950373
'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
 
LVL 4

Expert Comment

by:carlmahon
ID: 17950976
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:iscode
ID: 17951240
Multiple Update/Delete of Records    
Completed pages and code for this tutorial.
http://www.sensiomedia.nl/page14403532.aspx
0
 
LVL 4

Expert Comment

by:carlmahon
ID: 17951265
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
 
LVL 3

Author Comment

by:judsonmusic
ID: 17951447
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
 
LVL 4

Expert Comment

by:carlmahon
ID: 17951544
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
 
LVL 3

Author Comment

by:judsonmusic
ID: 17956152
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
 
LVL 4

Accepted Solution

by:
carlmahon earned 2000 total points
ID: 17957411
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question