Re: delete records from database
- From: "Mike Brind" <paxtonend@xxxxxxxxxxx>
- Date: 16 May 2006 00:39:55 -0700
Andrew Oke wrote:
trying to delete records from a database. form code looks like this
<%
'Check if user is logged in
if Session("name") = "" then
'If not, go to login page
Response.Redirect("http://www.off-grid-living.com/login.asp";)
Else
' Declaring variables
Dim rs, data_source, no
no = 0
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("\subscribedb\subscribelist1.mdb")
' Creating Recordset Object and opening the database
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "userlist", data_source
' Looping through the records to show all of them
While Not rs.EOF
Response.Write "<form action='del.asp' method='post'>"
Response.Write(rs("firstname") &" "& rs("lastname") & "<br>")
Response.Write(rs("street") & "<br>")
Response.Write(rs("city") & "<br>")
Response.Write(rs("province") & "<br>")
Response.Write(rs("postalcode") & "<br>")
Response.Write(rs("country") & "<br>")
Response.Write(rs("emailaddress") & "<br>")
Response.Write(rs("paid") & "<br>")
Response.Write("<input type='hidden' name='stremail' value='" &
rs("emailaddress") & "'>")
Response.Write("<input type='submit' value='Delete'>" & "<br>")
response.write("<br>")
response.write("<br>")
Response.Write("</form>")
no = no + 1
rs.MoveNext
Wend
' Done. Now close the Recordset
rs.Close
Set rs = Nothing
Response.Write "<p>Total Records Found : " & no
end if
%>
that works. code for the deleting is:
<% 'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsDeleteEntry 'Holds the recordset for the record to be deleted
Dim strSQL 'Holds the SQL query to query the database
Dim stremail 'Holds the record number to be deleted
stremail = request.form("stremail")
'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= " &
Server.MapPath ("\subscribedb\subscribelist1.mdb")
'Create an ADO recordset object
Set rsDeleteEntry = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT [emailaddress].* FROM userlist WHERE emailaddress=" &
stremail
You haven't delimited the value correctly. It should be as follows:
strSQL = "SELECT [emailaddress].* FROM userlist WHERE emailaddress='" &
stremail & "'"
However, as Bob says, you shouldn't create a recordset to delete a
record. And if this is a public site, you are liable to SQL Injection
attacks. If someone entered ' or ''=' into your form, it would select
all email addresses.
The best way to solve this is to use a saved parameter query.
Go into Access and click on the Query tab. Select New Query In Design
View, then close the Show Tables dialogue box. In the top left hand
corner, click on SQL to swith to SQL view, then type (or paste) this
in:
DELETE * FROM userlist WHERE emailaddress = [p1]
Save that as qDeleteEmail, then run it to see if it works. You will
be prompted for a value for [p1]. Enter a legitimate value, and verify
it's working.
Now in your del.asp code,
<% 'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim p1 'parameter holder
p1= request.form("stremail")
'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using the same OLEDB
connection string you used earlier - Don't use the ODBC driver
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("\subscribedb\subscribelist1.mdb")
'pass the name of the saved parameter query as a method on the
connection object followed by the parameter holder
adoCon.qDeleteEmail p1
Set adoCon = Nothing
'Return to the delete select page in case another record needs deleting
Response.Redirect "totaldatabase.asp"
%>
It's a lot quicker, easier and more secure to do it this way. You
don't have to worry about delimiting values either.
By the way, nothing to do with your problem, but While... Wend is not
recommended by Microsoft. They suggest you should use Do... Loop
instead.
Do While Not rs.EOF
....
process records
....
rs.Movenext
Loop
--
Mike Brind
.
- Follow-Ups:
- Re: delete records from database
- From: Andrew Oke
- Re: delete records from database
- From: Evertjan.
- Re: delete records from database
- References:
- delete records from database
- From: Andrew Oke
- delete records from database
- Prev by Date: Re: delete records from database
- Next by Date: Re: delete records from database
- Previous by thread: Re: delete records from database
- Next by thread: Re: delete records from database
- Index(es):
Relevant Pages
|
|
