Solved

update multiple records at once! please help me out!

Posted on 2006-11-15
5
199 Views
Last Modified: 2010-08-05
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
5 Comments
 
LVL 70

Expert Comment

by:Jason C. Levine
Comment Utility
Hi Judson,

The built-in behaviors in DW will not update multiple records at once.  You either need to hand-code this or buy a third-party extension that will write the code for you.

Unfortunately, I don't do ASP, so I can't help you with the code.  Rouchie does and he should be checking this question at some point...

If you want to take a crack at doing it yourself, here are some articles that may help:

http://charon.co.uk/content.aspx?CategoryID=27&ArticleID=41
http://www.drdev.net/article01.htm
http://www.drdev.net/article11.asp

If you want to throw money at the problem:

http://www.felixone.it/
http://www.interaktonline.com/Products/Bundles/Kollection/Features/Details/Edit+multiple+records+at+the+same+time.html?id_ftr=265
0
 
LVL 16

Expert Comment

by:CWS (haripriya)
Comment Utility
Always use this,
    Request.Form("MM_recordId")

instead of this,
    Request("MM_recordId")
0
 
LVL 25

Accepted Solution

by:
Rouchie earned 500 total points
Comment Utility
>> Rouchie does and he should be checking this question at some point...
Good morning!

Hi judsonmusic
I agree with Jason about the multiple updating.  As far as I know the repeated region trick in Dreamweaver is really designed just for displaying rather than updating.  You've now hit a problem with the software that thousands of other users also hit at some point in time, and that's its limit to do what you want!

Your problem is one of the reasons a lot of ASP developers switch to ASP.NET.  ASP just plainly loops though the database and spits out a textbox for each ID, then it forgets all about the page, and has no idea how many textboxes it has just produced.

Although there may be better ways, the only way I can think of getting round the problem is to store each ID in an array while displaying each text box.  You would store it while displaying each field from the database.  Then you would have to store the array in a hidden field or in the Session object so it could be read when the form was submitted.  The following code amends what you have above:

Part 1 - Create a blank variable to store each database ID to update
            Add each ID to the list when the page is loaded
            Name each form textbox with the database ID
            Store the ID's ready for page submit
---------------------------------------------------------------------------------------------
<%
On Error resume Next
%>
<%
Dim IDString ' BLANK VARIABLE LIST
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
IDString = Recordset1.Fields.Item("answersID").Value & "," ' ADD ID TO LIST
%>
      <tr>
        <td>
               <%=(Recordset1.Fields.Item("answersID").Value)%>
        </td>
        <td>
            <label>
                 <input name="<%=(Recordset1.Fields.Item("answersID").Value)%>"
                           type="text"
                           id="<%=(Recordset1.Fields.Item("answersID").Value)%>"
                           value="<%=((Recordset1.Fields.Item("answers").Value))%>" size="120" />
            </label>
         </td>
      </tr>
      <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
' AFTER PAGE IS SUBMITTED - STORE THE ID LIST
Session("IDList") = IDString
%>
---------------------------------------------------------------------------------------------
Part 2 (On the next page)  -  Read the ID's back in
                                         Loop through them retrieving the appropriate form data and save

Dim IDString
IDString = Session("IDList") ' READ THE ID's BACK IN
Dim IDArray
IDArray = Split(IDString, ",") ' SPLIT THEM UP INTO A LIST OF SEPERATE ID's
IF UBound(IDString) > 1 then ' CHECK 1+ ID's EXIST
   '------------------------------------
   '  OPEN DATABASE CONNECTION HERE!
   ' -----------------------------------
   for i = 0 to ubound(IDArray) - 1 ' LOOP THROUGH AND RETRIEVE
      Dim mySQL
      mySQL = "UPDATE dbo.answerstable SET answers = '" & request.form(IDArray(i)) & "' WHERE answersID = " & IDArray(i)
      '------------------------------------
      '  EXECUTE UPDATE COMMAND HERE USING ABOVE SQL
      ' -----------------------------------
   next
   '------------------------------------
   '  CLOSE DATABASE CONNECTION HERE!
   ' -----------------------------------
End IF
0
 
LVL 3

Author Comment

by:judsonmusic
Comment Utility
Ok, number one, where would I put this code, and number 2 what if I did use asp.net???

How would I do it then????


Thanks


Judson
0
 
LVL 25

Expert Comment

by:Rouchie
Comment Utility
The first block replaces the block you have in the code you posted.

To make things simpler and to break the code down, I would get this page to post to a new page rather than back to itself.  So I'd change this:
   <form id="form1" name="form1" method="POST" action="<%=MM_editAction%>">
to this:
   <form id="form1" name="form1" method="POST" action="updatepage.asp">

The second block of code then goes in updatepage.asp, but it will need expanding to include success/failure messages.  If you are unsure of how to do this then it might be best tracking down some lessons on how to write the code yourself, as its a bit of a minefield.

As for ASP.NET, the whole approach to writing pages is different, so there's lots of new things to be aware of.  The 4Guys' tutorials are what I used to start doing this, although you will need to download the .NET framework from microsoft to run ASP.NET on your computer.

http://aspnet.4guysfromrolla.com/articles/040502-1.aspx
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now