Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Interrupting a long SQL Server database access from VB

Posted on 2001-07-09
19
Medium Priority
?
237 Views
Last Modified: 2008-02-07
This is a 'standard' problem but have not seen a specific
example that I can just copy that does actually work

User interface has a list of reports for user to pick and display with Crystal reports. Selecting report causes
an SQL stored procedure to be called to build the data of
say 50 to 100,000 records and display the report.

Problem: Data access times out.

Required: Ability to press ESC key or some other specific user interface key/button and exit from the fetch cycle gracefully.

Have tried DoEvents but with no success


0
Comment
Question by:josereyes
  • 7
  • 5
  • 5
  • +2
19 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6264693
What i do for really long-running reports, is that i build the data into a table, using a stored procedure that is started by a job (using sp_startjob).
This way, you vb app keeps responding, and only needs to find out when the data has been populated.
* check the job status (by querying the sysjobhistory table)
* have your own table that is updated by your stored proc to find out if the process has completed or not.

Cheers
0
 
LVL 5

Expert Comment

by:AndrewDev
ID: 6264699
Either increase your timeout or decrease the number of records you are getting in one go. Can you build your report up in bits with several database requests, each getting less data. Not the neatest of solutions but it is one mechanism I have used.

Regards
Andrew
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6264865
The stored procedure will run on the client. Best bet would be to use MTS and the ObjectContext and use the GetObjectContex().SetAbort method once the escape button has been pressed. That should take care of the stored procedure too...

regards,
CJ
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Accepted Solution

by:
VincentLawlor earned 400 total points
ID: 6265059
I would use an ADO connection here and use an Asynchronous Execute for your Stored procedure.

With this you can invoke ADODB.Command.Cancel cancelling your execution. You can then pass the resulting recordset to Crystal reports.


Eg.

Dim adoConnection as new ADODB.Connection
Dim adoCommand as new ADODB.Command
Dim adoRs as new ADODB.Recordset

adoConnection.Open "Connection String here"

adoCommand.ActiveConnection = adoConnection
adoCommand.CommandText = "Stored Proc"

adoCommand.CommandTimeOut = 100000
'As long as you want but remember that this will be overridden by SQL's timeouts no matter what you do.

rsResult = adoCommand.Execute ,,adAsyncExecute

While adoCommand.State = adStateExecuting

    'Use a flag here to check if the user pressed the ESC    button
    If fCancelled then
       adoCommand.Cancel
       'Do some clean up in here and report that the      command was cancelled
       Exit Sub/Function etc.
    endif

    DoEvents

wend

'Pass your recordset to crystal


This should solve your problem.

V.


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265098
VincentLawlor, first of all, you might review the EE guidelines about posting comments/answers at the bottom of the page.
Then, you have not properly read the question:
josereyes is calling the stored procedures basically from crystal reports...
0
 

Author Comment

by:josereyes
ID: 6265137
Not using MTS, CJ S !!!

Some clarification needed..

Running under Windows 2000, VB6, ADO 2.6.
One stored procedure takes full set of print data filters from ui
and then passes that on to a specific stored procedure to populate data table for use by a specific report. So cannot really use sp_startjob easily at this stage of
development.

Must be a way of asyncronously executing an ADO Command ?
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6265154

See my suggestion josereyes this can be done with an asynchronous ADODB Command

V.

To: Angellll, I was only offering a proposed solution he is using ADO not just Crystal and stored procedures. And I have read the EE guidelines.

V.
0
 

Author Comment

by:josereyes
ID: 6265155
Think I just got Vincent's mail on async command as I was typing in last comment !!!!
Also, not calling sp's from Crystal , Crystal display is just the last bit to get done after waiting for table to
be filled.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265160
hint: josereyes, you should reject the proposed answer, unless it "answeres" your question.

But actually, you might combine that proposal:
using ADO's asyncronous command to fill a table with the data which could be cancelled, and then fill the report, which should simply be based on this table. This will make the report much faster, and it can be reopened on the same data again without need to requery the data. The other advantage is that this table can be repopulated whenever you want, and the report be opened without using the stored procedure...

Cheers
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265182
So we are all speaking of the same thing: a stored procedure (asyncronously called) filling a table, and the crystal report basing itself on the table, and not on the stored proc...

VincentLawlor, part of the guidelines says that proposing an answer when other experts have already posted should not be done...
BTW, i cannot see where josereyes speaks of ADO prior to your comment...
Please don't think that your comment isn't worthful, but simply doesn't respect EE guidelines IMHO

BTW, if you use the WITHEVENTS + async execution, you won't need this loop (while state = adStateExecuting)...

Cheers
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6265214
To: angellll Sorry about that I'm new here.

I was offering a proposed solution and the question as far as I remember was not locked at that stage.

IMHO the solution I have proposed will work.

In future I'll post a comment.  
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265238
to VincentLawlor: Thanks again for your understanding. Again, i do not doubt that your proposal can work, but it might not be THE solution for josereyes. My first proposal can also work, but as you see, it is not possible for him "at this point of development".
Note that i posted my first proposal as a comment, even being first for the q :-)

Cheers
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6265262
Again apologies...

V.
0
 

Author Comment

by:josereyes
ID: 6265329
Please gentlemen ! Do we need to have these minor discussions over EE protocol ? It's a distraction.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265347
my apologies...
0
 

Author Comment

by:josereyes
ID: 6265404
To Vincent....

UI is in a form, and this calls a method inside a DLL to execute the Command,
where is best place to place DoEvents ?
Presume I catch ESC key press in KeyPress event ?
Regards Jose
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265585
If you use the loop as suggestion, you need the doevents inside that loop. If you add the withevents in order to catch the event of the command to complete / fail, you don't need the doevents at all.

I would catch the esc key in the KeyUp event, and compare KeyCode to the vbKeyEscape constant ...

Cheers
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6265659
Agreed.

If you are calling the command from within a DLL, I assume you are using some public accessor functions to execute the commands, you will need to declare an Event in the DLL to indicate that the Asynchronous command execution call has completed.
You will also need to add a public accessor to the DLL to set the cancel flag from your main application.

WRT Capturing the ESC key:

'set your form.keypreview=true


Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

If KeyCode = vbKeyEscape And Shift = 0 Then
   objYourDLL.Cancel = True
End If

End Sub

V.



 
0
 

Author Comment

by:josereyes
ID: 6265938
Thanks Vincent,

Tried your last but one solution , with some of your last solution already there. Works fine so far .

Regards,
Jose
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

782 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