joris_navius
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
rst.MoveNext
Loop
rst.Close
con.Close
End Sub
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
ASKER
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