Hi,

I have a disconnected recordset which holds address details for a property.
I have an update command which updates certain property details.  How can I
then refresh my disconnected recordset so it brings back the latest updated
data?

Appreciate the help

Thanks

Damon

¤ Hi,
¤
¤ I have a disconnected recordset which holds address details for a property.
¤ I have an update command which updates certain property details.  How can I
¤ then refresh my disconnected recordset so it brings back the latest updated
¤ data?
¤

Did you reconnect your disconnected Recordset to the database and execute UpdateBatch?


Paul
~~~~
Microsoft MVP (Visual Basic)

I did but no joy.  Here is my code.

This initialises the global disconnected recordset:-

Public Sub local_initialise_records()
On Error GoTo Err_local_initialise_records
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

'Open Connection
If con_open = False Then
    msg_con_failed
Else

    Set cmd = New ADODB.Command
    Set rst = New ADODB.Recordset

    'set up recordsets so that they can be used 'disconnected'
    With rst
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
    End With

    With cmd
        Set .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandText = "proc_rs_main"
    End With

    rst.Open cmd
    'ensure no recordset has an active connection and then close the
connection
    Set cmd.ActiveConnection = Nothing
    Set rst.ActiveConnection = Nothing

    Set global_data_env_main = rst.Clone

End If


Exit_local_initialise_records:
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing
    Exit Sub
Err_local_initialise_records:
    MsgBox Err.Number & " " & Err.description
    Resume Exit_local_initialise_records
End Sub


This is the code which updates the record:-
    cn.begintrans
     With cmd1
                Set .ActiveConnection = cn
                .CommandType = adCmdStoredProc
                .CommandText = "proc_update_cleaning_services_internal"
                .Parameters("@m_ib_id").Value = global_internal_block
                .Parameters("@m_rota_day").Value =
IIf(string_day(opt_m_rota_day) = "", Null, string_day(opt_m_rota_day))
                .Parameters("@m_rota_week").Value =
IIf(string_week(opt_m_rota_week) = "", Null, string_week(opt_m_rota_week))
                .Parameters("@m_cleaning_team").Value =
IIf(cmb_m_cleaning_team.Text = "", Null, cmb_m_cleaning_team.Text)
                .Parameters("@m_block_grading").Value =
IIf(cmb_m_block_grading.Text = "", Null, cmb_m_block_grading.Text)
                .Parameters("@m_week1_date").Value = IIf(lbl_week1.Caption =
"", week1, lbl_week1.Caption)
                .Parameters("@m_eligible_internal").Value =
string_yn(opt_m_eligible_internal)
                .Parameters("@m_scheme_status").Value =
string_yn(opt_m_scheme_status)
                .Parameters("@m_date_withdrawn").Value =
IIf(msk_m_date_withdrawn = "__/__/____", Null, msk_m_date_withdrawn)
                .Execute
            End With
        End If
        cn.comitrans
        MsgBox "Save successful", vbInformation, "SAVE OK"

After the code above I want to be able to refresh the global_data_env_main
recordset with the data which was updated above.

Show quoteHide quote

¤ I did but no joy.  Here is my code.
¤
¤ This initialises the global disconnected recordset:-
¤
¤ Public Sub local_initialise_records()
¤ On Error GoTo Err_local_initialise_records
¤ Dim cmd As ADODB.Command
¤ Dim rst As ADODB.Recordset
¤
¤ 'Open Connection
¤ If con_open = False Then
¤     msg_con_failed
¤ Else
¤
¤     Set cmd = New ADODB.Command
¤     Set rst = New ADODB.Recordset
¤
¤     'set up recordsets so that they can be used 'disconnected'
¤     With rst
¤         .CursorType = adOpenStatic
¤         .CursorLocation = adUseClient
¤         .LockType = adLockBatchOptimistic
¤     End With
¤
¤     With cmd
¤         Set .ActiveConnection = cn
¤         .CommandType = adCmdStoredProc
¤         .CommandText = "proc_rs_main"
¤     End With
¤
¤     rst.Open cmd
¤     'ensure no recordset has an active connection and then close the
¤ connection
¤     Set cmd.ActiveConnection = Nothing
¤     Set rst.ActiveConnection = Nothing
¤
¤     Set global_data_env_main = rst.Clone
¤
¤ End If
¤
¤
¤ Exit_local_initialise_records:
¤     rst.Close
¤     Set rst = Nothing
¤     Set cmd = Nothing
¤     Exit Sub
¤ Err_local_initialise_records:
¤     MsgBox Err.Number & " " & Err.description
¤     Resume Exit_local_initialise_records
¤ End Sub
¤
¤
¤ This is the code which updates the record:-
¤     cn.begintrans
¤      With cmd1
¤                 Set .ActiveConnection = cn
¤                 .CommandType = adCmdStoredProc
¤                 .CommandText = "proc_update_cleaning_services_internal"
¤                 .Parameters("@m_ib_id").Value = global_internal_block
¤                 .Parameters("@m_rota_day").Value =
¤ IIf(string_day(opt_m_rota_day) = "", Null, string_day(opt_m_rota_day))
¤                 .Parameters("@m_rota_week").Value =
¤ IIf(string_week(opt_m_rota_week) = "", Null, string_week(opt_m_rota_week))
¤                 .Parameters("@m_cleaning_team").Value =
¤ IIf(cmb_m_cleaning_team.Text = "", Null, cmb_m_cleaning_team.Text)
¤                 .Parameters("@m_block_grading").Value =
¤ IIf(cmb_m_block_grading.Text = "", Null, cmb_m_block_grading.Text)
¤                 .Parameters("@m_week1_date").Value = IIf(lbl_week1.Caption =
¤ "", week1, lbl_week1.Caption)
¤                 .Parameters("@m_eligible_internal").Value =
¤ string_yn(opt_m_eligible_internal)
¤                 .Parameters("@m_scheme_status").Value =
¤ string_yn(opt_m_scheme_status)
¤                 .Parameters("@m_date_withdrawn").Value =
¤ IIf(msk_m_date_withdrawn = "__/__/____", Null, msk_m_date_withdrawn)
¤                 .Execute
¤             End With
¤         End If
¤         cn.comitrans
¤         MsgBox "Save successful", vbInformation, "SAVE OK"
¤
¤ After the code above I want to be able to refresh the global_data_env_main
¤ recordset with the data which was updated above.

OK, so it looks like you're updating with a Command object. Have you tried the Requery method of the
Recordset? It has to be connected of course?


Paul
~~~~
Microsoft MVP (Visual Basic)

Any known problems running VB6 application with Intel Centrino Duo
question -- close adodb connection inside dll
Hidden treasures...
CommonDialog sets Windows Default Printer - is Mike Williams out there?
vb6 and iso 8601 date format
VBnet 2005?
click on a menu
error making function call with multiple parameters to dll
Error creating Oracle object
WebBrowser vs Pdf.ocx