Desperately need VB help please...

[jehnx]

Ok, I am using VB6 (patched up to SP5), and I am using a ADO Datacontroller and a MS Datagrid.

The ADO controler connection string is set to "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & gDatabasePath & ";"

Where gDatabasePath is the database file path variable. (this works fine) The ADO Controller record source is set to "SHAPE {select KitNumber from IWSkitnums} AS ParentCMD APPEND ({select PartName,PartNumber,Qty,DateIssued,DateReceived,KitNumber from IWSkit } AS ChildCMD RELATE KitNumber TO KitNumber) AS ChildCMD"

The MS datagrid (PartsGrid) is then set using this the set command as follows: Set piIWS.PartsGrid.DataSource = piIWS.ADOcontroller.Recordset("ChildCMD").UnderlyingValue

Now all this works fine when I display the form containing these objects but the problem comes when I add data to the Child Database. When I add a new record to the child database and then refresh both the ADO controller and the datagrid the new record show on the fields controlled by the ADO controller but the new record does not show on the Datagrid. Here is where it gets weird, If I add another record the prior record appears in the datagrid. If I exit the form and come back the record appears. But, if I use code to close the window then use the same view code to make it visible again, the record is not thier until I manually exit the window and return.

What I need it to do is, when a record is added to the child database I need to be able to refresh the datagrid so that the new record appears in the datagrid without the user having to exit the window and return or without them having to enter another record. On the database end everything is working and all data appears as it should, it just seems to be the datagrid that is not working properly.

Any ideas?

[V3RIZON]

Try using the DataTables ColumnChanged event to update, should do the trick but will create some extra net traffic.

[jehnx]

K, this is a solution that I am almost sure will help. I am posting this in case any of you others might have a similar problem with VB so this can be of reference or in your memory. It was written for me by a good online friend of mine, hellz:

...databases work on the fact that you select the records that you require, storing them in a recordset for later use.

Now, if you add a record to your database, even if you close a window, and re-open it, it's still going to display the old number of records.

In the case of your example, I think that what you are doing, is selecting the records to be displayed, then opening the window to display them. From this window, a new record can be added, and once added, the new record is not being displayed along with the others.

This is because when you create a recordset, even if you retrieve all of the records in a table, unless the query to select the records from that table, is run for a second time, you are still going to be left with your previous amount of records.

Example:

Dim objRs, strSQL, objConn
Set objRs = Create("ADODB.Recordset")
objRs.Source = "SELECT * FROM some_table"
objRs.ActiveConnection = objConn 'assume this was defined earlier
objRs.Open

'code to display records goes here

Now, let's say that the particular query above, results in 10 records being stored as part of the objRs recordset.

Let's say you now used something like this to insert a record into the table, some_table:

Dim objCommand
Set objCommand = Create("ADODB.Command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "INSERT INTO some_table (first_name) values ('jehnx')"
objCommand.Execute

'we now go back to the original window where the records are to be displayed

The problem now, is that whilst you've added a new record, with a first name of jehnx, there are still 10 records in the objRs recordset. This is because when you run a query, and store the result, the result is never updated. This means that you will need to close your recordset, and re-open it, to select the new records.

When you close the recordset, do not dereference it. This will mean that strSQL will still be used as its source property, so by closing and opening the recordset, you should now have the updated records.

Hope that makes sense.

hellz.

[Simon Templer]

Hello Everyone,

Here is something that I usually do:

I write a sub that will load the DataGrid/ListView/Whatever.

For example:

Public Sub LoadListView()

*The DB query code and ListView loading code*

End Sub

Then whenever I add or remove a record I just call LoadListView() after!

Everything will then display correctly!!

Hope this has helped

Simon Templer

[jehnx]

Thank you very much.