Solved

update multiple rows in a table in dreamweaver / asp

Posted on 2006-11-15
9
501 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
Summernote and form validation 10 232
Select only the top record in a left join 13 48
Diminish Pop-up  in 3 seconds 7 70
Filktering Alphabetically 8 43
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

726 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