When developing ASP websites, sometimes the data of the MSSQL database needs to be printed on the client. If the amount of call data is small, you can print it by using FileSystemObject to generate file objects on the client. I will not repeat it here. If you need to call a large amount of data, you can instantiate the RDS.DataSpace (Remote Data Service) object in the client script, and use a remote provider to access the MSSQL database through the ASP website (set to only be through RDS Default Handler or custom commercial objects. Accessing the database can ensure the security of the database), instantiate the EXCEL.APPLICATION object on the client, write the data in the dataset into the EXCEL, and then save or print. The code is as follows:
| ＜html＞ ＜head＞ ＜META content=text/html; charset=gb2312 http-equiv=Content-Type＞ ＜title＞Client spreadsheet printing＜/title＞ ＜/head＞ ＜body bgColor=skyblue topMargin=5 leftMargin=20 oncontextmenu=return false rightMargin=0 bottomMargin=0＞ ＜div align=center＞＜center＞ ＜table border=1 bgcolor=#ffe4b5 style=HEIGHT: 1px; TOP: 0px bordercolor=#0000ff> ＜tr＞ ＜td align=middle bgcolor=#ffffff bordercolor=#000080＞ ＜font color=#000080 size=3＞ Client spreadsheet printing ＜/font＞ ＜/td＞ ＜/tr＞ ＜/table＞ ＜/div＞ ＜form name=myform＞ ＜DIV align=left＞ ＜input type=button value=Excel Report name=report language=vbscript onclick=fun_excel() style=HEIGHT: 32px; WIDTH: 90px> ＜/div＞ ＜/form＞ ＜/body＞ ＜/html＞ ＜script language=vbscript＞ sub fun_excel() Dim rds,rs,df dim strCn, strSQL, StrRs Dim xlApp, xlBook, xlSheet1 set rds = CreateObject(RDS.DataSpace) Set df = rds.CreateObject(RDSServer.DataFactory, http://192.168.0.1) '192.168.0.1 is the WEB server IP address strcn=provider=ms remote;remote server=http://192.168.0.1;handler=msdfmap.handler;data source=pubsdatabase; '192.168.0.1 is the IP address of the WEB server strsql= getalljobs Set rs = df.Query(strCn, strSQL) Set xlApp = CreateObject(EXCEL.APPLICATION) 'Note not: Server.CreateObject(EXCEL.APPLICATION) Set xlBook = xlApp.Workbooks.Add Set xlSheet1 = xlBook.Worksheets(1) xlSheet1.cells(1,1).value = Job table xlSheet1.range(A1:D1).merge xlSheet1.cells(2,1).value = job_id xlSheet1.cells(2,2).value = job_desc xlSheet1.cells(2,3).value = max_lvl xlSheet1.cells(2,4).value = min_lvl cnt =3 do while not rs.eof xlSheet1.cells(cnt,1).value = rs(job_id) xlSheet1.cells(cnt,2).value = rs(job_desc) xlSheet1.cells(cnt,3).value = rs(max_lvl) xlSheet1.cells(cnt,4).value = rs(min_lvl) rs.movenext cnt = cint(cnt) + 1 loop xlSheet1.Application.Visible = True end sub ＜/script＞ |
You can also instantiate RDS DataControl, just modify the above code:
| set rds = CreateObject(RDS.DataSpace) Set df = rds.CreateObject(RDSServer.DataFactory, http://192.168.0.1) '192.168.0.1 is the WEB server IP address strcn=provider=ms remote;remote server=http://192.168.0.1;handler=msdfmap.handler;data source=pubsdatabase; '192.168.0.1 is the WEB server IP address strsql= getalljobs Set rs = df.Query(strCn, strSQL) |
Modified to:
| set DC = createobject(RDS.DataControl) dc.ExecuteOptions =1 'Set to execute synchronously, which can simplify the next code dc.FetchOptions = 1 With dc .Server = http://192.168.0.1 .Handler = MSDFMAP.Handler .Connect = Data Source=pubsdatabase; .Sql = getalljobs .Refresh End With set rs= dc.Recordset |
Modify the file MSDFMAP.INI (if in WIN98, C: /windows/msdfmap.ini; if in WIN2000, D: /winnt/msdfmap.ini; if in WIN2000 SERVER, D: /winnts/msdfmap.ini).
| [sql getalljobs] Sql=SELECT * FROM jobs [connect pubsDatabase] Access=Readonly Connect=provider=sqloledb;data source=sql server;initial catalog=pubs;UID=userid;PWD=password |
Open the registry HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/ Services/W3SVC/Parameters/ADCLaunch If there is no RDSServer.Datafactory, please add it. This example uses RDS Default Handler to access the database. If the database is not accessed through RDS Handler, modify the registry HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/DataFactory/HandlerInfo Set HandlerRequired=1 to HandlerRequired=0. Please note that if the database is not accessed through RDS Handler or custom commercial objects, it will pose a security risk to the database. Therefore, the author highly recommends that the database can only be accessed through RDS Handler or custom commercial objects.
The following is a custom business object in VB, with the following code:
| 'Writing ActiveX DLL, name: rsget.dll, containing class rsreturn, method return rs Public Function ReturnRs (strDB As Variant, strSQL As Variant) As ADODB.Recordset 'Returns an ADODB recordset. On Error GoTo ehGetRecordset Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Select Case strDB Case ydjjspdatabase strDB = ydjjsp Case pubsdatabase strDB = pubs End Select If strSQL = getallbuy Then strSQL = select * from buyuser GoTo nextstep End If If Left(strSQL, InStr(strSQL, () - 1) = getpubsbyid Then If InStr(strSQL, ,) ＜= 0 Then Dim str As String str = Mid(strSQL, InStr(strSQL,() + 2, InStr(strSQL, )) - InStr(strSQL,() - 3) strSQL = select * from jobs where job_id=' & str & ' Else Dim strstart, strend As String strstart = Mid(strSQL, InStr(strSQL,() + 2, InStr(strSQL, ,) - InStr(strSQL,() - 3) strend = Mid(strSQL, InStr(strSQL, ,) + 2, InStr(strSQL, )) - InStr(strSQL, ,) - 3) strSQL = select * from jobs where job_id>=' & strstart & ' and job_id<=' & strend & ' End If End If nextstep: Dim strConnect As String strConnect = Provider=SQLOLEDB;Server=ddk;uid=ydj;pwd=ydj; Database= & strDB & ; cn.Open strConnect rs.CursorLocation = adUseClient rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText Set ReturnRs = rs Exit Function ehGetRecordset: Err.Raise Err.Number, Err.Source, Err.Description End Function |
Copy rsget.dll to C:/WINDOWS or D:/WINNT, start/run, enter Regsvr32.exe c:/windows/rsget.dll or Regsvr32.exe d:/winnt/rsget.dll, press the OK button to register To be a WEB server component, and add rsget.rsreturn in the registry HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/W3SVC/Parameters/ADCLaunch.
If you use a custom business object, modify the above ASP file code:
| set rds = CreateObject(RDS.DataSpace) Set df = rds.CreateObject(RDSServer.DataFactory, http://192.168.0.1) '192.168.0.1 is the WEB server IP address strcn=provider=ms remote;remote server=http://192.168.0.1;handler=msdfmap.handler;data source=pubsdatabase; '192.168.0.1 is the IP address of the WEB server strsql= getalljobs Set rs = df.Query(strCn, strSQL) |
Change to:
| set rds = CreateObject(RDS.DataSpace) Set df = rds.CreateObject(rsget.rsreturn,http://192.168.0.1) set rs=df.returnrs(pubsdatabase,getpubsbyid('2','10')) |
In addition, the following configuration is required on the browser side:
Open Control Panel -> INTERNET Options -> Security -> Custom Level -> Initialize and script running for ActiveX controls that are not marked as safe -> Activate