tomtan
Jul 27th, 2000, 03:08 AM
Anyone has any ADO Code samples/Information using Stored Procedures(SQL Server) ?
Samples of INSERT, UPDATE & DELETE.
Please Help.. Thanks.
CGTS
Jul 27th, 2000, 03:33 AM
How many do you want?
These are straight out of a book.... so don't blame me if they don't work, I haven't tested them.
------------------------------------------------------------
CREATING A PARAMETERIZED PROCEDURE
DIM conn As ADODB.Connection
DIM cmd As ADODB.Command
DIM rs As ADODB.Recordset
DIM Param1 As ADODB.Parameter
REM Open the Connection object, omit the password
conn.Open "data source=IDCDatabase", "john"
REM Associate the Command with a Connection
cmd.ActiveConnection = conn
REM Specify the Stored Procedure
cmd.CommandText =
"SELECT * FROM CUSTOMERS WHERE CITY = ?"
REM Save A Prepared Version Of This Query
cmd.Prepared = True
cmd.CommandType = adCmdText
REM Create The Command Parameters
Set Param1 = cmd.CreateParameter("City",adBSTR,adParamInput)
REM Serach For Customers In Buffalo
Param1.Value = "Buffalo"
REM Add The Parameters
cmd.Parameters.Append Param1
rs = cmd.Execute(NumRecs)
REM The rs Recordset Only Contains Customers That Live In
REM Buffalo
REM Close the Connection and Recordset...
rs.Close
conn.Close
------------------------------------------------------------
USING THE SP_WHO STORED PROCEDURE
DIM conn As ADODB.Connection
DIM cmd As ADODB.Command
DIM rs As ADODB.Recordset
DIM Param1 As ADODB.Parameter
REM Open the Connection object, omit the password
conn.Open "data source=IDCDatabase", "john"
REM Associate the Command with a Connection
cmd.ActiveConnection = conn
REM Specify the Stored Procedure
cmd.CommandText = "SP_WHO"
cmd.CommandType = adCmdStoredProc
set rs = cmd.Execute(NumRecs)
REM Display The Login Name For Each Current User
rs.MoveFirst
Do While (NOT rs.EOF)
MsgBox rs.Fields("LOGINAME")
rs.MoveNext
Loop
REM Close the Connection...
rs.Close
conn.Close
------------------------------------------------------------
USING THE SP_ADDGROUP STORED PROCEDURE
DIM conn As ADODB.Connection
DIM cmd As ADODB.Command
DIM rs As ADODB.Recordset
DIM Param1 As ADODB.Parameter
DIM Param2 As ADODB.Parameter
REM Open the Connection object, omit the password
conn.Open "data source=IDCDatabase", "john"
REM Associate the Command with a Connection
cmd.ActiveConnection = conn
REM Specify the Stored Procedure
cmd.CommandText = "SP_ADDGROUP"
cmd.CommandType = adCmdStoredProc
REM Create The Command Parameters
Set Param1 = cmd.CreateParameter("Return",
adInteger,adParamReturnValue)
Set Param2 = cmd.CreateParameter("GroupName",
adBSTR,adParamInput)
REM Create The ADMIN Group
Param2.Value = "ADMIN"
REM Add The Parameters
cmd.Parameters.Append Param1
cmd.Parameters.Append Param2
cmd.Execute(NumRecs)
If(Param1.Value = 0) Then
MsgBox "The Group " & Param2.Value & " Was Added Successfully!"
Else
MsgBox "The Group " & Param2.Value & " Was NOT Added Successfully!"
End If
REM Close the Connection...
conn.Close
------------------------------------------------------------
USING THE SP_ADDUSER STORED PROCEDURE
DIM conn As ADODB.Connection
DIM cmd As ADODB.Command
DIM rs As ADODB.Recordset
DIM Param1 As ADODB.Parameter
DIM Param2 As ADODB.Parameter
DIM Param3 As ADODB.Parameter
DIM Param4 As ADODB.Parameter
REM Open the Connection object, omit the password
conn.Open "data source=IDCDatabase", "john"
REM Associate the Command with a Connection
cmd.ActiveConnection = conn
REM Specify the Stored Procedure
cmd.CommandText = "SP_ADDUSER"
cmd.CommandType = adCmdStoredProc
REM Create The Command Parameters
Set Param1 = cmd.CreateParameter("Return",
adInteger,adParamReturnValue)
Set Param2 = cmd.CreateParameter("UserName",
adBSTR,adParamInput)
Set Param3 = cmd.CreateParameter("DBUserName",
adBSTR,adParamInput)
Set Param4 = cmd.CreateParameter("GroupName",
adBSTR,adParamInput)
REM Add The User Marie TO The ADMIN Group
Param2.Value = "MARIE"
Param3.Value = "MARIE”
Param4.Value = "ADMIN"
REM Add The Parameters
cmd.Parameters.Append Param1
cmd.Parameters.Append Param2
cmd.Parameters.Append Param3
cmd.Parameters.Append Param4
cmd.Execute(NumRecs)
If(Param1.Value = 0) Then
MsgBox "User " & Param2.Value & " Was Added!!"
Else
MsgBox "User " & Param2.Value & " Was NOT Added!"
End If
REM Close the Connection...
conn.Close
------------------------------------------------------------
USING THE SP_CHANGEGROUP STORED PROCEDURE
DIM conn As ADODB.Connection
DIM cmd As ADODB.Command
DIM rs As ADODB.Recordset
DIM Param1 As ADODB.Parameter
DIM Param2 As ADODB.Parameter
DIM Param3 As ADODB.Parameter
REM Open the Connection object, omit the password
conn.Open "data source=IDCDatabase", "john"
REM Associate the Command with a Connection
cmd.ActiveConnection = conn
REM Specify the Stored Procedure
cmd.CommandText = "SP_CHANGEGROUP"
cmd.CommandType = adCmdStoredProc
REM Create The Command Parameters
Set Param1 = cmd.CreateParameter("Return",
adInteger,adParamReturnValue)
Set Param2 = cmd.CreateParameter("GroupName",
adBSTR,adParamInput)
Set Param3 = cmd.CreateParameter("UserName",
adBSTR,adParamInput)
REM Add The User Marie TO The ADMIN Group
Param2.Value = "ADMIN"
Param3.Value = "MARIE"
REM Add The Parameters
cmd.Parameters.Append Param1
cmd.Parameters.Append Param2
cmd.Parameters.Append Param3
cmd.Execute(NumRecs)
If(Param1.Value = 0) Then
MsgBox "User " & Param3.Value &
" Was Added To The Group " &
Param2.Value & " Successfully!"
Else
MsgBox "User " & Param3.Value &
" Was NOT Added To The Group " &
Param2.Value & "!"
End If
REM Close the Connection...
conn.Close
------------------------------------------------------------
USING THE SP_DROPGROUP STORED PROCEDURE
DIM conn As ADODB.Connection
DIM cmd As ADODB.Command
DIM rs As ADODB.Recordset
DIM Param1 As ADODB.Parameter
DIM Param2 As ADODB.Parameter
REM Open the Connection object, omit the password
conn.Open "data source=IDCDatabase", "john"
REM Associate the Command with a Connection
cmd.ActiveConnection = conn
REM Specify the Stored Procedure
cmd.CommandText = "SP_DROPGROUP"
cmd.CommandType = adCmdStoredProc
REM Create The Command Parameters
Set Param1 = cmd.CreateParameter("Return",
adInteger,adParamReturnValue)
Set Param2 = cmd.CreateParameter("GroupName",
adBSTR,adParamInput)
REM Remove The Admin Group
Param2.Value = "ADMIN"
REM Add The Parameters
cmd.Parameters.Append Param1
cmd.Parameters.Append Param2
cmd.Execute(NumRecs)
If(Param1.Value = 0) Then
MsgBox "The Group " & Param2.Value & " Was Removed!"
Else
MsgBox "The Group " & Param2.Value &
" Was NOT Removed!"
End If
REM Close the Connection...
conn.Close
------------------------------------------------------------
USING THE SP_PASSWORD STORED PROCEDURE
DIM conn As ADODB.Connection
DIM cmd As ADODB.Command
DIM rs As ADODB.Recordset
DIM Param1 As ADODB.Parameter
DIM Param2 As ADODB.Parameter
DIM Param2 As ADODB.Parameter
REM Open the Connection object, omit the password
conn.Open "data source=IDCDatabase", "john"
REM Associate the Command with a Connection
cmd.ActiveConnection = conn
REM Specify the Stored Procedure
cmd.CommandText = "SP_PASSWORD"
cmd.CommandType = adCmdStoredProc
REM Create The Command Parameters
Set Param1 = cmd.CreateParameter("Return",
adInteger,adParamReturnValue)
Set Param2 = cmd.CreateParameter("UserName",
adBSTR,adParamInput)
Set Param3 = cmd.CreateParameter("GroupName",
adBSTR,adParamInput)
REM Change The Password For The User Marie
Param2.Value = "MARIE"
Param3.Value = "POOH"
REM Add The Parameters
cmd.Parameters.Append Param1
cmd.Parameters.Append Param2
cmd.Parameters.Append Param3
cmd.Execute(NumRecs)
If(Param1.Value = 0) Then
MsgBox "The Password Was Changed Successfully!"
Else
MsgBox "The Password Was NOT Changed!"
End If
REM Close the Connection...
conn.Close
------------------------------------------------------------
Cheers.
CGTS
Jul 27th, 2000, 03:34 AM
Doh !!!!!
I just realised you asked about INSERT, UPDATE Etc.
I will see what I have got.............
tomtan
Jul 27th, 2000, 04:03 AM
Thanks CGTS for helping..
You have anymore samples INSERT, UPDATE Etc..??
Aldea
Jul 27th, 2000, 10:00 AM
This is from Karl Moore Tutorial SQL SERVER -4.So if you want more go check his tutorial and you'll understand better.
Good Luck
This is a UPDATE procedure.
CREATE PROCEDURE UpdateTitle @OldTitle varchar(80), @NewTitle varchar(80)
AS
UPDATE Titles
SET Titles.Title = @NewTitle
WHERE Titles.Title = @OldTitle
VB Code:
Private Sub Command1_Click()
Dim objConn As New ADODB.Connection
Dim objCommand As New ADODB.Command
Dim Params(1 To 2) As Variant
'Objects to be used in this operation -
'objConn, the basic connection
'objCommand, which handles the stored procedure
'Params(1 To 2), a variant array to hold the two
' arguments this stored procedures requires
' NOTE: When passing arrays across to stored procedures
' you must declare them as the variant type, unless
' you like the phrase 'major problems'...
With objConn
.ConnectionString = "Driver=SQL Server;Server=COLOSSI;" & _
"Database=PUBS;User ID=KarlMoore;Password=TEST"
.Open
End With
'Get a connection to the database
With objCommand
.CommandType = adCmdStoredProc
.CommandText = "UpdateTitle"
.ActiveConnection = objConn
End With
'Tell objCommand what it will be working with
Params(1) = InputBox("Enter the book title to change:", "Book Title", "Net Etiquette")
Params(2) = InputBox("Enter the new title for " & strParams(1), "New Title", "My New Title")
'Put the old title and new title into the array,
'ready to pass as arguments to the stored procedure
objCommand.Execute , Params
'Execute the stored procedure, passing it the parameter
Set objCommand = Nothing
objConn.Close
Set objConn = Nothing
'Close all references
End Sub
tomtan
Jul 27th, 2000, 08:51 PM
Thanks for all your help ...