VBA/Excel/Access/Word/Access/Stored Procedure — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 15:46, 26 мая 2010
Содержание
Call store procedure
<source lang="vb">
Public Sub CallStoredProcedure()
Const ConnectionString As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" + _ "Persist Security Info=False;Initial Catalog=NorthwindCS;Data Source=LAP800;Workstation ID=LAP800;" Dim Command As Command Set Command = New Command Command.ActiveConnection = ConnectionString Command.rumandText = "[Sales by Year]" Command.rumandType = CommandTypeEnum.adCmdStoredProc Dim BeginningDate As ADODB.Parameter Dim EndingDate As ADODB.Parameter Dim StartDate As Date StartDate = #1/1/1995# Dim EndDate As Date EndDate = #1/1/2004# Set BeginningDate = Command.CreateParameter("@Beginning_Date",DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, , StartDate) Set EndingDate = Command.CreateParameter("@Ending_Date",DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, , EndDate) Call Command.Parameters.Append(BeginningDate) Call Command.Parameters.Append(EndingDate) Dim Recordset As ADODB.Recordset Set Recordset = Command.Execute Call Sheet1.Range("A1").CopyFromRecordset(Recordset)
End Sub
</source>
Create a stored procedure
<source lang="vb">
Sub CreateStoredProc()
Dim cmd As ADODB.rumand Set cmd = New ADODB.rumand cmd.ActiveConnection = CurrentProject.Connection cmd.rumandText = "CREATE PROCEDURE procClientGet " & _ "(CID long) " & _ "AS SELECT ClientID, CompanyName " & _ "FROM tblClients " & _ "WHERE ClientID = CID" cmd.Execute
End Sub
</source>
Creating a Stored Procedure that Accepts Parameters
<source lang="vb">
Sub Create_SpWithParam()
Dim conn As ADODB.Connection On Error GoTo ErrorHandler Set conn = CurrentProject.Connection conn.Execute "CREATE PROCEDURE procEnterData(@Company TEXT (40), @Tel TEXT (24)) AS " & _ "INSERT INTO employee (CompanyName, Phone) VALUES (@Company, @Tel);"
ExitHere:
If Not conn Is Nothing Then If conn.State = adStateOpen Then conn.Close End If Set conn = Nothing Exit Sub
ErrorHandler:
If InStr(1, Err.Description, "procEnterData") Then conn.Execute "DROP PROC procEnterData" Resume Else MsgBox Err.Number & ":" & Err.Description Resume ExitHere End If
End Sub
</source>
Deleting a Stored Procedure
<source lang="vb">
Sub Delete_StoredProc()
Dim conn As ADODB.Connection On Error GoTo ErrorHandler Set conn = CurrentProject.Connection conn.Execute "DROP PROCEDURE procName; "
ExitHere:
If Not conn Is Nothing Then If conn.State = adStateOpen Then conn.Close End If Set conn = Nothing Exit Sub
ErrorHandler:
If InStr(1, Err.Description, "cannot find") Then Debug.Print "The procedure you want to delete does not exist." Else Debug.Print Err.Number & ":" & Err.Description End If Resume ExitHere
End Sub
</source>
Executing a Parameterized Stored Procedure
<source lang="vb">
Sub Execute_StoredProcWithParam()
Dim conn As ADODB.Connection Dim strCompany As String Dim strPhone As String On Error GoTo ErrorHandler Set conn = CurrentProject.Connection strCompany = "nnn" strPhone = "123123" If strCompany <> "" And strPhone <> "" Then conn.Execute "procName " & strCompany & ", " & strPhone End If
ExitHere:
If Not conn Is Nothing Then If conn.State = adStateOpen Then conn.Close End If Set conn = Nothing Exit Sub
ErrorHandler:
Debug.Print Err.Number & ":" & Err.Description Resume ExitHere
End Sub
</source>
Executing a Stored Procedure Containing Parameters
<source lang="vb">
Public Sub UpdateWithStoredProcedure()
Dim cmd As New ADODB.rumand Dim conn As ADODB.Connection Dim prm As ADODB.Parameter Dim strConn As String Dim strSQL As String strConn = "Provider=SQLOLEDB.1;" & _ "Data Source=(local); Initial Catalog=NorthWind;" & _ "Integrated Security=SSPI" Set conn = New ADODB.Connection conn.Open strConn Set cmd = New ADODB.rumand cmd.rumandText = "procOrderUpdate" cmd.rumandType = adCmdStoredProc cmd.ActiveConnection = conn Set prm = cmd.CreateParameter("OrderID", adInteger, adParamInput) cmd.Parameters.Append prm cmd.Parameters("OrderID").Value = 1 Set prm = cmd.CreateParameter("OrderDate", adDate, adParamInput) cmd.Parameters.Append prm cmd.Parameters("OrderDate").Value = "1/1/2007" Set prm = cmd.CreateParameter("ShipVia", adInteger, adParamInput) cmd.Parameters.Append prm cmd.Parameters("ShipVia").Value = 2 Set prm = cmd.CreateParameter("Freight", adCurrency, adParamInput) cmd.Parameters.Append prm cmd.Parameters("Freight").Value = "10.5" "Execute the Stored Procedure cmd.Execute "Close the connection conn.Close End Sub </source>
Use the EXECUTE statement to execute the stored procedure
<source lang="vb">
Sub CreateStoredProc()
Dim cmd As ADODB.rumand Set cmd = New ADODB.rumand cmd.ActiveConnection = CurrentProject.Connection cmd.rumandText = "CREATE PROCEDURE procClientGet " & _ "(CID long) " & _ "AS SELECT ClientID, CompanyName " & _ "FROM tblClients " & _ "WHERE ClientID = CID" cmd.Execute
End Sub Sub ExecuteStoredProc()
Dim rst As ADODB.Recordset Dim cmd As Command Set cmd = New ADODB.rumand cmd.ActiveConnection = CurrentProject.Connection cmd.rumandText = "EXECUTE procClientGet 1" Set rst = cmd.Execute MsgBox rst("CompanyName")
End Sub
</source>