Dim Str, Val, n, Codes
Dim eyear As String
Dim etype As String
Dim ecode As String
Dim line As Integer
1 Function SearchEvents(ByVal year As String, ByVal etype As String, ByVal code As String)
2 Dim lastRows As Integer
3 strSQL = "exec PROC_Evaluation_EvalProgress_Select_Backup " & year & "," & etype & "," & code
4 'strSQL = "exec PROC_Evaluation_index_EvaluatorStatus "
5
6
7
8 Call rsOpen
9 If rs.RecordCount <> 0 Then
10 i = line
11 line = line + rs.RecordCount
12 Sheet2.Cells(4 + i, 1).CopyFromRecordset rs
13
14 Else
15 MsgBox Q004
16 End If
17 Call rsClose
18
19 End Function
1 Function TypeChange1(ByVal Source As String, ByVal TargetType As String, ByVal Target As String) As String
2 If Source = TargetType Then
3 Source = Target
4 End If
5 TypeChange1 = Source
6 End Function
1 Option Explicit
2
3 '''''廋惓梡 Server
4 'Public Const strIP As String = "172.20.3.135"
5 'Public Const strUser As String = "develop"
6 'Public Const strPasswd As String = "develop"
7 'Public Const strDB As String = "KOUBAI"
8
9 '''''嫵堢梡 Server
10 Public Const strIP As String = "10.100.1.220"
11 Public Const strUser As String = "sa"
12 Public Const strPasswd As String = ""
13 Public Const strDB As String = "dbEvaluation"
14
15 '''''杮斣梡 Server
16 'Public Const strIP As String = "172.20.5.68"
17 'Public Const strUser As String = "sa"
18 'Public Const strPasswd As String = "bpo"
19 'Public Const strDB As String = "dbKoubai"
20
21
22 Public strSQL As String
23 Public cn As New ADODB.Connection
24 Public rs As New ADODB.Recordset
25
26 Public Sub cnOpen()
27 On Error GoTo err
28 If cn Is Nothing Then
29 Set cn = New ADODB.Connection
30 End If
31 If cn.State = 0 Then
32 With cn
33 .Provider = "SQLOLEDB.1"
34 .ConnectionString = "Persist Security Info=True;" & _
35 "Data Source=" & strIP & ";" & _
36 "Initial Catalog=" & strDB & ";" & _
37 "User ID=" & strUser & ";" & _
38 "Password=" & strPasswd
39 .ConnectionTimeout = 800
40 .CommandTimeout = 800
41 .CursorLocation = adUseClient
42 .Open
43 End With
44 End If
45 Exit Sub
46 err:
47 MsgBox "DB偲僐僱僋僩偱偒傑偣傫偱偟偨丅"
48 End
49 End Sub
50
51 Public Sub cnClose()
52 If Not cn Is Nothing Then
53 If cn.State <> 0 Then cn.Close
54 Set cn = Nothing
55 End If
56 End Sub
57
58 Public Sub rsOpen()
59 If rs Is Nothing Then
60 Set rs = New ADODB.Recordset
61 End If
62 If rs.State = 0 Then
63 If cn.State = adStateClosed Then cnOpen
64
65 If cn.State = adStateOpen Then rs.Open strSQL, cn
66 End If
67
68 End Sub
69
70 Public Sub rsClose()
71 If Not rs Is Nothing Then
72 If rs.State <> 0 Then rs.Close
73 Set rs = Nothing
74 End If
75 End Sub
另外,在调用存储过程的时候,会遇到问题,数据库抽出数据时存放在ADODB.Recordset中的,每次抽出数据都会存放在rs中,但是,如果存储过程中含有临时表,那么,每执行一次临时表,ADODB.Recordset会自动关闭,因此外部使用ADODB.Recordset中的数据时便会报错,无法打开ADODB.Recordset,对于这种问题的解决方案是:将将存储过程中的临时表统统替换为sql语句,这样就不存在临时表,ADODB.Recordset也就会正常开关。