首選在水晶報表中把畫面拉好
再在SQL中添加一張表
設定SQL命令和FIELD


#Region "報表"
Private Sub Print()
Dim strRptID As String = "" '報表代碼
strRptID = ""
Dim strRptTab As String = "" '列印資料的table名稱
Dim strSqlUpd As String = "" '在列印後欲UPDATE資料之Script
Dim strSqlDesc As String = "" '產生列印資料存儲table之Script
Dim strSqlIns As String = "" '報表要列印資料先存入table之Script
Dim arrCaption As New ArrayList
Dim _objODComm As New OdComm
Dim _OdRpt As OdRpt = New OdRpt
'報表中所需的標頭CaptionLabel
If Me.ProgID.Trim = "OD1010040" Then
arrCaption.Add("lbl0292") '總收文
Else
arrCaption.Add("lbl0363") '單位收文
End If
arrCaption.Add("lbl0090") '報表編號：
arrCaption.Add("lbl0321") '收文文號：
arrCaption.Add("lbl0388") '來文字號：
arrCaption.Add("lbl0100") '來文機關(者)：
arrCaption.Add("lbl0117") '承辦單位：
arrCaption.Add("lbl0080") '主旨：
arrCaption.Add("lbl0001") '收文日期：
arrCaption.Add("lbl0004") '來文日期：
arrCaption.Add("lbl0395") '來文類別：
arrCaption.Add("lbl0096") '頁　　次：
arrCaption.Add("lbl0097") '列印日期：
arrCaption.Add("lbl0098") '列印人員
arrCaption.Add("grid0001") '序號
arrCaption.Add("grid0099") '收文文號
arrCaption.Add("grid0017") '收文日期
arrCaption.Add("grid0006") '來文字號
arrCaption.Add("grid0071") '來文機關
arrCaption.Add("grid0005") '來文日期
arrCaption.Add("grid0025") '承辦單位
arrCaption.Add("grid0026") '承辦人員
arrCaption.Add("grid0004") '主旨

'************此處選擇列印報表，並進行資料處理
Dim strUsrName As String = _objODComm.GetUsrName(Me.CompID, Me.UsrID)

strRptID = "RPT1010040.RPT"
strRptTab = "ODR1010040"
strSqlIns = GetIns_RPT()

'比對CaptionLebel
_OdRpt.UpdateResource(strRptID, arrCaption, Me.CompID)

'************開始列印資料處理動作
_OdRpt.CreateRptTab(strUsrName, strRptTab, strSqlDesc, strSqlIns, CompID())
_OdRpt.InsertInotData(strUsrName, strRptID, strRptTab, strSqlUpd, CompID())

'***********開始列印動作
Dim strScript As String = ""
strScript += "<script>FnOpenRpt('" + strUsrName + "','" + strRptID + "');</script>"
Page.ClientScript.RegisterStartupScript(Me.GetType(), "strScript", strScript)
End Sub
#End Region
Private Function GetIns_RPT() As String
Dim strIns As New System.Text.StringBuilder
Dim _objODComm As New OdComm
Dim strUsrName As String = _objODComm.GetUsrName(Me.CompID, Me.UsrID)
strIns.Append(" INSERT INTO ODR1010040(CNO_CODE,CNO_DT,SOURCE_SCNO,SOURCE_NAME " & vbCrLf)
strIns.Append(" ,SOURCE_DATE,HDL_DEPT,HDL_USR,CNO_DESC " & vbCrLf)

strIns.Append(" ,CNO_CODE_H,CNO_DT_H,SOURCE_SCNO_H,SOURCE_DATE_H " & vbCrLf)
strIns.Append(" ,SOURCE_NAME_H,HDL_DEPT_H,SOURCE_CLASS_H,CNO_DESC_H,MON_USR,NOW_DATE_H,USR_ID) " & vbCrLf)
strIns.Append(" SELECT CNO_CODE,CNO_DT,SOURCE_SCNO,SOURCE_NAME " & vbCrLf)
strIns.Append(" ,SOURCE_DATE,HDL_DEPT,HDL_USR,CNO_DESC" & vbCrLf)

strIns.Append(" ,'" & ViewState("str1") & "' AS CNO_CODE_H" & vbCrLf)
strIns.Append(" ,'" & ViewState("str2") & "' AS CNO_DT_H" & vbCrLf)
strIns.Append(" ,'" & ViewState("str5") & "' AS SOURCE_SCNO_H" & vbCrLf)
strIns.Append(" ,'" & ViewState("str3") & "' AS SOURCE_DATE_H" & vbCrLf)
strIns.Append(" ,'" & ViewState("str4") & "' AS SOURCE_NAME_H" & vbCrLf)
strIns.Append(" ,'" & ViewState("str7") & "' AS HDL_DEPT_H" & vbCrLf)
strIns.Append(" ,'" & ViewState("str8") & "' AS SOURCE_CLASS_H" & vbCrLf)
strIns.Append(" ,'" & ViewState("str6") & "' AS CNO_DESC_H" & vbCrLf)

'strIns.Append(" ,CNO_CODE,CNO_DT,SOURCE_SCNO,SOURCE_DATE" & vbCrLf)
'strIns.Append(" ,SOURCE_NAME,HDL_DEPT,STY_STAND,CNO_DESC" & vbCrLf)

strIns.Append(" ,'" & Session("USR_ID") & "' AS MON_USR, GETDATE() AS NOW_DATE_H,'" & strUsrName & "' AS USR_ID" & vbCrLf)
strIns.Append(" FROM ODMDOC" & vbCrLf)
strIns.Append(" WHERE '1' = '2' " & vbCrLf)
'將hicCOMP_ID欄位的值劈開，hicCOMP_ID中是ODMDOC兩主鍵COMP_ID　及　CNO_CODE的組合，形式AAA##BBB@@
Dim array As Array = Me.hicCOMP_ID.Value.Split("@@")
Dim i As Integer
Dim strComp_id As String
Dim strCNO_CODE As String
For i = 0 To array.Length - 2
Dim array2 = array(i).ToString.Split("##")
strComp_id = array2(2)
strCNO_CODE = array2(0)
strIns.Append(" OR COMP_ID ='" & strComp_id & "'" & vbCrLf)
strIns.Append(" AND CNO_CODE = '" & strCNO_CODE & "'" & vbCrLf)

i = i + 1
Next
Return strIns.ToString

End Function





''' -----------------------------------------------------------------------------
''' <summary>
''' 函數名稱: UpdateResource()
''' 目 的: 更新ODRCaption檔案
''' </summary>
''' <param name="RptID"> 報表代碼</param>
''' <param name="arrCaption"> CaptionLebal</param>
''' <history>
''' xx. YYYY/MM/DD VER AUTHOR COMMENTS
''' 1. 2006/09/18 1.00 Sandy Create
''' </history>
''' -----------------------------------------------------------------------------
Public Sub UpdateResource(ByVal RptID As String, ByVal arrCaption As ArrayList, ByVal CompanyID As String)
Dim strSQL As String = ""
Dim DT As DataTable

strSQL += " SELECT Caption FROM ODRCaption WHERE RPT_Resource='" & RptID.Replace(".RPT", "") & "'"

Try
DT = mdOdDBCmd.OpenDataTable(strSQL.ToString, enuDBConnectType.ODConnType, , CompanyID)

If DT.Rows.Count <> arrCaption.Count Then
Dim sbSQL As New StringBuilder
Dim strVal As String
sbSQL.Append("DELETE FROM ODRCaption WHERE RPT_Resource='" + RptID.Replace(".RPT", "") + "'; ")

For i As Integer = 0 To arrCaption.Count - 1
strVal = arrCaption.Item(i).ToString
sbSQL.Append("INSERT INTO ODRCaption (RPT_Resource, Caption_Index, Caption) VALUES(")
sbSQL.Append("'" & RptID.Replace(".RPT", "") & "'," & (i + 1) & ",'" & strVal & "'); ")
Next

mdOdDBCmd.ExecSQL(sbSQL.ToString, enuDBConnectType.ODConnType, , CompanyID)

End If
Catch ex As Exception
WriteErrorLog(ex, " RptID=" & RptID & ", CompanyID=" & CompanyID)
Throw
End Try

End Sub





''' -----------------------------------------------------------------------------
''' <summary>
''' 函數名稱: CreateRptTab()
''' 目 的: 在列印前處理列印資料，如果資料存在，則刪除其中該USER之資料

''' </summary>
''' <param name="UserID"> 使用者代碼</param>
''' <param name="RptTab"> 儲存列印資料暫存table名稱</param>
''' <param name="SqlDes"> 產生列印資料儲存table之Script</param>
''' <param name="SqlIns"> 報表要列印資料先存入table之Script</param>
''' <returns>執行成功返回true，否則返回false</returns>
''' <remarks>
''' </remarks>
''' <history>
''' xx. YYYY/MM/DD VER AUTHOR COMMENTS
''' 1. 2006/04/20 1.00 Michael Create
''' 2. 2006/04/27 2.00 Fiona Modify
''' 3. 2006/08/22 3.00 Sandy Modify
''' </history>
''' -----------------------------------------------------------------------------
Public Function CreateRptTab(ByVal UserID As String, ByVal RptTab As String, ByVal SqlDes As String, ByVal SqlIns As String, ByVal CompanyID As String) As Boolean
Dim sbSQL As New System.Text.StringBuilder
Dim ReValue As Boolean = False

sbSQL.Append(" IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[" + RptTab + "]') AND OBJECTPROPERTY(id, N'IsUserTable')=1)" + Chr(13) + Chr(10))
sbSQL.Append(" DELETE FROM [" + RptTab + "] WHERE USR_ID=@pUserID " + Chr(13) + Chr(10))

Try
Dim vaobjDataParams() As DataParameter
vaobjDataParams = New DataParameter() {New DataParameter("@pUserID", UserID)}
mdOdDBCmd.ExecSQL(sbSQL.ToString, enuDBConnectType.ODConnType, vaobjDataParams, CompanyID )

If SqlIns.Trim <> "" Then
mdOdDBCmd.ExecSQL(SqlIns.ToString, enuDBConnectType.ODConnType, , CompanyID)
End If

ReValue = True
Catch ex As Exception
WriteErrorLog(ex, " UserID=" & UserID & ", RptTab=" & RptTab & ", SqlDes=" & SqlDes & ", SqlIns=" & SqlIns & ", CompanyID=" & CompanyID)
Throw
End Try

Return ReValue
End Function





''' -----------------------------------------------------------------------------
''' <summary>
''' 函數名稱: InsertInotData()
''' 目 的: 在列印前處理需要的資料，先刪除以前的舊資料，然後加入新資料
''' </summary>
''' <param name="UserID"> 使用者代碼</param>
''' <param name="RptID"> 報表代碼</param>
''' <param name="RptTab"> 產出的實體檔案名稱</param>
''' <param name="CompanyID"> 公司名稱</param>
''' <param name="SqlDesc"> 在列印後欲UPDATE資料之Script</param>
''' <returns>執行成功返回true，否則返回false</returns>
''' <remarks>
''' </remarks>
''' <history>
''' xx. YYYY/MM/DD VER AUTHOR COMMENTS
''' 1. 2006/04/20 1.00 Michael Create
''' 2. 2006/04/27 2.00 Fiona Modify
''' 3. 2006/08/22 3.00 Sandy Modify
''' </history>
''' -----------------------------------------------------------------------------
Public Function InsertInotData(ByVal UserID As String, ByVal RptID As String, ByVal RptTab As String, ByVal SqlDesc As String, ByVal CompanyID As String) As Boolean
Dim strSQL As String = ""
Dim ReValue As Boolean = False
Try
strSQL += " DELETE FROM dbo.ODMRPTPREVIEW " + Chr(13) + Chr(10)
strSQL += " WHERE RPT_ID=@pRptID AND USR_ID_IDENT=@pUserID" + Chr(13) + Chr(10)

strSQL += " INSERT INTO dbo.ODMRPTPREVIEW (COMP_ID, RPT_ID, USR_ID_IDENT, RPT_TAB_NAME, SQL_DESC, CRT_DT) " + Chr(13) + Chr(10)
strSQL += " VALUES( @pCOMP_ID" + Chr(13) + Chr(10)
strSQL += " , @pRptID" + Chr(13) + Chr(10)
strSQL += " , @pUserID" + Chr(13) + Chr(10)
strSQL += " , @pRptTab" + Chr(13) + Chr(10)
strSQL += " , @pSqlDesc" + Chr(13) + Chr(10)
strSQL += " , GETDATE() " + Chr(13) + Chr(10)
strSQL += " ) "

Dim vaobjDataParams() As DataParameter
vaobjDataParams = New DataParameter() {New DataParameter("@pRptID", RptID), New DataParameter("@pUserID", UserID), New DataParameter("@pCOMP_ID", CompanyID), New DataParameter("@pRptTab", RptTab), New DataParameter("@pSqlDesc", SqlDesc.Trim())}
mdOdDBCmd.ExecSQL(strSQL, enuDBConnectType.ODConnType, vaobjDataParams, CompanyID)

ReValue = True

Catch ex As Exception
WriteErrorLog(ex, " UserID=" & UserID & ", RptID=" & RptID & ", RptTab=" & RptTab & ", SqlDesc=" & SqlDesc & ", CompanyID=" & CompanyID)
Throw
End Try

Return ReValue
End Function

很好,谢谢