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

DataGrid problem with lookup fields

I am having problems refreshing the contents of a bound data grid when I add a new row.  The problem only lies in fields which are from “lookup” tables.

I am using a datagrid (datagrid control 6.0 (OLEDB)) in VB6 linked to a command object of a DataEnvironment (DE) (which linked to a SQL Server 7 database).

I am using ADO (2.1) to access the data in the database

There are several fields in the main table in the database which store numeric values which link to fields in lookup tables which have text descriptions in them.

I set up a command object under the DE which shows the numeric fields from the main database table and the equivalent text descriptions from the lookups.

I drop the datagrid on the form and set the datasource and datamember at design time – when I load the form everything is shows OK in the datagrid.  I then have a command button which loads a data input form to add a new record to the grid.  Having added the data I do a recordset.update and return to the form with the datagrid on it – all fields have updated OK except those which show the values from the lookup tables.

If I close down the app and then run it again, the datagrid shows all the new values including the lookup fields

Any ideas how to solve this ?
0
JohnClayson2
Asked:
JohnClayson2
  • 2
1 Solution
 
vettrangerCommented:
Yes, you're going to have to rebind the Data Environment in between ... here's an article that has a bunch of good information on this subject for you :

The URL for the following is :
 http://support.microsoft.com/support/kb/articles/q191/4/57.asp

If you have controls bound to a query built by the DataEnvironment and then call the underlying recordset's Requery method, your bound controls will still display data from the recordset prior to having called the Requery method. In short, you will still see your old data.

You may also see an error message stating:


"Error while trying to set current row."



RESOLUTION

After you have re-queried the recordset, you must re-bind it.

-or-


Bind your controls to the recordset object rather than to the DataEnvironment. If you then call the Requery method on the recordset object, you see the up-to-date data in your bound controls.





STATUS
This behavior is by design.



MORE INFORMATION

Steps to Reproduce Behavior
Start a new Standard EXE project in Visual Basic. Form1 is created by default.


Add a DataEnvironment to your project.


Rename the connection "cnNWind" and use its property pages to use the Microsoft Jet OLE DB Provider to connect to the Northwind database (NWind.MDB) on your hard drive.


Add a command to the cnNWind connection. Rename the command "Customers," and use the SQL statement "SELECT CustomerID, City FROM Customers."


Drag-and-drop the CustomerID and City fields from the DataEnvironment onto your form.


Drag the Customers command from the DataEnvironment onto your form while holding down the right-mouse button. Select "Data Grid" from the available choices in the menu that appears when you release the right- mouse button.


Add two CommandButtons to your form. Name them cmdModifyData and cmdRequery.


Add the following code to your form:
      Private Sub Form_Load()
          cmdModifyData.Caption = "Modify Data"
          cmdRequery.Caption = "Requery"
      End Sub

      Private Sub ReBindMyControls()
          Set txtCustomerID.DataSource = DataEnvironment1
          Set txtCity.DataSource = DataEnvironment1
          Set DataGrid1.DataSource = DataEnvironment1
      End Sub

      Private Sub cmdModifyData_Click()
          Dim strSQL As String
          Dim strNewCity As String
          Dim intRecordsAffected As Integer

          strNewCity = InputBox("Enter a new value for the city")
          If strNewCity <> "" Then
              strSQL = "UPDATE Customers " & _
                       "SET City = '" & strNewCity & "' " & _
                       "WHERE CustomerID = '" & _
                       DataEnvironment1.rsCustomers!CustomerID & "'"
                DataEnvironment1.cnNWind.Execute strSQL, _
                                     IntRecordsAffected, adExecuteNoRecords
              MsgBox intRecordsAffected & " record(s) affected"
          Else
              MsgBox "No update performed"
          End If
      End Sub

      Private Sub cmdRequery_Click()
          DataEnvironment1.rsCustomers.Requery
          'ReBindMyControls
          MsgBox "Recordset reopened" & vbCrLf & _
                 "Current customer's city: " & _
                 DataEnvironment1.rsCustomers!City
      End Sub
   



Run the project.


Click on the button marked "Modify Data" to enter a new value for the current customer's city. The code will modify that row in the database without changing the row in the recordset.


Click on the button marked "Requery" to re-run the query. You will see a message box that says that the recordset has been re-opened and displays the value for the current customer's city in the recordset object. You will see the value that you entered in step 9 in this message box, but the textbox will still display the old value and the data in the grid will remain the same.


Exit the project by clicking on the "X" button in the upper-right corner of the form.


Modify the cmdRequery_Click event and un-comment the following line:
      ReBindMyControls
   



Re-run the project.


Click on the button marked "Modify Data" to enter a new value for the current customer's city. The code will modify that row in the database without changing the row in the recordset.


Click on the button marked "Requery" to re-run the query. You will see a message box that says that the recordset has been re-opened and displays the value for the current customer's city in the recordset object. You will see the value that you entered in Step 9 in this message box. The grid and textbox will now display the new value.

NOTE: You could also bind your controls to the recordset itself rather than to the DataEnvironment by placing the following code in the Form_Load event. If you use this method, you do not need to re-bind your controls:
      txtCustomerID.DataMember = "" 
      Set txtCustomerID.DataSource = DataEnvironment1.rsCustomers
      txtCity.DataMember = "" 
      Set txtCity.DataSource = DataEnvironment1.rsCustomers
      DataGrid1.DataMember = "" 
      Set DataGrid1.DataSource = DataEnvironment1.rsCustomers
0
 
JohnClayson2Author Commented:
The thing that solved the problem was the setting of the data source for the grid to the command's recordset i.e

mydatagrid.datamember=""
mydatagrid.datasource=myDE.rsmycmd

When I later do a rsmycmd.requery then everything updates OK in the grid.
0
 
vettrangerCommented:
Very good, and thanks for the follow up. I try to keep up with exactly what methods solve these DE issues.
0
 
MoondancerCommented:
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.

This is the Community Support link, if help is needed, along with the link to All Topics since many new ones were recently added.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thanks,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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