Results 1 to 5 of 5

Thread: Desperately need VB help please...

  1. #1
    Senior Member
    Join Date
    Dec 2001

    Desperately need VB help please...

    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?

  2. #2
    Join Date
    Oct 2001
    Try using the DataTables ColumnChanged event to update, should do the trick but will create some extra net traffic.

  3. #3
    Senior Member
    Join Date
    Dec 2001
    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.


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

    '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')"

    '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.


  4. #4
    Senior Member
    Join Date
    Oct 2001


    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
    Simon Templer

    \"Your work is to discover your world and then with all your heart give yourself to it. \"
    -The Buddha

  5. #5
    Senior Member
    Join Date
    Dec 2001
    Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts