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

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

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

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

- A.Kahtava