 |
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
|
|
|

May 13th, 2004, 04:03 PM
|
Authorized User
|
|
Join Date: May 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 14th, 2004, 02:10 AM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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?
|

May 14th, 2004, 02:47 AM
|
Authorized User
|
|
Join Date: Feb 2004
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

May 14th, 2004, 07:07 AM
|
Authorized User
|
|
Join Date: May 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I really appreciate the different options. Should put me on the right path. Means alot to me.
Thank you,
Jon Turlington
|

May 14th, 2004, 12:06 PM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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?
|

May 14th, 2004, 12:20 PM
|
Authorized User
|
|
Join Date: May 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 14th, 2004, 12:30 PM
|
Authorized User
|
|
Join Date: May 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 14th, 2004, 01:03 PM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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?
|

May 15th, 2004, 03:57 AM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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.
|

May 15th, 2004, 12:18 PM
|
Authorized User
|
|
Join Date: May 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
 |