Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DataGrid problem with lookup fields

Posted on 1999-09-17
4
Medium Priority
?
196 Views
Last Modified: 2013-12-25
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
Comment
Question by:JohnClayson2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
vettranger earned 800 total points
ID: 2054839
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
 

Author Comment

by:JohnClayson2
ID: 2058950
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
 
LVL 8

Expert Comment

by:vettranger
ID: 2059073
Very good, and thanks for the follow up. I try to keep up with exactly what methods solve these DE issues.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6823223
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question