Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source
Go Back  Xtreme Visual Basic Talk > > > Datagrid\FlexGrid ADO Data Source


Closed Thread
 
Thread Tools Display Modes
  #1  
Old 03-26-2004, 12:47 PM
Medic Medic is offline
Junior Contributor
 
Join Date: Dec 1999
Location: California, USA
Posts: 220
Question Datagrid\FlexGrid ADO Data Source


What I need to accomplish is incredibly common for database programming, yet I have searched and searched and seen no information on it.

I have a form "frmLookup" which is designed to display records in a database in a grid format. On this form I have a text box "txtSearchFor", a como-box "cboOrderBy" and a flexGrid\Data Grid.

When the form loads it is passed an SQL statement which loads the information from a table into the flexgrid or Data Grid. The user can then use the combo-box to re-sort the grid by any of a set number of fields. They can then use the text box to start typeing in information they seek in the table, and the table should automatically scroll as they are typing to the value that most closely matches what they "seek". The user then finally selects the record from the table and the key field is passed back to the calling form.

Simple right? WRONG!

I can use the intrinsic DAO ability to open the Foxpro tables, but it is not so fast. I have to use a DAO data control bound to a flexGrid, and it takes about 1.5 seconds per 1000 records in the table (it is not uncommon to have 10-20k records). I am not satisfied with this, so I switched to using an ADO record source (which also has a number of features the DAO does not) and use an OLEDB Provider for an Advantage database engine (the non-client server version of this engine is FREE and does FoxPro, Dbase and their own version). This makes me VERY happy because the load now takes about 1 second on 7,700 records.

The problem is, I cannot bind the flexGrid to the ADO data source. When I use the statement:
Set flxGrid.DataSource = AdsRecordset
I get an error "Object variable or With Block Variable Not set"

I CAN use the DataGrid in this fashion (which is where I get the 1 second load time) but the datagrid does not have sort capabilities, nor does it have the ability to "soft seek" as I have described in the textbox. I can use ADSRecordset.seek "sometext" but then I cannot get the DataGrid to scroll.

There HAS to be someone else out there that has already figured this out.

Any ideas?
__________________
A computer program will always do what you tell it to do, but rarely what you want it to do.
  #2  
Old 03-26-2004, 02:30 PM
Shurik12Datagrid\FlexGrid ADO Data Source Shurik12 is offline
Steppe Walker

Retired Moderator
* Expert *
 
Join Date: Jul 2002
Location: Ukraine/Russia/Belgium
Posts: 7,227
Default

Hi,

Just pick MSHFlexGrid (Hierarchical Flexgrid) instead, it supports ADO.

(You can use FlexGrid when working with ADO too but in this case you'll need to loop through the recordset and fill the grid)

Regards,
Shurik.
__________________
"A diaper is not like a computer that makes satisfying burbling noises from time to time, hinting at great inner complexity." Malcolm Gladwell

"I'm sitting here completely surrounded by no beer." Onslow, 'Keeping up appearances'
  #3  
Old 03-26-2004, 02:57 PM
Medic Medic is offline
Junior Contributor
 
Join Date: Dec 1999
Location: California, USA
Posts: 220
Default

Quote:
Originally Posted by Shurik12
Hi,

Just pick MSHFlexGrid (Hierarchical Flexgrid) instead, it supports ADO.

(You can use FlexGrid when working with ADO too but in this case you'll need to loop through the recordset and fill the grid)

Regards,
Shurik.
OMG! I cannot beleive it was THAT simple. Not being familiar with bound data controls (I usually avoid them) this was a bit of a surprise to me that they were so different in what they supported. Thanks!
__________________
A computer program will always do what you tell it to do, but rarely what you want it to do.
  #4  
Old 03-26-2004, 03:04 PM
Medic Medic is offline
Junior Contributor
 
Join Date: Dec 1999
Location: California, USA
Posts: 220
Default

Here is something interesting. If I bind an ADO recordsource to a datagrid, It takes about 1 seconds for a table with 7700 records in it. If I bind it to the flexgrid, it takes 5-6 seconds!

AND... if I use code to fill the recordset (Flyguy's code from the knowledgebase section) it takes 58 seconds!!!

WTH?

So I guesss I still think it would be nice if I can add soft-seek functionality to the datagrid. If I set the datagrid.firstrow property, I am suppsed to set it equal to a bookmark. I tried setting a string value to the bookmark from recordset.seek operation, and then passing that string to the datagrid, but it errored out when I tried to say datagrid.firstrow = strBookMark. I will use the HflexGrid if I have to, but it seems liek a perrformance hit that will only get worse as the tables get longer and the systems get more antiquated.
__________________
A computer program will always do what you tell it to do, but rarely what you want it to do.

Last edited by Medic; 03-26-2004 at 03:11 PM. Reason: wanted to add...
  #5  
Old 03-26-2004, 03:17 PM
MKoslof's Avatar
MKoslofDatagrid\FlexGrid ADO Data Source MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

You don't have to use a BOUND control. Instead of the MSHFlexGrid, you could use the MSFlexGrid. You can bind a recordset, array, UDT, etc. to this grid control..I actually prefer to use the MSFlexGrid over the MSHFlexGrid.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
  #6  
Old 03-26-2004, 03:37 PM
Shurik12Datagrid\FlexGrid ADO Data Source Shurik12 is offline
Steppe Walker

Retired Moderator
* Expert *
 
Join Date: Jul 2002
Location: Ukraine/Russia/Belgium
Posts: 7,227
Default

To MKoslof:

>Instead of the MSHFlexGrid, you could use the MSFlexGrid.

OK, right, one thing though. If we have an ADO recordset then the only way to get into MSFlexGrid is looping through it.
A DAO recordset will be just fine for doing Set MSFlexGrid.DataSource= rsDAO

The MSHFlexGrid in it's turn supports ADO so
Set MSHFlexGrid.DataSource=rsADO
or Set MSHFlexGrid.RecordSet=rsADO
are enough to populate the gird whereas in case of a DAO recordset you will need to loop again.


Regards,
Shurik.
  #7  
Old 03-26-2004, 03:42 PM
MKoslof's Avatar
MKoslofDatagrid\FlexGrid ADO Data Source MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

To a certain degree yes...but the MSFlexGrid supports ADO just fine .

When you say you have to loop a recordset..you are basically correct, but is there anything wrong with this? Also, what if I have a UDT or an array of data I want to display on the fly...the MSFlexGrid is the best option. What if I am trying to bring in data from a text file or textStream....MSFlexGrid is the best option.

Also, I have a big problem with bound controls. Because refreshing them or having them display updated data if I am making run-time modifications to the recordset can be troublesome. With the FlexGrid I can populate and re-populate, reset rows and columns dynamically and other functions with greater ease.

But just my opinion .
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
  #8  
Old 03-26-2004, 04:13 PM
Shurik12Datagrid\FlexGrid ADO Data Source Shurik12 is offline
Steppe Walker

Retired Moderator
* Expert *
 
Join Date: Jul 2002
Location: Ukraine/Russia/Belgium
Posts: 7,227
Default

>...but the MSFlexGrid supports ADO just fine
Sure thing I did not try to prove the opposite.

>When you say you have to loop a recordset..you are basically correct, but is there anything wrong with this?
Of course not. But I think it should be slower (if it's of any importance).

As for bringing data from textStream, etc I think there's no major difference between doing it in MSHFlexGrid or MSFlexGrid. And again populating and re-populating can be done in MSHFlexGrid too.
On the other hand I addmit if there's no a hierarchical recordset involved choosing between the two can be a matter of personal preferences.

Edit by Shurik:
...and no binding controls of course.

Regards,
Shurik.
__________________
"A diaper is not like a computer that makes satisfying burbling noises from time to time, hinting at great inner complexity." Malcolm Gladwell

"I'm sitting here completely surrounded by no beer." Onslow, 'Keeping up appearances'

Last edited by Shurik12; 03-27-2004 at 04:57 AM.
  #9  
Old 03-26-2004, 04:21 PM
MKoslof's Avatar
MKoslofDatagrid\FlexGrid ADO Data Source MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

What method do you use to repopulate a bound MSHFlexGrid control. Say

a) I bind a recordset to the grid

b) Then I update the recordset at run-time. I want to dynamically reflect the changes in the Grid.

Just curious how you would handle this.

EDIT: I disagree that looping until EOF is slower than using the SetDataSource method. Do a test and time it . A bound control contains an additional layer of processing (*well, depends on the control) and the time difference is minimal to none.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
  #10  
Old 03-26-2004, 05:33 PM
Shurik12Datagrid\FlexGrid ADO Data Source Shurik12 is offline
Steppe Walker

Retired Moderator
* Expert *
 
Join Date: Jul 2002
Location: Ukraine/Russia/Belgium
Posts: 7,227
Default

As for the repopulating the MSHFlexGrid in a situation similar to what you
are describing (if I understand correctly "updating recordset at run-time")
nothing spectacular just:

Code:
rs.Open "Select * From Table1", oConn,.. Set MSHFlexGrid.Recordset=rs rs.Close ' some changes are made to Table1 by say oConn.Execute "UPDATE Table1 ..." rs.Open "Select * From Table1", oConn,... Set MSHFlexGrid.Recordset=rs ...
You might be right that the loop is not slower. Will try to give it shot and do some testing to undeceive myself.
__________________
"A diaper is not like a computer that makes satisfying burbling noises from time to time, hinting at great inner complexity." Malcolm Gladwell

"I'm sitting here completely surrounded by no beer." Onslow, 'Keeping up appearances'
  #11  
Old 03-26-2004, 05:47 PM
MKoslof's Avatar
MKoslofDatagrid\FlexGrid ADO Data Source MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

So you have to reopen the recordset object and rebind the recordset to the bound control. So you need to close the recordset, re-create a new object instance, and rebind the datasource.

With a FlexGrid:
1) create a function to populate the grid, call it LoadGrid.
2) run your command object to execute an update command, delete command,etc.
3) reload the Grid on the fly, call LoadGrid again.

I guess it comes down to preference...but I don't like having to reinstantiate another recordset object and then creating another object "SET" instance.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
  #12  
Old 03-26-2004, 05:57 PM
Shurik12Datagrid\FlexGrid ADO Data Source Shurik12 is offline
Steppe Walker

Retired Moderator
* Expert *
 
Join Date: Jul 2002
Location: Ukraine/Russia/Belgium
Posts: 7,227
Default

Okay, I believe I see your point.

Regards,
Shurik.
__________________
"A diaper is not like a computer that makes satisfying burbling noises from time to time, hinting at great inner complexity." Malcolm Gladwell

"I'm sitting here completely surrounded by no beer." Onslow, 'Keeping up appearances'
  #13  
Old 03-26-2004, 06:40 PM
Medic Medic is offline
Junior Contributor
 
Join Date: Dec 1999
Location: California, USA
Posts: 220
Cool

Quote:
Originally Posted by MKoslof
EDIT: I disagree that looping until EOF is slower than using the SetDataSource method. Do a test and time it . A bound control contains an additional layer of processing (*well, depends on the control) and the time difference is minimal to none.
Wow! I seem to have sparked a debate. I too do not like to use Bound controls if I can avoid it, and in fact prefer to do everything in straight code if I can, as it gives me greater control. But I think you missed my point above. First of all, the FlexGrid does NOT support binding to the recordset at all, and will error out UNLESS the recordset is DAO, so you have to loop through it. I DID this and timed it for you. The same SQL and table that populates in a BOUND DataGrid (not FlexGrid) in about 1 second, takes 7 seconds in a BOUND MsFlexGrid with DAO, and about 8 and a half seconds in a BOUND MSHFlexGrid (not bound to a data control but bound to a ADO recordset). Are you ready for this. Looping through 7,578 records TAKES 58 (that's FIFTY EIGHT!!!) seconds!!! I used flyguy's code in the knowledgebase to accomplish this.

So to recap:
DataGrid bound to an ADO recordset object = 1 sec
MsFlexGrid bound to a DAO recordset object (or data control) = 7 sec
MsHFlexGrid bound to an ADO recordset object = 8 seconds
and
MsFlexGrid Loaded Manually by looping through the recordset = 58 seconds

For me, update is NOT a priority, and neither is editing. (Read ONLY is FINE) I simply am using the table to allow the user to select a range of key fields for a report. What IS definitely a priority is SPEED! Some of my clients have 10k to 20 thousand records, and are not going to be happy to wait for the list to pop up. ALso, I am concerned that my machine, which is a 3.2Mhz P4, is not going to be representative of my client's machines!

So to argue with both of you, the evidence would seem to support a superiority for the old fashioned DataGrid control, since speed is my priority. Now if ONLY I can get the datagrid control to automatically scroll to the closest match record as the user types their search criteria into a text box.
__________________
A computer program will always do what you tell it to do, but rarely what you want it to do.
  #14  
Old 03-27-2004, 09:13 AM
MKoslof's Avatar
MKoslofDatagrid\FlexGrid ADO Data Source MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

No way . The DataGrid is not faster then either of the FlexGrid controls and:

1) You can not make structural row and column changes (such as resizing controls and cells) at run-time

2) You can not autosize cells or change background color, etc.

If you go to the code library, there is a wonderful tutorial written by FlyGuy with code samples for both the MS and MSHFlexGrid. Review them.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
  #15  
Old 03-27-2004, 11:37 AM
Medic Medic is offline
Junior Contributor
 
Join Date: Dec 1999
Location: California, USA
Posts: 220
Default

Quote:
Originally Posted by MKoslof
No way . The DataGrid is not faster then either of the FlexGrid controls and:

1) You can not make structural row and column changes (such as resizing controls and cells) at run-time

2) You can not autosize cells or change background color, etc.

If you go to the code library, there is a wonderful tutorial written by FlyGuy with code samples for both the MS and MSHFlexGrid. Review them.
OK,
I am sorry to burst your bubble, but for this application, using the source tables I am using, and the data provider I am using, it is significantly (7 to 8 times) faster. I did not just make those numbers up for fun.
And as I have mentioned before, I HAVE read flyguy's tutorials on the flexgrid, and it was HIS code that filled the flexgrid from an ADO data source by looping through the recordsets, and it was this EXACT code that took 58 seconds vice the one.
Perhaps my data provider is optimized for use on the datagrid, as that was the object they used in their tutorials, but It does not surprise me as it does you, since the flexgrids have so many more features, it stands to reason that they would have more overhead.

As for the autosizing and the changing of the background color, I do not need these features. I know what size my fields will need to be, and I happen to be partial to WHITE backgrounds in business applications for fields where data is to be displayed.

I am not arguing with you about the power of the flexgrid, as I am sure it is well suited for most applications, and agree that you can do more with it, but it is obviously not the correct choice for this one.

Now my question, which remains unanswered, is HOW do we autoscroll the contents of the DATAGrid to show and highlight the row which most closely matches what we typed into a text box.
__________________
A computer program will always do what you tell it to do, but rarely what you want it to do.
  #16  
Old 03-27-2004, 06:39 PM
MKoslof's Avatar
MKoslofDatagrid\FlexGrid ADO Data Source MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

You need to look into the bookmark property of the DataGrid...this will allow you to select a cell and its underlying recordset value.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
  #17  
Old 03-31-2004, 04:47 PM
Medic Medic is offline
Junior Contributor
 
Join Date: Dec 1999
Location: California, USA
Posts: 220
Angry

Quote:
Originally Posted by MKoslof
You need to look into the bookmark property of the DataGrid...this will allow you to select a cell and its underlying recordset value.
the bookmark property is for UNBOUND data grids. Will not work properly here. I can obtain the index of the row I want scrolled to the top, but I cannot figure out how to get the control to ACTUALLY scroll.

DataGrid.FirstRow errors out, has no example, and I cannot get it to work
DataGrid.RowBookMark will only wok if the row is visible

This is incredibly frustrating. I have spent 3 weeks on this one issue which, if ANYONE has ever used a widely used database program, they have seen this in action. It shows up whenever you want to select ranges for a report. There is a Grid of information, you select the appropriate key fields for your ranges.
__________________
A computer program will always do what you tell it to do, but rarely what you want it to do.
  #18  
Old 03-31-2004, 04:51 PM
MKoslof's Avatar
MKoslofDatagrid\FlexGrid ADO Data Source MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

"if ANYONE has ever used a widely used database program, they have seen this in action."

I have used many of these applications...and I develop them to . Just another reason to not use a bound control.

I am sorry that you are so frusterated. But, my original advice was to use an unbound control. You have your own agenda..which is fine . But, you'll have to figure out a way to do this..if we don't have an answer, maybe try browsing some Google groups, if this is time sensitive. Or, think of other options, your hatred of the unbound grid control is not our fault .
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
  #19  
Old 04-01-2004, 12:10 PM
Medic Medic is offline
Junior Contributor
 
Join Date: Dec 1999
Location: California, USA
Posts: 220
Angry

Quote:
Originally Posted by MKoslof
"if ANYONE has ever used a widely used database program, they have seen this in action."

I have used many of these applications...and I develop them to . Just another reason to not use a bound control.

I am sorry that you are so frusterated. But, my original advice was to use an unbound control. You have your own agenda..which is fine . But, you'll have to figure out a way to do this..if we don't have an answer, maybe try browsing some Google groups, if this is time sensitive. Or, think of other options, your hatred of the unbound grid control is not our fault .
Mkoslof,
Why on earth would you say that I hate the unbound grid control? Part of my frustration stems from the fact that you do not seem to READ what I have taken so much time to post. Take for example a quote from my previous post:
Quote:
I too do not like to use Bound controls if I can avoid it, and in fact prefer to do everything in straight code if I can, as it gives me greater control
To say that I just must HATE the unbound controls because I have a personal AGENDA is just plain wrong. As I have already explained, if I use the control as UNBOUND, it takes 58 seconds to populate what takes a bound control 1 second! This is absolutely unacceptable, and you KNOW it. I used Flyguy's example, which you have already mentioned as a good source, but it just takes too long.
I would love to use the FlexGrid and populate the grid myself, but 58 seconds...come on!
__________________
A computer program will always do what you tell it to do, but rarely what you want it to do.
  #20  
Old 04-01-2004, 12:24 PM
MKoslof's Avatar
MKoslofDatagrid\FlexGrid ADO Data Source MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

And posting this thread gets you no closer to solving your problem . I have never had a 58 second population time. You are way too sensitive, I was simply stating that you decided to use a bound control that is not as flexible as an unbound control. If you notice the smiley faces in my post, there was no ill will in this post.

Typically, I find if I have to loop a large recordset it may take several seconds longer..that's all. If I am using an array, I tend to populate it within a UDT and store it..then when I need it, I loop until Ubound() populating the Grid..this is typically very quick. I don't know the code or database driver you are using. But speed has never been an issue for me. As important as speed is, it is not the only factor in development. I can develop a "lightning" quick application, but IF it also provides the user with no flexibility or options...it is a give and take situation.

So, are you closer to actually solving your issue? Does a bound control even have this ability? If not, what's your next step . It appears you are still frusterated, sorry to hear that. But, like all of us, I have some work to do, so unless you have another idea or are asking about some other strategries..there is nothing else I can do for you. Good luck.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown

Last edited by MKoslof; 04-01-2004 at 12:29 PM.
Closed Thread


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Inno setup script giannicotina Installation / Documentation 3 02-06-2004 11:18 PM
Help, ADO Data control not working!!! (Urgent) Süsser Tod Database and Reporting 6 08-22-2003 02:11 PM
Files to copy on Setup wendy General 5 03-25-2002 01:19 PM
Data Environment - ADO claude General 2 03-15-2001 11:53 AM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source
 
Datagrid\FlexGrid ADO Data Source
Datagrid\FlexGrid ADO Data Source
 
-->