|
|
|
| Title | Create and invoke a stored procedure in an Access database |
|---|
| Description | This example shows how to create and invoke a stored procedure in an Access database in Visual Basic 6. |
|---|
| Keywords | stored procedure, Access, database, |
|---|
| Categories | Database |
|---|
|
|
|
A stored procedure is a function stored inside a database. They are useful for several reasons. They let you change a function without recompiling the program and let you store database functionality with the data it manipulates. If the database sits on a network, the stored procedure can improve performance by examining many records and only returning a small result.
This program creates a stored procedure by executing the CREATE PROCEDURE statement.
|
|
' Create the stored procedure.
Private Sub cmdCreate_Click()
' Drop the procedure if it already exists.
On Error Resume Next
m_DBConnection.Execute "DROP PROCEDURE BookInfo"
On Error GoTo 0
' Create the stored procedure.
m_DBConnection.Execute lblProcedure.Caption
cmdCreate.Enabled = False
cboTitle.Enabled = True
End Sub
|
|
|
When the user selects a book title from a ComboBox, the program executes the stored procedure. It creates a Command object, adds a parameter to give the stored procedure the data it needs, and executes the command.
|
|
' Display information for this book.
Private Sub cboTitle_Click()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim result As String
Dim txt As String
Dim i As Integer
' Create a command object.
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = m_DBConnection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "BookInfo"
txt = cboTitle.Text
cmd.Parameters.Append cmd.CreateParameter("title", _
adVarChar, _
adParamInput, Len(txt), txt)
' Execute the command.
Set rs = cmd.Execute
' Display the results.
txt = ""
For i = 0 To rs.Fields.Count - 1
txt = txt & rs.Fields(i).Name & " = " & _
rs.Fields(i).Value & vbCrLf
Next i
lblResults.Caption = txt
' Close the recordset and free it and the command
' object.
rs.Close
Set rs = Nothing
Set cmd = Nothing
End Sub
|
| |
|
| |
|
|
