Visual Basic Q&A

Symptoms
As its name suggests, the MSHFlexGrid control that ships with Visual Basic6.0 allows database data to be displayed in an aggregated, hierarchicalformat. Using this control in conjunction with the Data EnvironmentDesigner, developers can easily and logically display information containedin several different database tables, which are connected through the useof foreign keys.
Resolution
The Data Environment Designer is a tool that enables developers toencapsulate ActiveX Data Object (ADO) connection and command objects in areusable Visual Basic object. This sample project makes use of a DataEnvironment Designer to establish a connection to SQL Server and toretrieve related data from two tables in the Pubs sample database viacommand objects. The related data is then displayed in a hierarchicalformat in the MSHFlexGrid control.
To create this sample, do the following:Create a new Standard EXE project. Form1 is created by default.On the Project menu, click Components, and click the Designers tab inthe Components window. On the Designers tab, make sure that the DataEnvironment check box is selected. On the Controls tab, select the checkbox next to the Microsoft Hierarchical FlexGrid Control 6.0 option. Thenclick OK to exit the Components window.On the Project menu, click Add Data Environment, which adds theDataEnvironment1 object to your project.If the object window for the DataEnvironment1 object is not visible,right-click the DataEnvironment1 object in Project Explorer and selectView Object.In the DataEnvironment1 object window, right-click the Connection1 iconpictured beneath the DataEnvironment1 icon and select the Propertiesoption from the pop-up menu (this displays the Data Link Propertieswindow).Select the Use Connection String option, and click Build. Select theMicrosoft OLE DB Provider for SQL Server option, and click Next.Under step 1 on the Connection tab, select or enter the name of the SQLServer that you are using for this project.Under step 2, enter a user name and password that are valid values foryour SQL Server Environment. Also select the Allow Saving Of Passwordcheck box.Under step 3, select the Pubs sample database from the combo box. ClickTest to test your connection information. If your test is successful,click OK to exit the Data Link Properties window.Right-click the Connection1 icon in the object window for theDataEnvironment1 object. Select the Add Command option from the pop-upmenu to add a new command object to your project.Right-click the new Command1 icon in the DataEnvironment1 objectwindow and select the Properties option from the pop-up window (thisdisplays the Command1 Properties window).On the General tab, select the SQL Statement option button and cut andpaste the following code into the enabled text box:

SELECT pub_id, pub_name FROM Publishers Then click OK to close the Command1 Properties window.Right-click the Command1 icon in the DataEnvironment1 object window,and select the Add Child Command option from the pop-up menu (this addsanother icon to the DataEnvironment1 object window called Command2).Right-click the Command2 icon in the DataEnvironment1 object window andselect the Properties option from the pop-up menu (this displays theCommand2 Properties window).On the General tab, select the SQL Statement option button and cut andpaste the following code into the enabled text box:

SELECT pub_id, title, price FROM Titles On the Relation tab, make sure that the pub_id value is selected inboth the Parent Fields and the Child Fields/Parameters combo boxes. Thenclick Add (this creates a hierarchical relationship between the pub_idfields that are selected in the two command objects).Place an MSHFlexGrid control onto Form1, the default form of theproject.Select the MSHFlexGrid control that you added to Form1, then select theProperties window.Select the DataEnvironment1 option for the DataSource property, andselect the Command1 option for the DataMember property.Now run your project. The MSHFlexGrid control is filled with data fromboth the Publishers and Titles tables of the Pubs database, and theinformation is hierarchically grouped.NOTE: The results of steps 18 and 19 can also be accomplished at run timeby adding the following code to the code window of Form1:

Option ExplicitPrivate Sub Form_Load()DataEnvironment1.Command1Set MSHFlexGrid1.DataSource = DataEnvironment1.rsCommand1End Sub

Symptoms
Clicking the plus sign to expand a stored procedure in the Data View window in either Visual Basic, Visual InterDev, or Visual J++ generates the following error message if the stored procedure contains calls to extended stored procedures that return something other than 0 or 1, such as xp_cmdshell:

Devenv.exe – Application ErrorThe instruction at “0×412406f3″ referenced memory at “0×00000006″. The memory could not be “read”.
Resolution
Clicking the plus sign executes the stored procedure to enumerate the parameters and tables for the stored procedure. When the data environment tries to execute xp_cmdshell it generates an error.
NOTE: This problem does not occur with SQL Server 7.0 or later. It also does not occur under a Microsoft Windows 2000 environment.

Symptoms
Attempting to rebind the MSHFlexGrid to a different Data Environment as theDataSource property results in an error:

Runtime Error 30022, The Hierarchical FlexGrid does not support therequested type of data binding.
Resolution
If the MSHFlexGrid.DataMember is set to a Data Environment other than theoriginal, the MSHFlexGrid attempts to rebind the data using the previousDataSource property setting. Because the same Command does not exist in thesubsequent Data Environment, an error results.

Symptoms
When you try to use the Query Designer against the MS Shape Provider inthe Data Environment, the following error message is returned:

Query Designer encountered a ADODB.Properties error:
Query Designer encountered a ADODB.Properties error:
Query Designer encountered a ADODB.Properties error:
ADO could not find the object in the collection corresponding to the
name or ordinal reference requested by the application.(11)Upon clicking OK, a second error message is returned:

Object Variable or With block variable not set.
Resolution
This behavior is by design.

Symptoms
In Data Environment, when a hierarchical recordset is used, and theparent/child/grandchild recordsets are bound, the expected behavior is forall the child/grandchild recordsets to “stay in sync” with the parent.However, when record in the parent table moves, the grandchild recordsetdoes not receive notification that it needs to retrieve the currentchapter. This creates a situation in which the grandchild recordset becomesout of sync.
Resolution
This behavior is by design.

Symptoms
When you use a Report Control that is bound to a Visual Basic Data Environment command object and perform a GroupBy, you receive the following error:

8570 Report sections do not match data source
Resolution
One of the following has occurred:The DataReport is bound to an ungrouped recordset.The DataReport was created before binding the recordset.