Last time posted a simpler use of Asp+rds client participation report
This post is a more complex client-side report with asp+rds+ components
Error Description: (if you are prompted that an ActiveX component cannot participate in RDS.) DataSpace)
IE need to set security options
Action: Menu Tool->internet option-> security-> Customization
Setting: Start but ActiveX not labeled Security-> Open
Principle Explanation:
Client directly using RDS to generate Recordset security is not enough, using the
Middle-tier Automation components can greatly increase security!
Please see below:
To write a registered component:
ActiveX Dll Project:iacrdsobj.vbp
Class Module Name:rsop
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
Dim StrConnect as String
StrConnect = "PROVIDER=SQLOLEDB; Server=server name; uid=sa;pwd=; Database= "& strDB &";
cn. Open strconnect
' These are aren't listed in the typelib.
Rs. CursorLocation = adUseClient
' Using The Unspecified parameters, an ADO/R recordset is returned.
Rs. Open strSQL, CN, Adopenunspecified, adLockUnspecified, adcmdunspecified
Set Returnrs = rs
Exit Function
Ehgetrecordset:
Err.Raise Err.Number, Err.Source, Err.Description
End Function
Then make Iacrdsobj.dll
If wrong, please set VB menu project-refreence
Add MicroSoft ActiveX Data Object 2.6 Library (of course the number is a little higher)
Then register iacrdsobj.dll to the database server (for security, it is best to change the database uid better than SA)!
Okay, next look at ASP
Long1.asp
<meta content= "text/html; charset=gb2312 "http-equiv=content-type>
<title>client use RDS produce Excel report</title>
<body bgcolor=skyblue topmargin=5 leftmargin= "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 use RDS produce Excel
</font>
</td>
</tr>
</table>
</div>
<form action= "long1.asp" method= "post" name= "MyForm" >
<div align=left>
<input type= "button" value= "Query Data" name= "Query" language= "VBScript" style= "HEIGHT:32PX; width:90px ">
<input type= "button" value= "Clear Data" name= "clear" "language=" VBScript "style=" HEIGHT:32PX; width:90px ">
<input type= "button" value= "Excel", "name=", "language=" VBScript "style=" HEIGHT:32PX; width:90px ">
</div>
<div id= "AddData" ></div>
</form>
</body>
<script language= "VBScript" >
Sub Fun_excel (t)
Dim Rds,rs,df,serverstr
Dim Strsql,strrs
Dim xlapp, Xlbook, XlSheet1
serverstr= "HTTP://SQL server name" ' SQL Server name of register IacRDSObj.dll
' Use RDS to produce client recordset
Set rds = CreateObject ("RDS.") DataSpace ", SERVERSTR)
' Eg:set rds = CreateObject (RDS. DataSpace "," Http://iac_fa ") ' IAC_FA is the LAN SQL Server name
' Eg:set rds = CreateObject (RDS. DataSpace "," http://10.150.254.102 ") ' 10.150.254.102 is the LAN SQL Server IP address
' The Register COM
Set df = rds. CreateObject ("Iacrdsobj.rsop", Serverstr)
' The query string of SQL
strSQL = "SELECT Top 8 * out of jobs order by job_id"
' The recordset
Set rs = df. Returnrs ("pubs", strSQL)
If T=1 Then
If not rs.eof then
strrs= "<table Border=1><tr><td>job_id</td><td>job_desc</td><td>max_ Lvl</td><td>min_lvl</td></tr><tr><td> "+ Rs. GetString (,, "</td><td>", "</td></tr><tr><td>", "") + "</td></tr> </table> "
Adddata.innerhtml=strrs
Strrs= ""
Else
MsgBox "No data in the table!"
End If
ElseIf t=2 Then
Strrs= ""
Adddata.innerhtml=strrs
ElseIf T=3 Then
Set xlapp = CreateObject ("EXCEL. Application ")
Set xlbook = XlApp.Workbooks.Add
Set XlSheet1 = xlbook.worksheets (1)
Xlsheet1.cells (1,1). Value = "The 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
' Adapt to office and 2000
Do as 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
' Adapt to Office
' Xlsheet1.range ("A3"). CopyFromRecordset RS
' xlSheet1.Application.Visible = True
End If
Rs.close
Set rs=nothing
End Sub
</script>
