в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:46:51Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Access/Stored Procedure - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия