Excel uses ADO to call SQL Server Stored Procedures and ado stored procedures
Test environment: SQL Server 2014, Excel 2013
Excel example File Download: http://download.csdn.net/detail/zhanghongju/8533555
In the early stage, I made a small software program for on-site voting and planned to use Excel. The workbook contains two worksheets, each containing a command button for submitting and viewing the voting results.
The problem lies in the check vote button. At first, the SELECT statement is used for query, and everything is normal. In the later stage, the voting result requires weight calculation, which is complicated, I thought of putting the computing process on the SQL Server side and implementing it using the stored procedure. However, when a stored procedure is called, a problem occurs and the record set cannot be returned. Finally, the problem is found in the setting of the CursorLocation attribute of the record set. The process and solution of the problem are as follows to avoid similar problems.
I. Differences between setting a cursor using the CursorLocation attribute
The CursorLocation attribute has two attribute values: adUseClient (Constant Value: 3) and adUseServer (Constant Value: 2 ). AdUseClient indicates that the client cursor provided by the local cursor library is used, and adUseServer is the default value, indicating that the cursor provided by the data provider or driver is used.
The adUseClient cursor service is generally more powerful, and many functions may not be provided by the cursor provided by the driver. However, the adUseServer cursor is highly sensitive. You can see the changes made by other users to the data source. This feature is advantageous for some multi-user operations. This feature makes the number of rows in the record set uncertain. In some cases, the RecordCount attribute cannot be used to obtain the number of rows in the record set, nor can we use EOF and BOF to determine whether it has reached the end and header of the record set.
2. Create sample data in SQL Server
The following statement is used to create two tables in SQL Server. Dbo. myScore is used to store the selected personnel ID, Project ID, and score, dbo. myPerson is used to store the ID of the selected person and whether the selected person is an expert. The flag is 1, indicating that the selected person is an expert.
IF OBJECT_ID ('dbo. MyScore ') IS NOT NULL
Drop table dbo. MyScore;
IF OBJECT_ID ('dbo. MyPerson ') IS NOT NULL
Drop table dbo. MyPerson;
GO
Create table dbo. MyScore (PersonID int, ProjectID int, ProjectScore int );
Create table dbo. MyPerson (PersonID int, IsExpert int );
Insert into dbo. MyScore VALUES
(1, 1001, 90 ),
(1, 1002, 80 ),
(1, 1003, 95 ),
(2, 1001, 85 ),
(2, 1002, 85 ),
(2, 1003, 90 ),
(3, 1001,100 ),
(3, 1002, 90 ),
(3, 1003, 95 );
Insert into dbo. MyPerson VALUES
(1, 0 ),
(2, 0 ),
(3, 1); -- 3 is an expert reviewer
Iii. query methods in Excel
1. Execute SELECT query
Open Excel, insert a command button in Sheet1, and specify the Click Method for it. The Code is as follows:
Sub button ← click ()
Dim cn As New ADODB. Connection, strCn AsString, strSQL As String, rs As New ADODB. Recordset
StrCn = "Provider = sqloledb; Server = 192.0.168.1; Database = MyTMP; Uid = sa; Pwd = 111111"
Cn. Open strCn
If cn. State <> adStateOpen Then
On Error Resume Next
Cn. Close
Err. Clear
O = MsgBox ("Data Connection Failed", vbOKOnly, "prompt ")
Exit Sub
End If
'Clear the data in the Excel worksheet and set the column name
ActiveWorkbook. Worksheets ("Sheet1"). Cells. Select
Selection. ClearContents
Range ("A1"). Value = "participating Project ID"
Range ("B1"). Value = "average score"
'Directly query the average score of all selected personnel
StrSQL = "SELECTProjectID, AVG (ProjectScore) AS ProjectScore FROM dbo. MyScore GROUP BYProjectID"
Rs. Open strSQL, cn, adOpenKeyset, adLockReadOnly
O = MsgBox ("number of records:" & rs. RecordCount & ", cursor position:" & rs. CursorLocation, vbOKOnly) 'indicates the number of rows and cursor position to be obtained.
With rs
If Not (. BOF And. EOF) Then 'has data records, the traversal record set is displayed in the Excel worksheet.
For I = 2 To. RecordCount + 1
Cells (I, 1) =. Fields (0)
Cells (I, 2) =. Fields (1)
. MoveNext
Next
End If
End
End Sub
The above Code uses the MsgBox function to prompt the number of rows in the retrieved record set. If there is a data record, it traverses the record set and writes the data to the Excel worksheet. If the above Code does not specify the CursorLocation attribute for the record set, use the default value adUseServer. When executing the code, you can see the number of records and cursor positions displayed in the pop-up dialog box, as shown in.
Of course, to ensure normal reference of the ADO object, you should add a reference to the Microsoft ActiveXData Objects support library in the project, as shown in.
2. Execute the Stored Procedure
First, create dbo in SQL Server. the storage process of usp_GetScore. This process is used to determine whether an expert participates in the selection. If an expert participates in the selection, the project score is calculated based on the weight of 70% of ordinary personnel and 30% of expert personnel. If no, calculate the average project score.
Create procedure dbo. usp_GetScore
AS
BEGIN
DECLARE @ IsExpert AS int;
Select top 1 @ IsExpert = B. IsExpert
FROM dbo. MyScore
Inner join dbo. MyPerson B
On a. PersonID = B. PersonID
Where B. IsExpert = 1;
/* In case of expert voting, the proportion of experts is 30%, and that of other personnel is 70%;
Otherwise, the average value of everyone */
IF @ IsExpertIS NOT NULL
WITH CTE
(Select a. ProjectID, A. ProjectScore, B. IsExpert
FROMdbo. MyScore
INNERJOIN dbo. MyPersonB
ONA. PersonID = B. PersonID
),
CTE1
(SELECT ProjectID, AVG (ProjectScore) * 0.7 AS ProjectScore
FROM CTE
WHERE IsExpert <> 1
GROUP BYProjectID
UNION ALL
SELECT ProjectID, AVG (ProjectScore) * 0.3 AS ProjectScore
FROM CTE
WHERE IsExpert = 1
GROUP BYProjectID
) SELECTProjectID, SUM (ProjectScore) AS ProjectScore
FROM CTE1
Group by ProjectID;
ELSE
SELECT ProjectID, AVG (ProjectScore) AS ProjectScore
FROM dbo. MyScore
Group by ProjectID;
END;
Replace the code of the "strSQL" button in the Excel file with the stored procedure as follows:
Sub button ← click ()
Dim cn As New ADODB. Connection, strCn AsString, strSQL As String, rs As New ADODB. Recordset
StrCn = "Provider = sqloledb; Server = 46.0.187.151; Database = MyTMP; Uid = sa; Pwd = zhj11111"
Cn. Open strCn
If cn. State <> adStateOpen Then
On Error Resume Next
Cn. Close
Err. Clear
O = MsgBox ("Data Connection Failed", vbOKOnly, "prompt ")
Exit Sub
End If
'Clear the data in the Excel worksheet and set the column name
ActiveWorkbook. Worksheets ("Sheet1"). Cells. Select
Selection. ClearContents
Range ("A1"). Value = "participating Project ID"
Range ("B1"). Value = "average score"
'Directly query the average score of all selected personnel
StrSQL = "EXECUTE dbo. usp_GetScore"
Rs. Open strSQL, cn, adOpenKeyset, adLockReadOnly
O = MsgBox ("number of records:" & rs. RecordCount & ", cursor position:" & rs. CursorLocation, vbOKOnly) 'indicates the number of retrieved rows
With rs
If Not (. BOF And. EOF) Then 'has data records, the traversal record set is displayed in the Excel worksheet.
For I = 2 To. RecordCount + 1
Cells (I, 1) =. Fields (0)
Cells (I, 2) =. Fields (1)
. MoveNext
Next
End If
End
End Sub
Run the Code. In the displayed dialog box, the number of rows returned from the record set is-1, as shown in. Because the number of records cannot be determined, it cannot be written to Excel.
The solution is to set the CursorLocation attribute of the record set to adUseClient so that it can run normally. Refer to the following code:
StrSQL = "EXECUTE dbo. usp_GetScore"
Rs. CursorLocation = adUseClient
Rs. Open strSQL, cn, adOpenKeyset, adLockReadOnly
Even if adUseClient is not specified, although the RecordCount of the record set returns-1, the record set actually has data. We can skip the method of traversing the record set, instead, use the CopyFromRecordset method in Excel to paste the record set directly into the worksheet. refer to the following code:
Sub button ← click ()
Dim cn As New ADODB. Connection, strCn AsString, strSQL As String, rs As New ADODB. Recordset
StrCn = "Provider = sqloledb; Server = 46.0.187.151; Database = MyTMP; Uid = sa; Pwd = zhj11111"
Cn. Open strCn
If cn. State <> adStateOpen Then
On Error Resume Next
Cn. Close
Err. Clear
O = MsgBox ("Data Connection Failed", vbOKOnly, "prompt ")
Exit Sub
End If
'Clear the data in the Excel worksheet and set the column name
ActiveWorkbook. Worksheets ("Sheet1"). Cells. Select
Selection. ClearContents
Range ("A1"). Value = "participating Project ID"
Range ("B1"). Value = "average score"
'Directly query the average score of all selected personnel
StrSQL = "EXECUTEdbo. usp_GetScore"
Rs. CursorLocation = adUseClient
Rs. Open strSQL, cn, adOpenKeyset, adLockReadOnly
Range ("A2"). CopyFromRecordset rs 'is directly pasted into Excel.
End Sub