How do you refresh a data report?

[Guest_imported]

Hello,
I have a VB/SQL Server application set up. I'm using PL/SQL for all of my database access issues, but I've used the Data Environment to do some simple reports. However, when I do an update to a record and then 'reopen' the report, the data in the record has not changed from the previous update. It's only if I close and restart the application do the changes in the report show up. How do I refresh the connection or command object in order for the report to show the most recent updates or inserts every time I close and reopen the report? I've tried the .refresh method for the command and for the report and neither seem to work. Any suggestions would be most appreciated. Thanks in advance.
-johnnyN

 

[TheVampire]

I, and many others have run into this problem. Here is one way to solve it. I didn't gome up with this, it was e-mailed to me by Amol Pargaonkar (

http://www.zestorm.com)

when I asked the same question on a different forum.


Well what you are facing is a problem with the Data Environment,the thing is that the "Initialize" event of the DE must be called everytime you wish to see the report,but this does not happen so we need to programatically call it.I had faced the same problem,how ever what i did was that i eliminated the DE . Here is the code that i used,try this and i guess that your problem will be solved,mine was solved 100 %.
Now the code calls the "initialize" event explicitly and this solved the problem

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command


cn.ConnectionString = " - - - - - " ' provide the connection string here

cn.Open

With cmd
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = " YOUR QUERY GOES HERE"
.Execute
End With

With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.Open cmd
.Requery
End With


Dim q As Integer
Dim intCtrl As Integer
Dim x As Integer
Dim z As Integer
x = 0
q = 0
z = 0

With DR 'NAME OF MY DATA REPORT
.Hide
Set .DataSource = rs
.DataMember = ""

'ASSIGN THE FIELDS THERE CORR VALUES AT RUN TIME,HOWEVER U SHOULD DESIGN THE DATA REPORT AT DESIGN TIME ONLY,THE WAY WE DO NORMALLY AND ALSO ADD THE LABELS AND TEXTBOXES,BUT KEEP THEM UNBOUND,THE CODE WILL BIND THEM.

With .Sections("Section1&quot.Controls
For intCtrl = 1 To .count
If TypeOf .Item(intCtrl) Is RptLabel Then
.Item(intCtrl).Caption = rs.Fields(q).Name & " :"
q = q + 1
End If

If TypeOf .Item(intCtrl) Is RptTextBox Then
.Item(intCtrl).DataMember = ""
.Item(intCtrl).DataField = rs(z).Name
z = z + 1
End If
Next intCtrl
End With
.Refresh
.Show
End With

rs.Close
cmd.Cancel
cn.Close


And that was it. I had to modify the code a little to get it to work in my specific instance ( cause I was using a table and not SQL ), but this should get you on the right track.

 

[bjd4jc]

There is a much more simplistic approach to this if you still want to use the data environment. But first, you will need to know how the data environment works. Microsoft did a poor job of documenting it but I have figured a few things out on my own.

The first thing I do before opening the report is the execute the command.

Example:
deMyEnvironment.MyCommand

This creates a recordset automatically. In this case the recordset would be called rsMyCommand. This is a normal ADO recordset that you can do whatever you want with.

After I execute the command, I show or print the report.

Example:
rptMyReport.show vbModal
--OR--
rptMyReport.PrintReport

Then I unload the report:
unload rptMyReport

Then, this is what will refresh the data next time you go to open it. You close the recordset that was created when you executed the command.

Example:
rsMyCommand.Close
set rsMyCommand=Nothing

This should refresh the report everytime you open it.

 

[bjd4jc]

There is a correction here. The recordset is created under the data environment. So, in order to close it you would have to do:

deMyEnvironment.rsMyCommand.Close
Set deMyEnvironment.rsMyCommand = Nothing

Sorry about that.

I also forgot to state one of the points I wanted to make. Closing or unloading the report DOES NOT automatically close the command used to create the report, but opening the report automatically opens the commands and recordsets needed to create the report.

 

[Guest_imported]

Follow up question to the last two responses. Where would the unload and close report and recordset code go? Thanks.

 

[goatsaregreat]

I had that prob. too, and I used, DE.requery. Seemed to work for me ok. l8r

 

[bjd4jc]

After you show or print your report.

If you show your report non-modal than you may have problems with this. You can put the code in the Terminate event of your report.

 

[floweriang]

I kind of agree with goatsaregreat. I put a code in the terminate event.

Private Sub DataReport_Terminate()

DataEnvironment1.rsTesting.Close

End Sub

 

[iqbal21]

Just try this out

If deDailyRpt.rscmdDailyHearing.State = 1 Then
deDailyRpt.rscmdDailyHearing.Requery
Else
deDailyRpt.cmdDailyHearing
End If

DailyRptFilterHearing.Show

 

[Guest_imported]

This last bit from iqbal21 worked like a charm! Thanks much.
-JN

 

[Montoya]

hmmm. I don't know what to say ...
Sunteti niste prosti

 

[Guest_imported]

Thanks for the information, I had the same problem.

 

[tedsmith]

Brilliant, I have been struggling with the Dataenvironment and given it up as a bad job. I will now go and revisit it.
I read another suggestion that all you have to do is to restate the connection statement.
Also changing a parameter seems to automatically refresh everything including any bound datagrids.

The main thing I am having trouble is with refreshing aggregate totals of a column that consists of the sums of a lot of other sub items. This aggregate is shown in a bound text box BUT when you change a value in the original values, or change the parameter value, the datagrid sum field changes but the aggregate of all the sums in the text box does not change. Textbox.refresh doesnt do anything either. The reAggregateTotalRecordset DOES change OK.
I must have the parameter and sums SQL right because it works OK when I start the application but not when I make a change.
Seems to be something in the binding to the text box.

 

[bjd4jc]

Are you closing your report or just doing a requery on it?

 

[bubarooni]

.

 

[tedsmith]

I am not using a report. I am using a screen form