• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

Stop ADO query in the middle of execution in VB6

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.
  • 2
1 Solution
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.
joris_naviusAuthor Commented:
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 ?

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
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

End Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now