ADO Samples??

[tomtan]

Anyone has any ADO Code samples/Information using Stored Procedures(SQL Server) ?

Samples of INSERT, UPDATE & DELETE.

Please Help.. Thanks.

[CGTS]

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]

Doh !!!!!

I just realised you asked about INSERT, UPDATE Etc.

I will see what I have got.............

[tomtan]

Thanks CGTS for helping..

You have anymore samples INSERT, UPDATE Etc..??

[Aldea]

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]

Thanks for all your help ...