logo-dw

Go Back   Dreamweaver Club Forums > Hand Coders Forum > General
Register FAQ Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Display Modes
Old 01-20-2007, 09:37 PM   #1
BenS
 
Join Date: Jan 2007
Posts: 9
Default Problem Deleting Correct Record From Table

Hi,
I am having an issue with a drop down box to select the record I need to delete from a table.
It is a simple table called users and contains 3 fields, ID (Primary Key), username, and password.
The dreamweaver page I have constructed to delete records, is a drop down menu which is dynamic. I have set the values to ID, and labels to username, and there is a static option with the label 'select user' and the value left blank.
To delete there is a button with the delete record server behaviour where the unique key column is ID and is deleted by submitting 'form 2' (the name of the form that contains the drop down menu and submit button). After deleting it links to the same page.
The problem that I am having is that it deletes the first person off the list and not the one selected. Is this a common problem?
I hope you can help me, I have found it to be an annoying problem...
Ben
BenS is offline   Reply With Quote
Old 01-21-2007, 03:19 PM   #2
domedia
Administrator
domedia's Avatar
 
Join Date: Dec 2003
Posts: 9,730
Default

Bens, it's impossible for anyone to troubleshoot your code if you don't provide it.
domedia is offline   Reply With Quote
Old 01-21-2007, 09:45 PM   #3
BenS
 
Join Date: Jan 2007
Posts: 9
Default

Hi, sorry I didn't provide the code as I thought it was a dreamweaver settings issue. Maybe I have posted in the wrong part of the forum. Anyway, there is a few table on the page in question, and I have selected the code that I think is relevant:
Quote:
<table width="200" border="0">
<tr>
<td bgcolor="#FFFFFF">Username</td>
<td></label>
<select name="select">
<option value="">Select User</option>
<%
While (NOT Recordset2.EOF)
%><option value="<%=(Recordset2.Fields.Item("User_ID").Value )%>"><%=(Recordset2.Fields.Item("username").Value) %></option>
<%
Recordset2.MoveNext()
Wend
If (Recordset2.CursorType > 0) Then
Recordset2.MoveFirst
Else
Recordset2.Requery
End If
%>
</select>
</td>
</tr>
<tr>
<td bgcolor="#FFFFFF">&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td bgcolor="#FFFFFF">&nbsp;</td>
<td><input type="submit" name="Submit2" value="Delete" /></td>
</tr>
</table>
I hope that will help someone answer my query, thanks again
Ben
BenS is offline   Reply With Quote
Old 01-21-2007, 10:45 PM   #4
davidj
davidj's Avatar
 
Join Date: Sep 2005
Location: The Toon (newcastle upon Tyne)
Posts: 8,256
Default

this does not help

this is the form. We need to see the script that process the form data and deletes the record
__________________
Would you like to learn PHP from me? Check out -> www.codezenith.co.uk
davidj is offline   Reply With Quote
Old 01-22-2007, 05:42 PM   #5
BenS
 
Join Date: Jan 2007
Posts: 9
Default

Sorry about my ineptitude....
This is the code for the whole page, please note that there is also a table that adds users:

Code:
<%@LANGUAGE="VBSCRIPT"%>
<%
' *** Logout the current user.
MM_Logout = CStr(Request.ServerVariables("URL")) & "?MM_Logoutnow=1"
If (CStr(Request("MM_Logoutnow")) = "1") Then
  Session.Contents.Remove("MM_Username")
  Session.Contents.Remove("MM_UserAuthorization")
  MM_logoutRedirectPage = "newlogin.asp"
  ' redirect with URL parameters (remove the "MM_Logoutnow" query param).
  if (MM_logoutRedirectPage = "") Then MM_logoutRedirectPage = CStr(Request.ServerVariables("URL"))
  If (InStr(1, UC_redirectPage, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
    MM_newQS = "?"
    For Each Item In Request.QueryString
      If (Item <> "MM_Logoutnow") Then
        If (Len(MM_newQS) > 1) Then MM_newQS = MM_newQS & "&"
        MM_newQS = MM_newQS & Item & "=" & Server.URLencode(Request.QueryString(Item))
      End If
    Next
    if (Len(MM_newQS) > 1) Then MM_logoutRedirectPage = MM_logoutRedirectPage & MM_newQS
  End If
  Response.Redirect(MM_logoutRedirectPage)
End If
%>
<!--#include file="Connections/qcnw1.asp" -->
<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers="1"
MM_authFailedURL="newacessdenied.asp"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
  If (false 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 = ""
%>
<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) = "form1") Then

  MM_editConnection = MM_qcnw1_STRING
  MM_editTable = "users"
  MM_editRedirectUrl = ""
  MM_fieldsStr  = "username|value|password|value|access_level|value"
  MM_columnsStr = "username|',none,''|password|',none,''|access_level|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
%>
<%
' *** Delete Record: declare variables

if (CStr(Request("MM_delete")) = "form2" And CStr(Request("MM_recordId")) <> "") Then

  MM_editConnection = MM_qcnw1_STRING
  MM_editTable = "users"
  MM_editColumn = "User_ID"
  MM_recordId = "" + Replace(Request.Form("MM_recordId"),",","") + ""
  MM_editRedirectUrl = "newmaintainance.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
%>
<%
' *** Insert Record: construct a sql insert statement and execute it

Dim MM_tableValues
Dim MM_dbValues

If (CStr(Request("MM_insert")) <> "") Then

  ' create the sql insert statement
  MM_tableValues = ""
  MM_dbValues = ""
  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_tableValues = MM_tableValues & ","
      MM_dbValues = MM_dbValues & ","
    End If
    MM_tableValues = MM_tableValues & MM_columns(MM_i)
    MM_dbValues = MM_dbValues & MM_formVal
  Next
  MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

  If (Not MM_abortEdit) Then
    ' execute the insert
    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
%>
<%
' *** Delete Record: construct a sql delete statement and execute it

If (CStr(Request("MM_delete")) <> "" And CStr(Request("MM_recordId")) <> "") Then

  ' create the sql delete statement
  MM_editQuery = "delete from " & MM_editTable & " where " & MM_editColumn & " = " & MM_recordId

  If (Not MM_abortEdit) Then
    ' execute the delete
    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 Recordset2
Dim Recordset2_numRows

Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_qcnw1_STRING
Recordset2.Source = "SELECT * FROM users"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

Recordset2_numRows = 0
%>
<!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">
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>QC North West - Aide Memoire v2.1 - Maintainance</title>
<script type="text/javascript">
<!--

<!--
var time = 3000;
var numofitems = 7
;
BenS is offline   Reply With Quote
Old 01-22-2007, 05:43 PM   #6
BenS
 
Join Date: Jan 2007
Posts: 9
Default

Code:
//menu constructor
function menu(allitems,thisitem,startstate){ 
  callname= "gl"+thisitem;
  divname="subglobal"+thisitem;  
  this.numberofmenuitems = allitems;
  this.caller = document.getElementById(callname);
  this.thediv = document.getElementById(divname);
  this.thediv.style.visibility = startstate;
}

//menu methods
function ehandler(event,theobj){
  for (var i=1; i<= theobj.numberofmenuitems; i++){
    var shutdiv =eval( "menuitem"+i+".thediv");
    shutdiv.style.visibility="hidden";
  }
  theobj.thediv.style.visibility="visible";
}
                
function closesubnav(event){
  if ((event.clientY <48)||(event.clientY > 107)){
    for (var i=1; i<= numofitems; i++){
      var shutdiv =eval('menuitem'+i+'.thediv');
      shutdiv.style.visibility='hidden';
    }
  }
}
// -->
//-->
</script>
<style type="text/css">
<!--
@import url("emx_nav_left.css");
.style4 {font-family: Arial, Helvetica, sans-serif}
-->
</style>
</head>
<body onmousemove="closesubnav(event);">
<div class="skipLinks">skip to: <a href="#content">page content</a> | <a href="#pageNav">links on this page</a> | <a href="#globalNav">site navigation</a> | <a href="#siteInfo">footer (site information)</a> </div>
<!-- end masthead -->
<div id="pagecell1">
  <!--pagecell1-->
<img alt="" src="tl_curve_white.gif" height="6" width="6" id="tl" /> <img alt="" src="tr_curve_white.gif" height="6" width="6" id="tr" />
  <div id="breadCrumb">
    <div align="left">Welcome <%Response.Write(Session("MM_Username"))%> </div>
  </div>
  <div id="pageName">
    <h2>QC North West - Aide Memoire v2.1 </h2>
  </div>
  <div id="pageNav">
<div id="sectionLinks"><a href="newfrontpage.asp">Front Page </a><a href="newform.asp">Form</a><a href="newresults.asp">Results</a><a href="newmaintainance.asp">Maintainance</a></div>
    <div class="relatedLinks">
      <h3><a href="<%= MM_Logout %>">Log Out </a></h3>
    </div>
  </div>
  <div id="content">
    <div class="story">
      <h3>Maintainance</h3>
    </div>
    <div class="story">
      <table width="100%" cellpadding="0" cellspacing="0" summary="">
        <tr valign="top">
<td width="51%" height="216" class="storyLeft"><p> <a href="#" class="capsule">Add Users </a></p>
            <form id="form1" name="form1" method="POST" action="<%=MM_editAction%>">
              <table width="249" border="0">
                <tr>
                  <td width="59"><span class="style4">Username</span></td>
                  <td width="180"><span class="style4">
                    <label>
                    <input name="username" type="text" id="username" size="30" />
                    </label>
                  </span></td>
                </tr>
                <tr>
                  <td><span class="style4">Password</span></td>
                  <td><span class="style4">
                    <label>
                    <input name="password" type="text" id="password" size="30" />
                    </label>
                  </span></td>
                </tr>
                <tr>
                  <td>Access Level </td>
                  <td><select name="access_level" id="access_level">
                    <option value="0" selected="selected">User</option>
                    <option value="1">Super User</option>
                                    </select></td>
                </tr>

                <tr>
                  <td><span class="style4"></span></td>
                  <td><span class="style4">
                    <label>
                    <input type="submit" name="Submit" value="Add" />
                    </label>
                  </span></td>
                </tr>
              </table>
            
              <input type="hidden" name="MM_insert" value="form1">
</form>          </td>
          <td width="49%"><p> <a href="#" class="capsule">Remove Users </a></p>
            <form ACTION="<%=MM_editAction%>" METHOD="POST" id="form2" name="form2">
              <table width="200" border="0">
                <tr>
                  <td bgcolor="#FFFFFF">Username</td>
                  <td></label>
                    <select name="select">
                      <option value="">Select User</option>
                      <%
While (NOT Recordset2.EOF)
%><option value="<%=(Recordset2.Fields.Item("User_ID").Value)%>"><%=(Recordset2.Fields.Item("username").Value)%></option>
                      <%
  Recordset2.MoveNext()
Wend
If (Recordset2.CursorType > 0) Then
  Recordset2.MoveFirst
Else
  Recordset2.Requery
End If
%>
                    </select>
</td>
                </tr>
                <tr>
                  <td bgcolor="#FFFFFF">&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr>
                  <td bgcolor="#FFFFFF">&nbsp;</td>
                  <td><input type="submit" name="Submit2" value="Delete" /></td>
                </tr>
              </table>
              <p align="center">&nbsp;</p>
            
                            
              <input type="hidden" name="MM_delete" value="form2">
          <input type="hidden" name="MM_recordId">
          <input type="hidden" name="MM_recordId">
          <input type="hidden" name="MM_recordId" value="<%= Recordset2.Fields.Item("User_ID").Value %>">
            </form>          </td>
        </tr>
      </table>
    </div>
  </div>
  <div id="siteInfo"><a href="#">Contact Us</a> | &copy;2007 B.Staley </div>
</div>
<!--end pagecell1-->
<br />
<script type="text/javascript">
    <!--
      var menuitem1 = new menu(7,1,"hidden");
            var menuitem2 = new menu(7,2,"hidden");
            var menuitem3 = new menu(7,3,"hidden");
            var menuitem4 = new menu(7,4,"hidden");
            var menuitem5 = new menu(7,5,"hidden");
            var menuitem6 = new menu(7,6,"hidden");
            var menuitem7 = new menu(7,7,"hidden");
    // -->
    </script>
</body>
</html>
<%
Recordset2.Close()
Set Recordset2 = Nothing
%>
As has probably been worked out - I'm pretty new at this so please bear with me. Thanks for your help and patience
BenS is offline   Reply With Quote
Old 01-22-2007, 06:57 PM   #7
davidj
davidj's Avatar
 
Join Date: Sep 2005
Location: The Toon (newcastle upon Tyne)
Posts: 8,256
Default

you need to to find whats in MM_editQuery and whether it contains data

if you find MM_editQuery where the delete is built you need to print the values from this var


Code:
 
Response.Write(MM_editQuery)
__________________
Would you like to learn PHP from me? Check out -> www.codezenith.co.uk
davidj is offline   Reply With Quote
Old 01-22-2007, 07:43 PM   #8
BenS
 
Join Date: Jan 2007
Posts: 9
Default

Hi thanks for your response. I inserted the code:
Code:
<%Response.Write(MM_editQuery)%>
and nothing showed up when deleting users, but when adding users it showed:
Quote:
insert into users (username,password,access_level) values ('dennis','dennis',1)
Am I placing the code in the wrong place?

Last edited by BenS; 01-22-2007 at 07:46 PM..
BenS is offline   Reply With Quote
Old 01-22-2007, 08:01 PM   #9
davidj
davidj's Avatar
 
Join Date: Sep 2005
Location: The Toon (newcastle upon Tyne)
Posts: 8,256
Default

paste it at the end of your ASP script but before the HTML

look for this...

Code:
 
Recordset2_numRows = 0
then place

Code:
 
Recordset2_numRows = 0
Response.Write(MM_editQuery) //<<<
__________________
Would you like to learn PHP from me? Check out -> www.codezenith.co.uk
davidj is offline   Reply With Quote
Old 01-22-2007, 08:14 PM   #10
BenS
 
Join Date: Jan 2007
Posts: 9
Default

I'm feeling really stupid now...
I have added:
Code:
Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_qcnw1_STRING
Recordset2.Source = "SELECT * FROM users"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

Recordset2_numRows = 0
Response.Write(MM_editQuery) //<<<
%>
And it is giving the same response - something when I add a user, but nothing when I delete one. Am I looking at the correct variable?
BenS is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 12:24 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Copyright 2006 DreamweaverClub.com