+ Reply to Thread
Results 1 to 6 of 6

Stored Procedures - First Attempt

  1. #1
    Jim Heavey
    Guest

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

  2. #2
    Dave Peterson
    Guest

    Re: Stored Procedures - First Attempt

    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

  3. #3
    Jim Heavey
    Guest

    Re: Stored Procedures - First Attempt

    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?


  4. #4
    Bob Phillips
    Guest

    Re: Stored Procedures - First Attempt

    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" <JimHeavey@discussions.microsoft.com> wrote in message
    news:A88A758D-C41A-42A2-A3CE-1376A289E2F8@microsoft.com...
    > 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!!!




  5. #5
    Bob Phillips
    Guest

    Re: Stored Procedures - First Attempt

    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" <JimHeavey@discussions.microsoft.com> wrote in message
    news:552ECB0D-A47D-4D73-AC14-580FD82A7BB8@microsoft.com...
    > 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?
    >




  6. #6
    Robin Hammond
    Guest

    Re: Stored Procedures - First Attempt

    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" <JimHeavey@discussions.microsoft.com> wrote in message
    news:A88A758D-C41A-42A2-A3CE-1376A289E2F8@microsoft.com...
    >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!!!




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1