PDA

View Full Version : Problem Deleting Correct Record From Table


BenS
01-20-2007, 09:37 PM
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

domedia
01-21-2007, 03:19 PM
Bens, it's impossible for anyone to troubleshoot your code if you don't provide it.

BenS
01-21-2007, 09:45 PM
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:
<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

davidj
01-21-2007, 10:45 PM
this does not help

this is the form. We need to see the script that process the form data and deletes the record

BenS
01-22-2007, 05:42 PM
Sorry about my ineptitude....
This is the code for the whole page, please note that there is also a table that adds users:

<%@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
01-22-2007, 05:43 PM
//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

davidj
01-22-2007, 06:57 PM
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



Response.Write(MM_editQuery)

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

davidj
01-22-2007, 08:01 PM
paste it at the end of your ASP script but before the HTML

look for this...


Recordset2_numRows = 0


then place


Recordset2_numRows = 0
Response.Write(MM_editQuery) //<<<

BenS
01-22-2007, 08:14 PM
I'm feeling really stupid now...
I have added:
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?

davidj
01-22-2007, 08:18 PM
dont worry about it

you probably know more about ASP than i do

ok find this...then add the bottom bit


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

Response.Write(MM_editQuery)

BenS
01-22-2007, 08:44 PM
It doesn't seem to be working. To simplify matter I just started again - from scratch and it still didn't work. So added the code you suggested, and it gave me nothing...
This is the latest 'simple' page - hopefully it is a little clearer than before:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/qcnw1.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 = ""
%>
<%
' *** 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 = "" + Request.Form("MM_recordId") + ""
MM_editRedirectUrl = "temp.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
%>
<%
' *** 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
Response.Write(MM_editQuery)

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
Response.Write(MM_editQuery)
%>
<!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="form2" name="form2" method="POST" action="<%=MM_editAction%>">
<table width="200" border="0">
<tr>
<td bgcolor="#FFFFFF">Username</td>
<td><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>

<input type="hidden" name="MM_delete" value="form2">
<input type="hidden" name="MM_recordId" value="<%= Recordset2.Fields.Item("User_ID").Value %>">
</form>
</body>
</html>
<%
Recordset2.Close()
Set Recordset2 = Nothing
%>

davidj
01-22-2007, 09:06 PM
im sorry

but i dont have an ASP testing enviroment where i can replicate your fault

I am not an ASP programmer so any syntax that may be incorrect doesnt stand out to me

What i will say is this...

Please dont rely on dreamweaver code. Rather than taking the time to learn how to press the buttons that creates ASP. use this time to learn ASP or even try PHP (tutorials > www.dreamweaverclub.com/vtm (http://www.dreamweaverclub.com/vtm) ).

Once you understand what your doing and are able to read the code you have written you will never use a button-that-writes-code again.

as you yourself confess that your ASP is at a very basic level.. in 6 months or 12 months your skill is going to be at the same level because you have relied on dreamweaver's buttons

get a good book on ASP or switch to PHP. Take 5 minutes to look at the videos i have produced and see if i can change your mind

im sorry. Its not very often that one gets away from me

BenS
01-22-2007, 09:11 PM
Well thanks for your help. I am indeed intending to learn ASP at some point. This project is my first delve into web development - it is for a pre-registration pharmacist project in the NHS. Thanks again!

BenS
01-24-2007, 07:10 PM
Just in case anyone was wondering how to solve this problem I was told how to do it like:
This will be because the deleting code does not actually know anything about the drop down list and is taking the record to delete from the hidden variable at the bottom of the HTML. Try changing the name of the drop down list and getting rid of the hidden variable, so <select name="select"> becomes <select name="MM_recordId"> and remove <input type="hidden" name="MM_recordId" value="<%= Recordset2.Fields.Item("User_ID").Value %>">