Wrox Programmer Forums
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language 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
 
Old March 5th, 2006, 08:44 AM
Registered User
 
Join Date: Mar 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Delete Query

I am creating a real estate web site for a client, quite a simple one where the client will be able to view a specific page to delete entries from her database. I have used the following codes.

Page one allows the client to input the delete criteria which is to delete between id "x" and id "y" the second page is the delete query.

I am getting an error: -

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Between operator without And in query expression 'id between (('55') and ('57'))'.

This at least proves that the information from page 1 is being sent to page 2 and being partially processed.

Page 1 codes is: -

<p class="style1">WARNING!!! Once Database items are deleted they are gone forever </p>
<form name="form1" method="post" action="test2.asp">
  <table border="1" cellspacing="3" cellpadding="3">
    <tr>
      <td>Delete Database Entry's from ID Number </td>
      <td><input type="text" name="df" size="4"></td>
      <td>To</td>
      <td><input type="text" name="dt" size="4"></td>
    </tr>
    <tr>
      <td colspan="4"><input type="submit" name="Submit" value="Delete"></td>
    </tr>
  </table>
</form>

and to a degree is being processed by page 2: -

<%
df2=Request.Form("df")
dt2=Request.Form("dt")

%>

<%
query="delete * from hunt where id between (('" & df2 & "') and ('" & dt2 & "'))"
set database=server.createobject("adodb.connection")
database.open "hunt"
Set rslist=server.createobject("adodb.recordset")
rslist.open query,database,3


'rslist.close
Set rslist = Nothing
database.close
set database = Nothing
%>

If I rem out these three statements

df2=Request.Form("df")
dt2=Request.Form("dt")
query="delete * from hunt where id between (('" & df2 & "') and ('" & dt2 & "'))"

and replace them with: -

query="delete * from hunt where id between 55 and 57"

the delete query works fine so I am assuming that the issue is a syntax error in the query that takes the "id" limits from page 1.

If anyone could help me with this it would be much appreciated and I may have some hair left at the end of the day.

PS: I am also curious why I have to rem out the rslist.close statement in the second page. If I leave it in another error occurs saying that

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.

I assumed it wasn't closed until the query was complete.


IanC
 
Old March 5th, 2006, 09:47 AM
Wrox Technical Editor
 
Join Date: Dec 2005
Posts: 271
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What data type is id?
In the following query you are treating df2 and dt2 as strings.
query = "delete * from hunt where id between (('" & df2 & "') and ('" & dt2 & "'))"

Remove the single quotes.

Try this:
query = "DELETE FROM hunt WHERE id BETWEEN (" & df2 & " AND " & dt2 & ");"

- A.Kahtava
 
Old March 5th, 2006, 01:00 PM
Registered User
 
Join Date: Mar 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanx adam for your rapid response.

You hit the nail on the head when you said "remove the single quotes"

I changed (('" & df2 & "') and ('" & dt2 & "'))"
to this (" & df2 & ") and (" & dt2 & ")" and it worked.
My data type is an Access Database, so your fix was 99% there all I needed was to include the brackets around the statements on each side of the "and"

Much appreciated Thanx again


IanC
 
Old March 5th, 2006, 09:56 PM
Wrox Technical Editor
 
Join Date: Dec 2005
Posts: 271
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Glad I could be of some assistance... :)

- A.Kahtava





Similar Threads
Thread Thread Starter Forum Replies Last Post
php/mysql delete button and delete query dungey PHP Databases 17 April 11th, 2009 12:24 PM
delete query using trigger prashant_telkar SQL Language 1 June 19th, 2007 10:39 AM
Delete query problem aneesh_sunu@yahoo.co.in PHP How-To 2 December 18th, 2006 09:24 AM
Delete Query Corey Access 2 February 12th, 2006 07:36 PM
Delete Query not working kbonney98 Access 1 April 14th, 2005 06:29 AM





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