Passing sorted ADO.NET dataset to Crystal Reports

[GlynA]

I have a VB.NET DLL which creates an ADO.NET Dataset by executing some SQL which includes an "ORDER BY" clause to ensure data is in the correct sequence. When I look through the Dataset.Tables(0) collection, I can see that the data is indeed sorted correctly. When I use this dataset as the DataSource of a Crystal Report, however, the data is displayed in the incorrect sequence. It appears to be in the same sequence I get the data if I omit the "ORDER BY" clause. The crystal report itself does not have any sorting built in.
What can I do to get the report correctly sorted?

 

[woyler]

Set the sort order from the report itself rather then the dataset

 

[GlynA]

Thanks for the suggestion.
Unfortunately the problem is worse than I thought. The sorted dataset I am specifying in SetDatSource is being entirely ignored and what is displaying is the report using the database which I used to originally defined the report.
If I use an ADO Recordset in SetDataSource, this is correctly shown, but any ADO.NET dataset is ignored!

 

[RiverGuy]

I was gonna say, its probably showing the saved data. I would turn off the save data option for your report anyways.

But, it is very possible to use an ADO.Net dataset as the report source. I've used both DataSets and Recordsets. DataSets were actually a little easier for me to use, as I could only get the Recordsets working if my original source was a .TTX file.

Is it possible that when you change the sql to the Order By statement that something else is changing too? Like an extra select field, or something else that would cause the report not to reflect the same fields and data types that were originally part of the report?

 

[olichap]

Could you paste the code you use to set the datasource?

 

[GlynA]

Thanks for the feedback.

The report was set up using an ODBC connection to the database. What seems to be happening is that the report is always being populated using this connection rather than the data contained in the dataset that I am passing using SetDataSource.

The code used to display the form containing the report is as follows:
2: Dim objForm As New frmRepView()
3: objForm.crReport1.SetDataSource(gobjFile.ReadReport(psSort, pbOrder))
4: objForm.crReport1.PrintOptions.PaperOrientation = piOrientation
6: objForm.Show()

This works fine when gobjFile is an ActiveX DLL passing an ADO recordset, but does not work when an ADO.NET dataset is used.

The code I use to create the dataset is as follows:

Dim sSql As String
Dim adpAdapter As Microsoft.Data.Odbc.OdbcDataAdapter
Dim cmdCommand As Microsoft.Data.Odbc.OdbcCommand
Dim datDataset As DataSet

sSql = "SELECT * FROM " & msTable & " ORDER BY " & psSort
If pbOrder Then
sSql = sSql & " ASC"
Else
sSql = sSql & " DESC"
End If

cmdCommand = mCon.CreateCommand()
cmdCommand.CommandText = sSql
If mbInTrans Then
cmdCommand.Transaction = mTran
End If

adpAdapter = New Microsoft.Data.Odbc.OdbcDataAdapter()
adpAdapter.SelectCommand = cmdCommand

datDataset = New DataSet()
adpAdapter.Fill(datDataset)

Return datDataset

Any further help would be appreciated.

Glyn.

 

[RiverGuy]

Try setting the DataSource within your function, before you return the DataSet. Use the actual name of the DataSet. That's not a solution to your problem, but if it works, then I might have an idea.

 

[GlynA]

I have now got the code to work by changing the called module to return a datatable rather than a database i.e. by changing the line

return datdataset

to

return datdataset.tables(0)

The documentation says that that SetDataSource requires a dataset and does not mention the possibility of using a datatable. Is this documentation incorrect, or am I missing something?

 

[RiverGuy]

I always used dataset. But there could be numerous reasons. Like you said, it was using your original connection source. I used XML schema's for my connection source, which contained no data.

I wonder if it would produce unexpected results if you were using more than one table in the report. If the report itself was designed with more than one table, not a pre-joined query I mean.

 

[RiverGuy]

Also, have you seen this link?

http://support.crystaldecisions.com/communityCS/TechnicalPapers/rtm_reportingoffadonetdatasets.pdf