Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Mar 2006
    Location
    Sumter, SC
    Posts
    178
    Thanks
    10
    Thanked 4 Times in 4 Posts

    Inserting Multiple Records via VBScript

    Hi guys,

    The issue I'm having is trying to figure out the logic behind how to insert multiple records into an Access database with out having to run the add or edit scripts over and over again. Basicly its an event calendar where there is an option to insert a recurring event. (ex: party at bob's every wed night at 7:00). Assuming the starting date is 8/8/07 and I can use the dateadd function to add 7 days to the starting date, how could I use this to insert these dates into the database with out running the script over and over?

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    one way would be to use some asp code to build a string of dates as a comma seperated list and then use that variable in your sql insert statement using an IN statement in your WHERE clause

    Code:
    Dim startDate
    Dim iNumWeeks
    Dim sDates
    
    
    startDate = CDate("8/08/2007")
    sDates = "startDate"
    iNumWeeks = 10 'let's say the party every wednesday is for a period of 10 weeks.
    for i = 1 to iNumWeeks
         sDates = sDates & "','" & DateAdd("d",7,startDate) 
         startDate =  DateAdd("d",7,startDate)  'increment this value by 1 week
    Next
    
    'as an insert statement
    sSQL = "INSERT INTO MyTable (EventField) VALUES('party at Bob''s - 7pm') WHERE DateField IN ('" & sDates & "')"
    
    'as an update statement to update what may already be there 
    sSQL = "UPDATE MyTable SET EventField = EventField + 'party at Bob''s - 7pm' WHERE DateField IN ('" & sDates & "')"
    Last edited by miranda; 08-05-2007 at 01:08 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •