Solved

update multiple rows in a table in dreamweaver / asp

Posted on 2006-11-15
9
490 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

13 Experts available now in Live!

Get 1:1 Help Now