ActiveConnection, CommandText, CommandTimeout, CommandType, Size, Direction 속성 예제(VB)
이 예제에서는 ActiveConnection, CommandText, CommandTimeout, CommandType, Size, Direction 속성을 사용하여 저장 프로시저를 실행합니다.
'BeginActiveConnectionVB
'To integrate this code
'replace the data source and initial catalog values
'in the connection string
Public Sub Main()
On Error GoTo ErrorHandler
'recordset, command and connection variables
Dim Cnxn As ADODB.Connection
Dim cmdByRoyalty As ADODB.Command
Dim prmByRoyalty As ADODB.Parameter
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim strCnxn As String
Dim strSQLAuthors As String
Dim strSQLByRoyalty As String
'record variables
Dim intRoyalty As Integer
Dim strAuthorID As String
' Define a command object for a stored procedure
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Cnxn.Open strCnxn
Set cmdByRoyalty = New ADODB.Command
Set cmdByRoyalty.ActiveConnection = Cnxn
' Set the criteria
strSQLByRoyalty = "byroyalty"
cmdByRoyalty.CommandText = strSQLByRoyalty
cmdByRoyalty.CommandType = adCmdStoredProc
cmdByRoyalty.CommandTimeout = 15
' Define the stored procedure's input parameter
intRoyalty = Trim(InputBox("Enter royalty:"))
Set prmByRoyalty = New ADODB.Parameter
prmByRoyalty.Type = adInteger
prmByRoyalty.Size = 3
prmByRoyalty.Direction = adParamInput
prmByRoyalty.Value = intRoyalty
cmdByRoyalty.Parameters.Append prmByRoyalty
' Create a recordset by executing the command.
Set rstByRoyalty = cmdByRoyalty.Execute()
' Open the Authors Table to get author names for display
Set rstAuthors = New ADODB.Recordset
strSQLAuthors = "Authors"
'rstAuthors.Open strSQLAuthors, strCnxn, , , adCmdTable
rstAuthors.Open strSQLAuthors, strCnxn, adOpenForwardOnly, adLockReadOnly, adCmdTable
'the above two lines of code are identical as the default values for
'CursorType and LockType arguments match those shown
' Print the recordset and add author names from Table
Debug.Print "Authors with " & intRoyalty & _
" percent royalty"
Do Until rstByRoyalty.EOF
strAuthorID = rstByRoyalty!au_id
Debug.Print , rstByRoyalty!au_id & ", ";
rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
Debug.Print rstAuthors!au_fname & " " & _
rstAuthors!au_lname
rstByRoyalty.MoveNext
Loop
' clean up
rstAuthors.Close
rstByRoyalty.Close
Cnxn.Close
Set rstAuthors = Nothing
Set rstByRoyalty = Nothing
Set Cnxn = Nothing
Exit Sub
ErrorHandler:
' clean up
If Not rstAuthors Is Nothing Then
If rstAuthors.State = adStateOpen Then rstAuthors.Close
End If
Set rstAuthors = Nothing
If Not rstByRoyalty Is Nothing Then
If rstByRoyalty.State = adStateOpen Then rstByRoyalty.Close
End If
Set rstByRoyalty = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
'EndActiveConnectionVB
참고 항목
ActiveCommand 속성(ADO)
명령 개체(ADO)
CommandText 속성(ADO)
CommandTimeout 속성(ADO)
CommandType 속성(ADO)
연결 개체(ADO)
Direction 속성
Parameter 개체
레코드 개체(ADO)
레코드 집합 개체(ADO)
Size 속성(ADO 매개 변수)
피드백
https://aka.ms/ContentUserFeedback
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요.다음에 대한 사용자 의견 제출 및 보기