Solved

Update multiple rows

Posted on 2004-08-27
12
290 Views
Last Modified: 2010-04-25
This question has been asked plenty of times on EE, mostly in ASP.

My problem is that I can't code in ASP - so I don't understand the answers....

What I have is a page of 'players', columns are ID, Name and Points.  It has a repeat region to display the list of records from an Access DB table.

On my list of players, I want the last column, points, to be editable so that a user can modify the points for a list of players, and then click submit once to update all and multiple rows of the database in one go.

Like I said, I know this can be done in ASP, and my page is using ASP VBScript...and I have trawled EE pages long and hard, but when I try and relate any previous answers to my code, they don't look anything like the same, probably because of the way DW marks up the pages.

SO.... there obviously isn't a way of doing this in native DW.... BUT I was wondering if anyone knows of an nice little EXTENSION that someone has written that will achive this ??!!

Surely someone has done this with a DW extension to save all us poor non-coding people the embarassement of not being able to do a relatively simple thing on our pages ?!!

Heres hoping ....
0
Comment
Question by:MPWOOD
  • 6
  • 5
12 Comments
 

Author Comment

by:MPWOOD
Comment Utility
increasing points
0
 
LVL 10

Expert Comment

by:rockmansattic
Comment Utility
no , I know of no extensions becasue of the variables used.  
0
 
LVL 10

Expert Comment

by:rockmansattic
Comment Utility
Hand codeing and DW are similar, the MAJOR difference is that DW may put stuff in that you dont need, and variable names will be different (because DW has default names)

It shouldnt be too hard to adjust other code to meet your needs.

Rockman
0
 

Author Comment

by:MPWOOD
Comment Utility
Hi Rockman,

Thanks for this, perhaps you could help me adjust my code then ??!!

I have posted the html of the page in question to www.mattwood.co.uk/update_players.txt

My recordset is called 'players', from a connection 'ff', from table 'player'.

I have columns in my repeat region player_id; name; club; position; value; and points.

It is the last column 'points' that is a text field displaying the editable box for the user to update the points.

Currently the page lists the players in the repeat region as it should - but the submit button isn't displaying.

If you could give me some clues as to where I should be putting an update record loop then I would be most grateful !

Please let me know if I need to supply any more info about the page.

Thanks

Matt
0
 
LVL 10

Expert Comment

by:rockmansattic
Comment Utility
ill look at it after lunch.

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:MPWOOD
Comment Utility
:-)
0
 
LVL 10

Expert Comment

by:rockmansattic
Comment Utility
just a try,
replace all above the html with this.
Dont think this will work, but a quick shot before lunch
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/ff.asp" -->
<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers=""
MM_authFailedURL="home.asp"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
  If (true Or CStr(Session("MM_UserAuthorization"))="") Or _
         (InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization"))>=1) Then
    MM_grantAccess = true
  End If
End If
If Not MM_grantAccess Then
  MM_qsChar = "?"
  If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
  MM_referrer = Request.ServerVariables("URL")
  if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
  MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
  Response.Redirect(MM_authFailedURL)
End If
%>
<%
' *** 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 = ""
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
players_numRows = players_numRows + Repeat1__numRows
%>
<%
' *** Update Record: set variables

If (CStr(Request("MM_update")) = "players" And CStr(Request("MM_recordId")) <> "") Then
 
While ((Repeat1__numRows <> 0) AND (NOT players.EOF))

  MM_editConnection = MM_ff_STRING
  MM_editTable = "player"
  MM_editColumn = "player_id"
  MM_recordId = "" + Request.Form("MM_recordId") + ""
  MM_editRedirectUrl = "home.asp"
  MM_fieldsStr  = "textfield|value"
  MM_columnsStr = "points|none,none,NULL"

  ' 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

  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  players.MoveNext()
WendEnd If

%>
<%
Dim players
Dim players_numRows

Set players = Server.CreateObject("ADODB.Recordset")
players.ActiveConnection = MM_ff_STRING
players.Source = "SELECT * FROM player "
players.CursorType = 0
players.CursorLocation = 2
players.LockType = 1
players.Open()

players_numRows = 0
%>
0
 

Author Comment

by:MPWOOD
Comment Utility
your're right, sorry - it doesn't.

I can validate the recordset, but the update record server behaviour shows and error, and when I try to validate it (double click it) it says 'Before using this server behavour, please create a form'

When I view the page in design mode it's blank...

thanks for your help so far.

Matt
0
 

Author Comment

by:MPWOOD
Comment Utility
rockman, in case you post anything else today, I'm offline until tomorrow (I'm a few hours ahead of you!)
0
 
LVL 10

Accepted Solution

by:
rockmansattic earned 300 total points
Comment Utility
OK try this

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/ff.asp" -->
<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers=""
MM_authFailedURL="home.asp"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
  If (true Or CStr(Session("MM_UserAuthorization"))="") Or _
         (InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization"))>=1) Then
    MM_grantAccess = true
  End If
End If
If Not MM_grantAccess Then
  MM_qsChar = "?"
  If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
  MM_referrer = Request.ServerVariables("URL")
  if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
  MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
  Response.Redirect(MM_authFailedURL)
End If
%>
<%
' *** 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 = ""
%>

<%
Dim players
Dim players_numRows

Set players = Server.CreateObject("ADODB.Recordset")
players.ActiveConnection = MM_ff_STRING
players.Source = "SELECT * FROM player "
players.CursorType = 0
players.CursorLocation = 2
players.LockType = 1
players.Open()

players_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
players_numRows = players_numRows + Repeat1__numRows
%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Update player points</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
.style1 {
      font-family: Tahoma;
      font-size: 11px;
}
.style2 {
      font-size: 14px;
      font-weight: bold;
}
.style5 {font-size: 12; font-weight: bold; }
.style7 {font-size: 12}
-->
</style>

</head>

<body>
<p class="style1 style2">Players Page </p>
<form ACTION="<%=MM_editAction%>" METHOD="POST" enctype="multipart/form-data" name="players" class="style1" id="players">
  <label></label>
  <p> Total Players Registered:: </p>
  <p>Players with the most points are listed first. Click on a title to sort
    by a different column and to toggle ascending/descending.
    <label></label>
  </p>
  <table width="100%"  border="0" cellspacing="0" cellpadding="0">
   


  </table>
  <table width="100%"  border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td><span class="style5">Player ID </span></td>
      <td><span class="style5">Name</span></td>
      <td><span class="style5">Club</span></td>
      <td><span class="style5">Position</span></td>
      <td><span class="style5">Value</span></td>
      <td><span class="style5">Points</span></td>
    </tr>
    <%
While ((Repeat1__numRows <> 0) AND (NOT players.EOF))
%><% intRecID =(players.Fields.Item("player_id").Value) ' Store the current RecordID in a variable %>
    <tr>
      <td><span class="style7"><%=(players.Fields.Item("player_id").Value)%></span></td>
      <td><span class="style7"><%=(players.Fields.Item("name").Value)%></span></td>
      <td><span class="style7"><%=(players.Fields.Item("club").Value)%></span></td>
      <td><span class="style7"><%=(players.Fields.Item("position").Value)%></span></td>
      <td><span class="style7"><%=(players.Fields.Item("value").Value)%></span></td>
      <td> <span class="style7">
        <label>
            <input name="points<%= intRecID %>" type="text" value="<%=(players.Fields.Item("points").Value)%>">
       
        </label>
      </span></td>
    </tr>
      <%
' *** Update Record: set variables

If (CStr(Request("MM_update")) = "players" And CStr(Request("MM_recordId")) <> "") Then

  MM_editConnection = MM_ff_STRING
  MM_editTable = "player"
  MM_editColumn = "player_id"
  MM_recordId = "" + Request.Form("MM_recordId") + ""
  MM_editRedirectUrl = "home.asp"
  MM_fieldsStr  = "textfield|value"
  MM_columnsStr = "points|none,none,NULL"

  ' 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
%>
    <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  players.MoveNext()
Wend
%>
  </table>
  <p>&nbsp;</p>
  <p>
    <input type="submit" name="Submit" value="Click when done updating">
  </p>

    <input type="hidden" name="MM_update" value="players">
  <input type="hidden" name="MM_recordId" value="<%= players.Fields.Item("player_id").Value %>">
</form>
<p class="style1">&nbsp;</p>
<p class="style1">&nbsp; </p>
</body>
</html>
<%
players.Close()
Set players = Nothing
%>

NOTICE that the new name of the points textarea is now changed. line 137

Let me know if you get errors
Rockman
0
 

Author Comment

by:MPWOOD
Comment Utility
Sorry about this - I've been away combined with PC problems.

I did have a look at rockmansattic answer and will come back to it again when I have more time as I can't figure it out just yet.

He deserves the points for taking the time to look at my problem and coming up with a some code (which will probably work if only I get to understand ASP better!)
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

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…
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

728 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

10 Experts available now in Live!

Get 1:1 Help Now