Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 5th, 2006, 08:44 AM
Registered User
 
Join Date: Mar 2006
Location: Glasgow, South Lanarkshire, United Kingdom.
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
Reply With Quote
  #2 (permalink)  
Old March 5th, 2006, 09:47 AM
Wrox Technical Editor
 
Join Date: Dec 2005
Location: , , Canada.
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
Reply With Quote
  #3 (permalink)  
Old March 5th, 2006, 01:00 PM
Registered User
 
Join Date: Mar 2006
Location: Glasgow, South Lanarkshire, United Kingdom.
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
Reply With Quote
  #4 (permalink)  
Old March 5th, 2006, 09:56 PM
Wrox Technical Editor
 
Join Date: Dec 2005
Location: , , Canada.
Posts: 271
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

- A.Kahtava
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:51 PM.


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