Solved

Running Multiple Queries

Posted on 2014-02-09
9
294 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
  • 4
  • 3
9 Comments
 
LVL 32

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 32

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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 32

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now