Solved

Running Multiple Queries

Posted on 2014-02-09
9
300 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
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…

830 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