-
Nov 22nd, 2000, 09:43 AM
#1
Thread Starter
New Member
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
-
Nov 22nd, 2000, 10:18 AM
#2
Frenzied Member
I don't know your record, but maybe you should insert a
date,or a number so that you can do
-WHERE date < ????
-WHERE number < ????
Describe a bit your table specs.
-
Nov 22nd, 2000, 11:26 AM
#3
Thread Starter
New Member
Thanks to Sebs,
But suppose I want delete any 10 record out of 110
in a table (in which there are only "username" and "password" fields. What can I write the statement for it ?
-
Nov 22nd, 2000, 11:39 AM
#4
Addicted Member
You're going to have to use another field to identify with, date would be a good one so you could delete the 10 oldest records. In VB script DATE gives you todays date but I guess you know that, but do something like:
SQL = "DELETE user, password FROM tablename WHER Date <= >= (To_Date('" & Starter & "', 'dd/MM/yyyy HH24:MI:SS')) "
Hope this helps
"It wasn't the booze that made me snooze, It was the Gin that did me in!"
-
Nov 22nd, 2000, 11:41 AM
#5
Frenzied Member
There's the IN in SQL:
....WHERE Number IN(1,2,3,4,5,6,7,8)
-
Nov 22nd, 2000, 01:11 PM
#6
Thread Starter
New Member
VBScript in ASP (connection to MS Access)
Thanks to Sebs and Skeen.
To Skeen:
But, unfortunately, I am not allowed to have and extra field in this prohibited table. That is why I encounter this problem of no INDEX to let the cursor pointing to a particular record(s) I want.
To Sebs:
Sebs, the "WHERE Number" , here "Number" is a fieldname in the table ? Or SQL SYNTAX key ?
Thanks for any reply
-
Nov 22nd, 2000, 01:19 PM
#7
Frenzied Member
field name in the table
-
Nov 23rd, 2000, 03:10 AM
#8
Addicted Member
Maybe pl/SQL is the way forward?
How about using some sort of formating of the paramaters to get rid of all the records that begin witha certain letter. I've never done it myself except formatting date, but I check it out at some point today and leave another post.
sort of:
WHERE user = Format('" & A& "')'ParsedVariableFromVB'
I'm not sure of the syntax, but theres probably something in pl/SQL which will do that. A collegue of mine is **** hot at pl/SQL so I'll tap him for some help later today.
C ya
Skeen
"It wasn't the booze that made me snooze, It was the Gin that did me in!"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|