How to trace the return value of shell command in VB

Posted on 2003-02-25
Medium Priority
Last Modified: 2008-02-01
Hi friends

I'm executing a .sql file(which has around 100 insert statements) from VB using shell command.
If any one of the insert statements fail in .sql i want to rollback the other transactions.
Any idea how to trap it.

I'm doing like this...

nResultVal = Shell("sqlplus vri/vri@gpoint @C:\Query.sql", vbMinimizedNoFocus)

This nResultVal is not consistent also.So i'mnt able to check for the success of all the queries.

Can u let me know??

pls mail at my id vidhyalakshmi_s@infy.com

Question by:vidhyalakshmi

Expert Comment

ID: 8015940

Expert Comment

ID: 8015959
its always suggested to extract the file contents and execute each statement seperately to get the most accurate results.  

Expert Comment

ID: 8019655
Just so you know what "nResultVal" really is: it's the process ID and can be used, for example, to check if the process is still running or not.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 8020611
I would try to keep the processes inside the VB app if possible.  Each line could be executed individually and you will know exactly where you are in the list of insert statements.

However, I have one other option for you: stored procedures.  You could take that .sql file and create a stored procedure. You didn't mention if you were in Access, oracle, SQLServer, etc.  But all of them have a way of creating a stored procedure that can handle a rollback for any missed transactions or failures.  Also, you are more likely to have fewer errors when the procedures are compiled inside your database because they can check for dependencies at compile/test.

They also can return specific error codes (you can even customize your own error codes in the stored procedure).  This can help with your error messages or error trapping in your front end code because the message could originate from the database where whomever is doing that work knows the error best and can give a more detailed reasoning for the problem.

Dan M

Expert Comment

ID: 8901152
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
LVL 49

Expert Comment

ID: 8972761
vidhyalakshmi, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Save as PAQ -- No Refund.

DanRollins -- EE database cleanup volunteer

Accepted Solution

Computer101 earned 0 total points
ID: 9667965
PAQed - no points refunded (of 50)

E-E Admin

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

621 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