A simpler use of the Asp+rds client-side Reference report first

Source: Internet
Author: User
Tags query range reference
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>
&LT;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>


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.