Results 1 to 8 of 8

Thread: VBScript in ASP (connection to MS Access)

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2000
    Posts
    12

    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
    David

  2. #2
    Frenzied Member sebs's Avatar
    Join Date
    Sep 2000
    Location
    Aylmer,Qc
    Posts
    1,606
    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.

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2000
    Posts
    12

    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 ?
    David

  4. #4
    Addicted Member Skeen's Avatar
    Join Date
    Jul 2000
    Location
    Abingdon, Oxon
    Posts
    138
    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!"

  5. #5
    Frenzied Member sebs's Avatar
    Join Date
    Sep 2000
    Location
    Aylmer,Qc
    Posts
    1,606
    There's the IN in SQL:

    ....WHERE Number IN(1,2,3,4,5,6,7,8)


  6. #6

    Thread Starter
    New Member
    Join Date
    Nov 2000
    Posts
    12

    Thumbs up 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
    David

  7. #7
    Frenzied Member sebs's Avatar
    Join Date
    Sep 2000
    Location
    Aylmer,Qc
    Posts
    1,606
    field name in the table

  8. #8
    Addicted Member Skeen's Avatar
    Join Date
    Jul 2000
    Location
    Abingdon, Oxon
    Posts
    138

    Thumbs up 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
  •  



Click Here to Expand Forum to Full Width