Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 13th, 2004, 04:03 PM
Authorized User
 
Join Date: May 2004
Location: Houston, TX, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to jturlington
Default Create an ASP page that will allow deletion

I am trying to create a graphically pleasing page for deleting record from a specified table and want it to basically have a certain field of the table record in one column that makes it easy to recognize and a link or button that will activate like the deletion of that record. If someone could help me get started it would be appreciated.

Would/Could/Should appear something like this.
************************************************** ********************
Record Deletion Page
---------------------------------------------------------------------
Record1 [Delete]
Record2 [Delete]


If you get the idea and maybe can help it would be greatly appreciated. I am just now learning ASP and VB so this could be a simple thing.

Thank you,
Jon Turlington
__________________
Thank you,
Jon Turlington
  #2 (permalink)  
Old May 14th, 2004, 02:10 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

There are many ways to do this, like spawning a new page that deletes the record (like you (un)subscribe to a topic here on P2P), a page that posts back to itself, or a separate delete page that sends the user back to the list page. Personally, I prefer that last option. It looks like this (partly in pseudo code)
[List.asp]
' Get Recordset
While Recordset has Records
  Response.Write("MyRecordset.Fields("Description"). Value & " ")
  Response.Write("<a href=""Delete.asp?ID=" & MyRecordset.Fields("ID").Value & _
        """>Delete</a><br />")
  MyRecordset.MoveNext()
Loop

This dumps a list with the record's Description column and a word Delete that links to the Delete.asp page and passes it the unique ID of the record.

On Delete.asp you could do this:

[Delete.asp]
' Get the ID passed to this page
Dim RecordID
RecordID = Request.QueryString("ID")
' Execute SQL to delete
DELETE FROM MyTable WHERE ID = RecordID
Response.Redirect("List.asp")

This pages deletes the requested record, and then takes the user back to the previous page.

In a real-life application, you probably wouldn't use spaces and line breaks to layout the page, but use a table or CSS instead. I didn't add any formatting, so you can focus on the concepts rather than on the surrounding HTML.

There are many alternatives or additional features you could add (delete multiple records at once, ask for confirmation before you delete, don't delete the actual record, but just mark it as deleted), but I think this should at least get you in the right direction. If you have further or specific questions, let us know.

<plug type="Shameless">
If you want to do this with Dreamweaver, take a look at my book: http://www.wrox.com/books/0764555243.shtml It deals with this kind of requirements in great detail.
</plug>

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Crush by Smashing Pumpkins (Track 5 from the album: Gish) What's This?
  #3 (permalink)  
Old May 14th, 2004, 02:47 AM
Authorized User
 
Join Date: Feb 2004
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi....
When you click the DELETE (using Hyperlink) it will take to the other page, where in this page all the datas are show. then the code for delete will be process in the next page.is that what you want.

The one I gave you is using Hyperlink for the delete and it will show the all data First then
in the next page the delete code will be execute.

In ShowData.asp

1. You create the table and what fields do you want to show:
Example:
<Table >
  <TR>
  <TH>EmployeeID</TH>
  <TH>Surname</TH>
  <TH>DELETE</TH>
  </TR>
  <% Do While Not rs.EOF %>
  <TD><%=rsData("EmployeeID")%></TD>
  <TD><%=rsData("Surname")%></TD>
  <TD><A HREF="Delete.asp?EmpID=<%=rsData("EmployeeID")%>"> DELETE</A></TD>

In DELETEShow.asp
in here you will show all the data from your table, the one you want to delete.

Set rsData = Conn.Execute (Select * from tblname where EmployeeID='" &varEmployeeID&"'")

redirect to delete.asp page to delete the data.

In DELETE.asp
strsql= "Delete * From tblname Where EmployeeID='" &varEmployeeID &"'"
Conn.Execute strsql, Conn
Conn.Close
Set Conn = nothing

I hope this will help you to get start.
  #4 (permalink)  
Old May 14th, 2004, 07:07 AM
Authorized User
 
Join Date: May 2004
Location: Houston, TX, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to jturlington
Default

I really appreciate the different options. Should put me on the right path. Means alot to me.

Thank you,
Jon Turlington
  #5 (permalink)  
Old May 14th, 2004, 12:06 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Great. Keep us updated about the solution you have chosen, if you want.....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Kill You by KoRn (Track 14 from the album: Life Is Peachy) What's This?
  #6 (permalink)  
Old May 14th, 2004, 12:20 PM
Authorized User
 
Join Date: May 2004
Location: Houston, TX, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to jturlington
Default

Well, I did it like this.:

lots of my html junk around it.

[display_articles.asp]
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% DIM PagNam
PagNam = "Delete An Article" %>
<% Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "dsn","sa","password"
' Connection String
set rsArticle=Server.CreateObject("ADODB.recordset")
rsArticle.ActiveConnection = oConn
rsArticle.Source = "Select * from article ORDER BY ArticleId DESC"
rsArticle.CursorType = 0
rsArticle.CursorLocation = 2
rsArticle.LockType = 1
rsArticle.Open()
' Recordset String
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<link href="/includes/mainstyle.css" rel="stylesheet" type="text/css">
</head>

<body><table class="main" width="700" align="center" cellpadding="0" cellspacing="0">
  <tr><td><table width="100%" cellpadding="0" cellspacing="0"><tr><td colspan="2">
 </td></tr>
  <tr><td bgcolor="#000000" class="nav" width="90"></td>
  <td align="center" width="610"><table width="80%" border="1"><%
Do Until rsArticle.EOF
  Response.Write("<tr><td>" & rsArticle.Fields("ArticleTitle").Value & "</td><td>")
  Response.Write("<a href=""delete_articles.asp?ArticleID=" & rsArticle.Fields("ArticleID").Value & _
        """>Delete</a></td></tr>")
  rsArticle.MoveNext()
Loop
%></table>
</td></tr></table>
</td></tr></table>
</body>
</html>

[delete_articles.asp]

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "dsn","sa","password"
' Connection String
set rs=Server.CreateObject("ADODB.recordset")
rs.ActiveConnection = oConn
rs.Source = "Select * from article ORDER BY ArticleId DESC"
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()
' Recordset String
Dim RecordID
RecordID = Request.QueryString("ArticleID")
strSQL = "DELETE FROM article WHERE ArticleID =" & RecordID
oConn.execute(strSQL)
oConn.close
Set oConn = nothing
Response.Redirect("display_articles.asp")
%>

Thank you,
Jon Turlington
  #7 (permalink)  
Old May 14th, 2004, 12:30 PM
Authorized User
 
Join Date: May 2004
Location: Houston, TX, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to jturlington
Default

Got another question this code I did to update username and passwords for users is not updating the records but also not erroring out.

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "dsn","sa","password"
' Connection String
set rsUser=Server.CreateObject("ADODB.recordset")
rsUser.ActiveConnection = oConn
rsUser.Source = "Select * from auth ORDER BY id ASC"
rsUser.CursorType = 0
rsUser.CursorLocation = 2
rsUser.LockType = 1
rsUser.Open()
' Recordset String
set rsUsername=Server.CreateObject("ADODB.recordset")
rsUsername.ActiveConnection = oConn
rsUsername.Source = "Select username from auth WHERE id=" & Request.QueryString("id")
rsUsername.CursorType = 0
rsUsername.CursorLocation = 2
rsUsername.LockType = 1
rsUsername.Open()
xuser = rsUsername.GetString
' Recordset String
If request.form("password") = "" & request.form("username") = "" then response.Redirect("../administration/error.asp?errorid=1")
If request.form("password") = "" & request.form("username") <> "" then
strSQLusername = "UPDATE auth SET username='" & request.form("username") & "' WHERE id ='" & request.form("id") & "'"
oConn.execute(strSQLusername)
End If
If request.form("password") <> "" & request.form("username") = "" then
strSQLpassword = "UPDATE auth SET password='" & request.form("password") & "' WHERE id ='" & request.form("id") & "'"
oConn.execute(strSQLpassword)
End If
If request.form("password") <> "" & request.form("username") <> "" then
strSQLboth = "UPDATE auth SET username='" & request.form("username") & "', password='" & request.form("password") & "' WHERE id ='" & request.form("id") & "'"
oConn.execute(strSQLboth)
End If
oConn.close
Set oConn = nothing
Response.Redirect("display_users.asp?status=comple te&xuser=" & xuser & "")
%>

Thank you,
Jon Turlington
  #8 (permalink)  
Old May 14th, 2004, 01:03 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Looks good, except for the Delete page. There is no need to open the Recordset first:
Code:
<% 
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "dsn","sa","password"
Dim RecordID
RecordID = Request.QueryString("ArticleID")
strSQL = "DELETE FROM article WHERE ArticleID = " & RecordID
oConn.Execute(strSQL)
oConn.Close
Set oConn = nothing
Response.Redirect("display_articles.asp")
%>
should do the trick as well.

I am off now, will look at your other question some other time....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Kill You by KoRn (Track 14 from the album: Life Is Peachy) What's This?
  #9 (permalink)  
Old May 15th, 2004, 03:57 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi again,

Why are you opening all those recordsets?? Is the purpose of this page to allow a member to change their username or password?
I think you need to remove all the recordset bloat, and the write out the SQL statement so you can see what gets executed. Try this:
Code:
<% 
Dim SQLStatement
' Both fields empty?
If Request.Form("password") = "" & Request.Form("username") = "" Then 
       Response.Redirect("../administration/error.asp?errorid=1")

' Update just the username
If Request.Form("password") = "" & Request.Form("username") <> "" Then
  SQLStatement = "UPDATE auth SET username='" &  Request.Form
      ("username") & "' WHERE id ='" & Request.Form("id") & "'"
End If

' Update just the password
If Request.Form("password") <> "" & Request.Form("username") = "" Then 
  SQLStatement = "UPDATE auth SET password='" & Request.Form
      ("password") & "' WHERE id ='" & Request.Form("id") & "'"
End If

' Update both
If Request.Form("password") <> "" & Request.Form("username") <> "" then 
  SQLStatement = "UPDATE auth SET username='" & Request.Form
     ("username") & "', password='" & Request.Form("password") & "' 
          WHERE id ='" & request.form("id") & "'"
End If

' Comment out the next two lines when SQLStatement looks OK
Response.Write("SQL is " & SQLStatement)
Response.End

' If there is something to execute
If Len(SQLStatement) > 0 Then
  Set oConn = Server.CreateObject("ADODB.Connection")
  oConn.Open "dsn","sa","password"
  oConn.Execute(SQLStatement)
  oConn.Close
  Set oConn = nothing 
End If

Response.Redirect("display_users.asp?status=complete&xuser=" & xuser & "") 
%>
I made a few changes to your code. First of all, I removed all the recordset stuff. You don't need it .
Secondly, I used a single SQL statement for all three update option. This way, you don't have to repeat oConn.Execute(SQLStatement) for each statement. Makes it easier to read and maintain the code. I also added a few comments to describe the purpose of the code. Again, makes it easier to read, maintain and communicate your code to people at a forum ;)

(I added a few line breaks in the UPDATE statement so the code would wrap in this forum, but you should remove them again in your code.)

You can make your code a bit easier to read, by assigning the form fields to variables first, e.g.:

Dim Password
Dim UserName
Password = Request.Form("password")
UserName = Request.Form("username")

Then in your code you can use Password instead of Request.Form("password").

Finally, I added two lines with Response.Write and Response.End. The first statement writes out the SQL statement to the screen. If the SQL looks odd, post it here so I' can take a look at it.

Be aware that this code has a huge security hole. You're not really doing any checking, but assume the ID, username and password are OK. So, I could construct my own custom form, pass 1 for the ID field (the first user in the system is quite often the admin) and pass my own username and password..... Not a comforting thought....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
  #10 (permalink)  
Old May 15th, 2004, 12:18 PM
Authorized User
 
Join Date: May 2004
Location: Houston, TX, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to jturlington
Default

Again Imar you are my savior and I thank you, I am in the process of learning ASP, a lil VB, and SQL all at once and many times I float in the deep-end of the pool of ignorance.

For right now the changing of the username and password page is for my use but I definately see your point. I also add a line of code that reads like:
Code:
<%
If Session("loggedin") <> True Then Response.Redirect "/login.asp"
%>
to many of my pages as my security so not just anyone can access certian areas. My recordset thing was a better safe than sorry type thing. I have a phobia about variables kinda, but I definately understand life can be easier if you just plan a little.

I appreciate the help.

Thank you,
Jon Turlington




Similar Threads
Thread Thread Starter Forum Replies Last Post
how to create login page using asp.net prakashbabu ASP.NET 2.0 Basics 1 March 7th, 2007 11:52 AM
Deletion.. ankur_icfai SQL Server 2000 8 August 30th, 2006 07:31 AM
Create MSpowerpoint slides form and ASP page gmsmith Classic ASP Professional 0 April 7th, 2006 02:58 PM
how to create ASP page in visual studio.net madhusrp Other Programming Languages 3 March 2nd, 2006 03:07 AM
How to create HTML page from a dynamic ASP page musharaf Classic ASP Professional 2 March 18th, 2004 09:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.