delete records from database

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 "

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

'Set the lock type so that the record is locked by ADO when it is deleted
rsDeleteEntry.LockType = 3

'Open the recordset with the SQL query
rsDeleteEntry.Open strSQL, adoCon ------------>>>> says there is error here

'Delete the record from the database
rsDeleteEntry.Delete

'Reset server objects
rsDeleteEntry.Close
Set rsDeleteEntry = Nothing
Set adoCon = Nothing

'Return to the delete select page in case another record needs deleting
Response.Redirect "totaldatabase.asp"

%>

says there is an error in the rsDeleteEntry.Open statement... which i
believe is coming from my strSQL statement. help would be appreciated.

--Andrew

Re: delete records from database

What's the error, and why are you using a record set to delete records?

Bob Lehmann

"Andrew Oke" wrote in message
news:eeDXk6HeGHA.764@TK2MSFTNGP05.phx.gbl...
> 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 "

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
>
> 'Set the lock type so that the record is locked by ADO when it is deleted
> rsDeleteEntry.LockType = 3
>
> 'Open the recordset with the SQL query
> rsDeleteEntry.Open strSQL, adoCon ------------>>>> says there is error
here
>
> 'Delete the record from the database
> rsDeleteEntry.Delete
>
> 'Reset server objects
> rsDeleteEntry.Close
> Set rsDeleteEntry = Nothing
> Set adoCon = Nothing
>
> 'Return to the delete select page in case another record needs deleting
> Response.Redirect "totaldatabase.asp"
>
> %>
>
> says there is an error in the rsDeleteEntry.Open statement... which i
> believe is coming from my strSQL statement. help would be appreciated.
>
> --Andrew

Re: delete records from database

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 "

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

Re: delete records from database

"Andrew Oke" wrote in message
news:eeDXk6HeGHA.764@TK2MSFTNGP05.phx.gbl...
> trying to delete records from a database. form code looks like this
>
> 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

This is your problem try:-

strSQL = "SELECT [userlist].* FROM userlist WHERE emailaddress='" & _
Replace(stremail,"'","''") & "'"

However is there a reason why you are using a recordset. Why not:-

strSQL = "DELETE userlist WHERE emailaddress = '" & _
Replace(stremail,"'","''") & "'"

adoCon.Execute strSQL




>
> 'Set the lock type so that the record is locked by ADO when it is deleted
> rsDeleteEntry.LockType = 3
>
> 'Open the recordset with the SQL query
> rsDeleteEntry.Open strSQL, adoCon ------------>>>> says there is error
here
>
> 'Delete the record from the database
> rsDeleteEntry.Delete
>
> 'Reset server objects
> rsDeleteEntry.Close
> Set rsDeleteEntry = Nothing
> Set adoCon = Nothing
>
> 'Return to the delete select page in case another record needs deleting
> Response.Redirect "totaldatabase.asp"
>
> %>
>
> says there is an error in the rsDeleteEntry.Open statement... which i
> believe is coming from my strSQL statement. help would be appreciated.
>
> --Andrew

Re: delete records from database

Mike Brind wrote on 16 mei 2006 in microsoft.public.inetserver.asp.db:

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

Who worries about SQL Injection, when you open to the web a code to delete
a record by entering just an email address?

And, would an email address be [made] unique?
Not in, say, a club member file,
where family members could share an email address.

The code then deletes all records with the specified email address,
not only the one displayed.

Specific deletion should only be done on a unique field.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Re: delete records from database

Evertjan. wrote:
> Mike Brind wrote on 16 mei 2006 in microsoft.public.inetserver.asp.db:
>
> > 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.
>
> Who worries about SQL Injection, when you open to the web a code to delete
> a record by entering just an email address?
>
> And, would an email address be [made] unique?
> Not in, say, a club member file,
> where family members could share an email address.

I think I would prefer to have to fix that, rather than try to fix the
problem of losing all club member records :-)

>
> The code then deletes all records with the specified email address,
> not only the one displayed.
>
> Specific deletion should only be done on a unique field.

For sure.

>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress)

Re: delete records from database

Mike Brind wrote on 16 mei 2006 in microsoft.public.inetserver.asp.db:

>
> Evertjan. wrote:
[..]
>> Who worries about SQL Injection, when you open to the web a code to
>> delete a record by entering just an email address?
>>
>> And, would an email address be [made] unique?
>> Not in, say, a club member file,
>> where family members could share an email address.
>
> I think I would prefer to have to fix that, rather than try to fix the
> problem of losing all club member records :-)

Then you just restore your last backup,
while individual deletions won't be so easily noticed.

;-)

But in general I agree.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Re: delete records from database

Evertjan. wrote:
> Mike Brind wrote on 16 mei 2006 in microsoft.public.inetserver.asp.db:
>
> >
> > Evertjan. wrote:
> [..]
> >> Who worries about SQL Injection, when you open to the web a code to
> >> delete a record by entering just an email address?
> >>
> >> And, would an email address be [made] unique?
> >> Not in, say, a club member file,
> >> where family members could share an email address.
> >
> > I think I would prefer to have to fix that, rather than try to fix the
> > problem of losing all club member records :-)
>
> Then you just restore your last backup,
> while individual deletions won't be so easily noticed.

Huh? Backup? What's that then??!?
;-)


>
> But in general I agree.
>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress)

Re: delete records from database

Thanks for everyones help. I really appreciate it.

Mike Brind wrote:
> 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 "

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
>