Excel uses ADO to call SQL Server Stored Procedures and ado stored procedures

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