?
Solved

Stop ADO query in the middle of execution in VB6

Posted on 2004-08-04
6
Medium Priority
?
530 Views
Last Modified: 2013-12-25
Hi all

I am trying to do a query which returned around 10.000 rows and put it in the MSHFlexGrid.
Command is like

strsql = "select * from [table_name]"
rs.open strsql,[connection_name]

set mshflexgrid.datasource = rs

I would like to stop the query in the middle of execution and put the happen being executed rows in the mshflexgrid. (like in SQL Query analyzer)

Is that possible ?

Please advise.
0
Comment
Question by:joris_navius
[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
  • 2
6 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 11723602
I guess it's not possible. But my question for you is: why get so many rows in your flexgrid ? It will take a lot of memory and time to get all those records.
0
 

Author Comment

by:joris_navius
ID: 11723931
Hi Dhaest

I am creating small tool as an add-in in my applications (like Query Analyzer) so user can create their own query. Just in case the user select lot of rows and want to stop it, do you have any advise about this ?

thanks
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 11726204
You cannot expect that you will stop a query execution in the middle and it will return a half of results. Sure, for simple queries like "SELECT * FROM SomeTable" this might work, but in general, it depends on what execution plan SQL Server will use. Specially in complex queries with multiple JOINs and sub-selects.

I suggest you must limit amount of rows BEFORE the quety execution. So if user will try to execute query "SELECT Something FROM SOmeTable" with result limit of 10000 rows, you will have to do this:

sQuery = "SELECT TOP " & iResultLimit & Mid$(sQuery, 7) ' replace SELECT with SELECT TOP x
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 11726298
Or better, use MaxRecords property of Recordset object:

Private Sub Form_Load()
    Dim con As New ADODB.Connection
    Dim rst As New ADODB.Recordset
   
    con.Open "<your connection string>"
   
    rst.MaxRecords = 10000 'YOUR RECORD LIMIT
   
    rst.Open"SELECT Something FROM SOmeTable", con
   
    Do While Not rst.EOF
       ' add rows to grid

        rst.MoveNext
    Loop
   
    rst.Close        
    con.Close
   
End Sub
0

Featured Post

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!

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…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

801 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