Stored Procedures - First Attempt

[Jim Heavey]

I am trying to figure out how to call a stored procedure. I have entered the
following lines of code ....

Dim cmd As New Command
cmd.CommandType = adCmdStoredProc
Dim parm1 As Parameter
With parm1
.DataType = xlParamTypeInteger
.Name = "i_User_ID"
.Value = 1
End With
Dim parm2 As Parameter
With parm2
.Name = "i_Time_ID"
.DataType = xlParamTypeInteger
.Value = 136
End With
cmd.Parameters.Append (parm1)
cmd.Parameters.Append (parm2)
cnn.ConnectionString = "Provider=MSDAORA.1;Password=YYYYYY;User
ID=XXXXX;Data Source=ZZZZZZZ;Persist Security Info=True"
cmd.ActiveConnection = cnn
cmd.Execute

The code fails when I attempt to place something into my Parameter with the
following error "Object Variable or with block not set". Sounds like it
wants me to instatiate the object, but I can not use the "new" with
"Parameter" object.

So what am I doing wrong? How do I associate the Command Object with the
Connection Object....I was guessing "cmd.ActiveConnection".

Thanks in advance for your assistance!!!

 

[Dave Peterson]

This doesn't look like Excel to me.

Maybe you wanted to post in one of the Access newsgroups.

 

[Jim Heavey]

These objects are all available in VBA within Excel. Not sure why you are
suggesting that this is not Excel related....it is.

I have figured out that I need to specify ADODB.Parameter and this gets me
by the error that I was experiencing, but then it fails which I attempt to
load the parameters into the Command Object.

I get the error "object required" for the following commands

cmd.Parameters.Append (parm1)
cmd.Parameters.Append (parm2)

The code to create parm1, looks like the following:

Dim parm1 As New ADODB.Parameter
With parm1
.Type = adInteger
.Name = "i_User_ID"
.Value = 1
End With

When I hover over parm1, is see the value of "1" being displayed back to me.
Why does the cmd.Parameters.Append (parm1) fail?

 

[Bob Phillips]

I think, at the very least, you should be using ADO data types, not Excel.

So

.DataType = xlParamTypeInteger

should be

.DataType = adInteger

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Did you declare the command as adodb?

Dim cmd As New ADODB.Command


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Robin Hammond]

Jim,

This is a reply to a post a couple of weeks ago. You need to look at
refreshing the parameters for the stored proc.

You should be able to decipher what you need from the example.

If not, post back.

'You need to add a project reference to MS ActiveX Data Objects
'back in the database DO THE FOLLOWING
CREATE PROC spTemp(@Table1 nvarchar(50), @Table2 nvarchar(50))
as
-- example of a dynamic SQL sp returning multiple recordsets
SET NOCOUNT ON
EXEC('SELECT * FROM ' + @Table1)
EXEC('SELECT * FROM ' + @Table2)
SET NOCOUNT OFF
GO

Sub Test()
Dim vParams As Variant
Dim vValues As Variant
Dim rsReturn As ADODB.Recordset
vParams = Array("Table1", "Table2")
vValues = Array("TableName1", "TableName2")
'change DBNAME to whatever DB you created the above proc in
ReturnRSFromSP "spTemp", vParams, vValues, "DBNAME"
End Sub

Public Sub ReturnRSFromSP(strSP As String, _
vParams As Variant, _
vValues As Variant, _
strCatalog As String)

Dim cnSP As ADODB.Connection
Dim cmdSP As ADODB.Command
Dim lCounter As Long
Dim strItem As String
Dim lIndex As Long
Dim rsReturn As ADODB.Recordset

Set cnSP = New ADODB.Connection

cnSP.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=" & strCatalog & _
";Data Source=" & FILLTHISIN 'add your data source here
cnSP.Open

'create the command object
Set cmdSP = New ADODB.Command
cmdSP.ActiveConnection = cnSP
cmdSP.CommandText = strSP
cmdSP.CommandType = adCmdStoredProc
'here's the bit you seem to be missing
cmdSP.Parameters.Refresh

lCounter = 0

For lCounter = 1 To cmdSP.Parameters.Count - 1

strItem = cmdSP.Parameters(lCounter).Name

For lIndex = 0 To UBound(vParams)

If "@" & vParams(lIndex) = strItem Then

cmdSP.Parameters(lCounter).Value = vValues(lIndex)
Exit For

End If

Next

Next

'*****************************************
'use this bit if trying to return results as a recordset
'delete it otherwise
'*****************************************

'create the recordset object
Set rsReturn = New ADODB.Recordset

With rsReturn

.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic

'execute the SP returning the result into a recordset
.Open cmdSP

End With

Do Until rsReturn Is Nothing

If rsReturn.State = adStateOpen Then

DumpRecordset rsReturn

End If

Set rsReturn = rsReturn.NextRecordset

Loop
'*********************end of section

'*****************************************
'use this bit if just trying to run a stored proc
'delete it otherwise
'*****************************************
'execute the SP
oCmd.Execute

'*********************end of section


Set cmdSP = Nothing

If cnSP.State = adStateOpen Then
cnSP.Close
End If
Set cnSP = Nothing
Set rsReturn = Nothing
End Sub

Sub DumpRecordset(rsName As ADODB.Recordset, Optional lstartpos As Long)
Dim W As Workbook
Dim nField As Integer
Dim lRowPos As Long

Set W = ActiveWorkbook

Workbooks.Add

With rsName

For nField = 1 To .Fields.Count

Cells(1, nField).Value = .Fields(nField - 1).Name

Next nField

If .RecordCount = 0 Then Exit Sub
.MoveFirst

If Not IsEmpty(lstartpos) Then .Move lstartpos

End With

Cells(2, 1).CopyFromRecordset rsName
End Sub

Robin Hammond
www.enhanceddatasystems.com