Solved

Running Multiple Queries

Posted on 2014-02-09
9
304 Views
Last Modified: 2014-02-13
I have a VBScript page that is designed to run about a dozen queries to create separate tables for different conditions. The first four are as follows:
 
sql="INSERT INTO membersBkup SELECT members.* FROM members"
 Set rs=conn.Execute(sql)
  
 sql="DELETE * from membersTemp"
 Set rs=conn.Execute(sql)
 
 sql="INSERT INTO membersTemp SELECT members.* FROM members"
 Set rs=conn.Execute(sql)
  
 sql="DELETE * from members"
 Set rs=conn.Execute(sql)

Open in new window

The entire sequence didn't work, so I tested running one at a time. When I run the first three, it runs as intended. When I run the first four, membersTemp is always empty.

It appears that members gets deleted before the insert into the temp file. Is this a timing issue? Is there a way to delay between executions? Is there a better way to do this?
0
Comment
Question by:slegy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 39846090
You could try to create just one sql statement and separate each line with a semi colon. If they doesn't work I would create a stored procedure with all of the statements located there.
0
 

Author Comment

by:slegy
ID: 39846180
Thank you so much again. I tried using the single statement with a semi colon separator and got the same results. I've never used a stored procedure, so I'll have to start researching.
0
 
LVL 33

Assisted Solution

by:Big Monty
Big Monty earned 100 total points
ID: 39846372
run the following query in your databse. it'll create the stored procedure that you can then call from wherever:

create PROCEDURE dbo.sp_doMembersMaintenance
@userID int
AS
BEGIN
   SET NOCOUNT ON;

INSERT INTO membersBkup SELECT members.* FROM members;
  
DELETE * from membersTemp;
 
INSERT INTO membersTemp SELECT members.* FROM members;

DELETE * from members;

END

Open in new window


and in your vbscript code, you would do thr following to run the stored procedure:

sql = exec sp_doMemberMaintenance"
set rs = conn.Execute( sql )

Open in new window

0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 39847093
Are you using SQL Server or Access? You have included this in the Access zone, but not the SQL Server zone.

If you're using Access, then you can't run more than one statement at a time (i.e. the semi-colon delimited method), and Access does not use stored procedures (although it can call server-based SPs).

You don't need to set a rs variable when doing this so change this line:

Set rs = conn.execute(sql)

to this:

conn.Execute(sql)

When you run the first 4, does membersBkp contain data?

Are membersBkp and membersTemp EXACTLY the same structure as the members table? I get nervous when I see "SELECT *", unless you're using SELECT INTO syntax.

You might try changing the order in which these occur:

Delete from membersTemp
Insert into membersTemp and membersBkp
Delete from members
0
 

Author Comment

by:slegy
ID: 39847458
We are running an ASP site with Access. Yes, the tables are exactly the same structure. I am trying some of the suggested changes.
0
 

Author Comment

by:slegy
ID: 39847670
Obviously, I need to rethink this entire approach. It never occurred to me that the queries would not be executed sequentially. Here is the entire list. When I omit the three deletes at the bottom, everything works. When the deletes are included, nothing works - as if they are executed first. I would like to understand why it works this way.
 
sql="INSERT INTO membersBkup SELECT members.* FROM members"
 conn.Execute(sql)
  
 sql="DELETE * from membersTemp"
 conn.Execute(sql)
 
 sql="INSERT INTO membersTemp SELECT members.* FROM members"
 conn.Execute(sql)
 
 sql="DELETE * from membersRenew"
 conn.Execute(sql)
  
 sql="DELETE * from membersrenewUpdt"
 conn.Execute(sql)
  
 sql="DELETE * from membersNew"
 conn.Execute(sql)
  
 sql="DELETE * from membersUnresolved"
 conn.Execute(sql)
 
 sql="DELETE * from membersUnauthorized"
 conn.Execute(sql)
  
 sql="DELETE * from memLogin"
 conn.Execute(sql)
 
 sql="INSERT INTO membersRenew SELECT membersTemp.* FROM membersTemp WHERE membersTemp.appType='R' AND membersTemp.memUpdt='N' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="INSERT INTO membersrenewUpdt SELECT membersTemp.* FROM membersTemp WHERE membersTemp.appType='R' AND membersTemp.memUpdt='Y' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="INSERT INTO membersNew SELECT membersTemp.* FROM membersTemp WHERE membersTemp.appType='M' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="DELETE * FROM membersTemp WHERE membersTemp.appType='R' AND membersTemp.memUpdt='N' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="DELETE * FROM membersTemp WHERE membersTemp.appType='R' AND membersTemp.memUpdt='Y' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="DELETE * FROM membersTemp WHERE membersTemp.appType='M' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="INSERT INTO membersUnresolved SELECT membersTemp.* FROM membersTemp"
 conn.Execute(sql)
 
 sql="INSERT INTO membersunauthBkup SELECT membersUnauth.* FROM membersUnauth"
 conn.Execute(sql)
 
 sql="INSERT INTO membersUnauthorized SELECT membersUnauth.* FROM membersUnauth"
 conn.Execute(sql)
 
 sql="INSERT INTO memberloginBkup SELECT memberLogin.* FROM memberLogin"
 conn.Execute(sql)
 
 sql="INSERT INTO memLogin SELECT memberLogin.* FROM memberLogin"
 conn.Execute(sql)
  
 sql="DELETE * from members"
 conn.Execute(sql)
 
 sql="DELETE * from membersUnauth"
 conn.Execute(sql)
  
 sql="DELETE * from memberLogin"
 conn.Execute(sql)

Open in new window

To clarify, this information is being downloaded to update a local database.
0
 

Author Comment

by:slegy
ID: 39848795
My apologies to both of you. This was serious programmer error. All is well.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39850442
good to hear, glad you got it working!

please close out this question, awarding ;points if any of the suggestions helped you resolve the problem, or do a request for attention if you need a mod to close it out.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question