Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Running Multiple Queries

Posted on 2014-02-09
9
Medium Priority
?
307 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 300 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 600 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

664 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