Stored Procedures - First Attempt

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!!!

This doesn't look like Excel to me.

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

Jim Heavey wrote:
>
> 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

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?

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)

"Jim Heavey" <[email protected]> wrote in message
news:[email protected]...
> 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!!!

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)

"Jim Heavey" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

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


"Jim Heavey" <[email protected]> wrote in message
news:[email protected]...
>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!!!