Hi, my question is about Vbscript in ASP.



If I have 150 records in a MS access table, in which there is no field of 

record number. I want to write a statement to DELETE all previous records 

(e.g here number 1-50 records)and to keep the last 100 records (number 

51-100). If the table contains 430 records, I want to keep only the last 100 

records, which are the record of number 331 to 430; and which are involved 

within number 1 to 330 are DELETED



I try the following statement, but it DELETE all records



Dim rsUser

Set rsUser = Server.CreateObject("ADODB.Recordset")

rsUser.Open "theTablename", objConnection, _

              .....<constant>..., adcmdTable

rsUser.Filter

rsUser.Delete

rsUser.Update



OR



Dim rsUser, strSQL

Set rsUser = Server.CreateObject("ADODB.Recordset")

strSQL = "DELECT FROM theTablename"

rsUser.Open strSQL, objConnection, _

              .....<constant>..., adcmdText





Thanks for any reply



David

its deleting all the records as your sql statements are not stating any

particular set of records to delete (achieved usually with a where clause)-

just to delete all records in the table.



Without seeing the layout of your table its hard to define a filter or WHERE

statement to define the set of the last 100 records.  

You must have a primary key or some type of order in the table that would

help to define the filter/ WHERE statement.  

Send me the layout of the table and I should be able to help



Louise



(And let everyone else know the solution on list if you would... - moderator)

-----Original Message-----

From: David BG [mailto:johnson_bg@h...]

Sent: 23 November 2000 00:09

To: ASP Databases

Subject: [asp_databases] VBScript in ASP question





Hi, my question is about Vbscript in ASP.



If I have 150 records in a MS access table, in which there is no field of 

record number. I want to write a statement to DELETE all previous records 

(e.g here number 1-50 records)and to keep the last 100 records (number 

51-100). If the table contains 430 records, I want to keep only the last 100



records, which are the record of number 331 to 430; and which are involved 

within number 1 to 330 are DELETED



I try the following statement, but it DELETE all records



Dim rsUser

Set rsUser = Server.CreateObject("ADODB.Recordset")

rsUser.Open "theTablename", objConnection, _

              .....<constant>..., adcmdTable

rsUser.Filter

rsUser.Delete

rsUser.Update



OR



Dim rsUser, strSQL

Set rsUser = Server.CreateObject("ADODB.Recordset")

strSQL = "DELECT FROM theTablename"

rsUser.Open strSQL, objConnection, _

              .....<constant>..., adcmdText





Thanks for any reply



David

Thanks Louise,



Actually, I am not allowed to have any EXTRA field in this table, beside 

USERNAME and PASSWORD.



For testing, I intend to include a LONG DATE as an extra field in this 

table, then it would be



Filed 1 - USERNAME

Filed 2 - PASSWORD

Filed 3 - DATE (LONG TYPE)



Sometimes, there are so many records input into this table from USERS

at several seconds of time; but also, sometimes there are no record input at 

all for 2 hours.



and I still want to keep only the last 100 records when there over 100 

records and DELETE action takes place. What would the code be?



David











----Original Message Follows----

From: Louise Greally <lgreally@c...>

Reply-To: "ASP Databases" <asp_databases@p...>

To: "ASP Databases" <asp_databases@p...>

Subject: [asp_databases] RE: VBScript in ASP question

Date: Wed, 22 Nov 2000 16:44:29 -0000



its deleting all the records as your sql statements are not stating any

particular set of records to delete (achieved usually with a where clause)-

just to delete all records in the table.



Without seeing the layout of your table its hard to define a filter or WHERE

statement to define the set of the last 100 records.

You must have a primary key or some type of order in the table that would

help to define the filter/ WHERE statement.

Send me the layout of the table and I should be able to help



Louise



(And let everyone else know the solution on list if you would... - 

moderator)

-----Original Message-----

From: David BG [mailto:johnson_bg@h...]

Sent: 23 November 2000 00:09

To: ASP Databases

Subject: [asp_databases] VBScript in ASP question





Hi, my question is about Vbscript in ASP.



If I have 150 records in a MS access table, in which there is no field of

record number. I want to write a statement to DELETE all previous records

(e.g here number 1-50 records)and to keep the last 100 records (number

51-100). If the table contains 430 records, I want to keep only the last 100



records, which are the record of number 331 to 430; and which are involved

within number 1 to 330 are DELETED



I try the following statement, but it DELETE all records



Dim rsUser

Set rsUser = Server.CreateObject("ADODB.Recordset")

rsUser.Open "theTablename", objConnection, _

               .....<constant>..., adcmdTable

rsUser.Filter

rsUser.Delete

rsUser.Update



OR



Dim rsUser, strSQL

Set rsUser = Server.CreateObject("ADODB.Recordset")

strSQL = "DELECT FROM theTablename"

rsUser.Open strSQL, objConnection, _

               .....<constant>..., adcmdText





Thanks for any reply



David

> Actually, I am not allowed to have any EXTRA field in this table, beside

> USERNAME and PASSWORD.



Who designed this table?

I think you need to look at your specifications again...you need to work out

what the table is going to be used for, and then put in the appropriate

fields. I would, at the very least, at an autonumber field as primary key,

then you could do something like:



DELETE FROM table1

WHERE IDField NOT IN

    (SELECT TOP 100 IDField

    FROM table1)



Cheers

Ken

One possible solution is to use a sub-query.

In Access and SQL you have the advantage of

using the TOP modifier in the SELECT statement.



Your code would look something like this:





DELETE GIftShoppe.ProductName

FROM GIftShoppe WHERE GiftShoppe.ProductName IN (SELECT TOP 50 g.ProductName

FROM GiftShoppe g ORDER BY g.productname)



You must alias the second reference to the table, else Access will be

confused.



There is one caveat to this. All records will be deleted which have

have a matching value in the sub-query. If there are repeating values

in ProductName field then those records will be deleted. But then again,

you can filter/restrict the SELECT statement in the sub-query with a

WHERE clause.



I suggest you, develop your query in the Access Query Designer and then

cut and paste into your ASP page.



If you were running ORACLE, then the sub-query would reference the

ROWID column, as in  .... IN (SELECT "true" FROM table1 T1 where ROWID < 51)



hth,

Dallas Martin















----- Original Message -----

From: "David BG" <johnson_bg@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, November 22, 2000 7:09 PM

Subject: [asp_databases] VBScript in ASP question





> Hi, my question is about Vbscript in ASP.

>

> If I have 150 records in a MS access table, in which there is no field of

> record number. I want to write a statement to DELETE all previous records

> (e.g here number 1-50 records)and to keep the last 100 records (number

> 51-100). If the table contains 430 records, I want to keep only the last

100

> records, which are the record of number 331 to 430; and which are involved

> within number 1 to 330 are DELETED

>

> I try the following statement, but it DELETE all records

>

> Dim rsUser

> Set rsUser = Server.CreateObject("ADODB.Recordset")

> rsUser.Open "theTablename", objConnection, _

>               .....<constant>..., adcmdTable

> rsUser.Filter

> rsUser.Delete

> rsUser.Update

>

> OR

>

> Dim rsUser, strSQL

> Set rsUser = Server.CreateObject("ADODB.Recordset")

> strSQL = "DELECT FROM theTablename"

> rsUser.Open strSQL, objConnection, _

>               .....<constant>..., adcmdText

>

>

> Thanks for any reply

>

> David

>