VB中使用水晶报表，怎么记录会重复出现？

[frogdan]

一、环境：
1. 数据库：有两个表，Order和OrderDetail，一条Order记录对应若干条OrderDetail记录；
2. 报表：以Order的ID作为Group，把OrderDetail的一些字段放在Detail的Section中；
3. VB：一个Designer，一个带有CRViewer的窗体，代码如下

Dim Report As New CrystalReport1
Dim oConn As New ADODB.Connection
Dim oRecordset As New ADODB.Recordset

Private Sub Form_Load()
Dim sql As String

oConn.ConnectionString = "DRIVER=SQL SERVER;SERVER=xxx;UID=sa;PWD=;DATABASE=abc"
oConn.Open

sql = "SELECT * FROM Order, OrderDetail " & _
"WHERE Order.ID = OrderDetail.OrderID AND " & _
"Order.ID = '2004050401'"

oRecordset.ActiveConnection = oConn
oRecordset.Open sql

Report.Database.SetDataSource oRecordset

Screen.MousePointer = vbHourglass
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
Screen.MousePointer = vbDefault
End Sub

二、目的：想在程序中动态指定显示某张订单的内容。

三、现象：
1. 如果不用“Report.Database.SetDataSource oRecordset”这句的话，就显示全部订单的内容，是正确的；
2. 但用了上面那句企图限定某张订单的话，OrderDetail会重复出现！例如：

OrderID: 2004050401
ProductID: 001 ProductName: abc
ProductID: 002 ProductName: def
...
ProductID: 001 ProductName: abc
ProductID: 002 ProductName: def
...
ProductID: 001 ProductName: abc
ProductID: 002 ProductName: def

有多少条OrderDetail就重复多少次，真是 faint 啊！

请高手指教！

[email0755]

----------------------------
试试下面的方法,测试通过:
----------------------------
Private Sub Form_Load()

Dim CryApp As New CRAXDDRT.Application
Dim Report As CRAXDDRT.Report

Dim strCnn As String
Set Report = CryApp.OpenReport(App.Path & "\test.rpt")

Dim cnn1 As New ADODB.Connection
Dim adoRS As New ADODB.Recordset

strCnn = "Provider=sqloledb;Data Source=xxx;Initial Catalog=abc;User Id=sa;Password=;"
cnn1.Open strCnn
ID = InputBox("ID")
adoRS.CursorLocation = adUseClient
adoRS.Open "select Order1.*,OrderDetail.* from order1 left join orderdetail on order1.orderid=orderdetail.orderid where order1.orderid='" & ID & "'", cnn1, adOpenDynamic, adLockBatchOptimistic

Report.DiscardSavedData
Report.Database.SetDataSource adoRS

Screen.MousePointer = vbHourglass
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
Screen.MousePointer = vbDefault

End Sub

[frogdan]

哈，问题解决了。

原因在于 SQL 语句：
SELECT * FROM Order, OrderDetail WHERE Order.ID = OrderDetail.OrderID AND Order.ID = '2004050401'

原来在设计水晶报表的时候，已经把这些数据关系做在报表中了，我们的 SQL 语句只需写成：
SELECT * FROM Order WHERE Order.ID = '2004050401'

写多了反而误事，害我这个菜鸟烦恼了好几天。

在此感谢 email0755 的帮助！