利用Array传递参数、变量、条件到COM方法

 

利用Array传递参数、变量、条件到COM方法

以前建立COM中的Function，往往只要改变参数个数，就会造成机码不兼容的状况，偶发现一个可以避免的方法

旧 Function A(byval p1 as string, byval p2 as string ,byval p3 as string) as Adodb.Recordet
新 Function A(Parameter as Varinet,Rs as Varent) as Varient
其中，Parameter是一个矩阵，可用来作参数传递、多笔维护、SQL查询条件等。
传递【参数、变数】：

比如说有一个Function共有3个参数，分别是RTN,DEPT,AREA
新的写法如下：
Asp中
 

RTN = Session(“userrtn”)
DEPT = Session(“userdept”)
AREA = Session(“userarea”)
Dim P(3)
P(1) = RTN
P(2) = DEPT
P(3) = AREA
Set Obj = Server.CreateObject(“Pxxx0000.Cxxx0000”)
RC = Obj.A(P,Rs)

Com 中：
 

Public Function A() Function A(Parameter as Varient,Rs as Varient) as Varient
Dim RTN as String
Dim DEPT as String
Dim AREA as String

If Not(IsArray(Parameter) Then
        Goto Err_NotArray
End If


RTN = Parameter(1)
DEPT = Parameter(2)
AREA = Parameter(3)

Set Rs = CreateObject(“ADODB.Recordset”)
With Rs
        .CursorLocation = adUseClientBatch
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .ActiveConnection = conn  
End With

SQLTXT = “SELECT * FROM TXXX1234 WHERE (RTN = ‘” & RTN & ”’) AND (DEPT = ‘” & DEPT & ”’) AND (AREA=’” & AREA & ”’)”
Rs.Open SQLTXT

A = “Success”

……………….

 

 

==============================================================================
例1：传递【条件】

ASP:

Dim P(3,7)
P(1,1) = "WHERE"
P(2,1) = "(SOURCE = "
P(3,1) = "N'2'"
P(1,2) = "OR"
P(2,2) = "SOURCE = "
P(3,2) = "N'5'"
P(1,3) = "OR"
P(2,3) = "SOURCE = "
P(3,3) = "N'6')"
P(1,4) = "AND"
P(2,4) = "(DIVPROD = "
P(3,4) = "N'B'"
P(1,5) = "OR"
P(2,5) = "DIVPROD = "
P(3,5) = "N'I'"
P(1,6) = "OR"
P(2,6) = "DIVPROD = "
P(3,6) = "N'9')"
P(1,7) = "ORDER BY"
P(2,7) = "PROD"
P(3,7) = ""
Set Obj = Server.CreateObject("PXXXXXX.CXXXXXXX")
RC = Obj.ProdMutiLst(P,rs)

 

===============================================================================

COM:
 

Set Rs = CreateObject("ADODB.Recordset")

With Rs
        .CursorLocation = adUseClientBatch
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .ActiveConnection = conn
End With
‘基本的SELECT
SQLTXT1 = "SELECT * FROM VSANPRD"

If Not IsArray(Parameter) Then
        SQLTXT1 = SQLTXT1 & ""
Else
        ArrayCount = UBound(Parameter, 2)
        For i = 1 To ArrayCount
                SQLTXT1 = SQLTXT1 & " " & Parameter(1, i) & " " & Parameter(2, i) & " " & Parameter(3, i)
        Next
End If
Rs.Open SQLTXT1

ProdMutiLst = "Success"

例2：传递【变量、参数、多笔维护】
ASP:

‘***************************************************************
‘** 此部份传递变数(参数)
‘***************************************************************

 

Dim Parameter(13)  
Parameter(1) = OldStudyID  
Parameter(2) = StudyID  
Parameter(3) = YYYY  
Parameter(4) = SName  
Parameter(5) = Birthday  
Parameter(6) = Father  
Parameter(7) = Mother  
Parameter(8) = ClassID  
Parameter(9) = PostNo  
Parameter(10) = City  
Parameter(11) = Area  
Parameter(12) = Road  
Parameter(13) = Memo  

'***************************************************************
'** 此部份【多笔维护】
'***************************************************************
Dim Tel()  
TelCnt = Request.Form("TelNo").Count  
ReDim Tel(2,TelCnt)  
For y=1 to TelCnt  
        Tel(1,y) = Request.Form("TelName").Item(y)  
        Tel(2,y) = Request.Form("TelNo").Item(y)  
Next  
Set Obj = Server.CreateObject("PCSLStudent.CCSLStudent0")  
RC = Obj.StudentModify(RType,Parameter,Tel,rs)

COM:
 

'***************************************************************
'** 此部份传递变数(参数)
'***************************************************************
OldStudyID = Parameter(1)
StudyID = Parameter(2)
YYYY = Parameter(3)
SName = Parameter(4)
Birthday = Parameter(5)
Father = Parameter(6)
Mother = Parameter(7)
ClassID = Parameter(8)
PostNo = Parameter(9)
City = Parameter(10)
Area = Parameter(11)
Road = Parameter(12)
Memo = Parameter(13)

TelCnt = UBound(Tel, 2)

SQLTXT2 = "INSERT INTO StudentMain (StudyID, YYYY, Name, Birthday, Father, Mother, ClassID, PostNo, City, Area, Road, [Memo]) VALUES ('" & StudyID & "', '" & YYYY & "', '" & SName & "', '" & Birthday & "', '" & Father & "', '" & Mother & "', " & ClassID & ", '" & PostNo & "', '" & City & "', '" & Area & "', '" & Road & "', '" & Memo & "')"
Conn.Execute SQLTXT2

  

'***************************************************************
'‘** 此部份【多笔维护】
'***************************************************************
For y = 1 To TelCnt
        TelName = Tel(1, y)
        TelNo = Tel(2, y)
        SQLTXT3 = "INSERT INTO StudentTel (StudyID, TelName, TelNo) VALUES ('" & StudyID & "', '" & TelName & "', '" & TelNo & "')"
        conn.Execute SQLTXT3
Next y