executing sproc

I have an Excel macro from which I connect to a DB in order to execute a
stored procedure that will ultimately insert a row into a DB table. First I
establish a new Connection Cnct and open it successfully. Then I create a new
Command Cmnd and set it up to run my stored proc, using Cnct as my
ActiveConnection. Before I run the stored proc, I execute a SQL Select
statement as a method on connection Cnct. It returns a Recordset with 2
fields. So far, so good. Then I load up my stored proc's parameters into
Cmnd object and execute the proc as a method on Cmnd. Everything looks like
it's working and there are no error messages, but my data does not get
inserted to the DB.

Am I doing something bad in running the SQL statement on my active connection
before I execute the stored proc on the command that uses that active
connection?

Or are there settings that I am missing?

Or can you suggest how I might investigate what happens when I execute the
stored proc? I've looked at each of the parameters in the watch window to
ensure all the values are correct. I've also tried running the stored proc on
the DB to make sure it will work as expected (then backed the data out).

Any suggestions appreciated.

Code follows...

Public objCnct As New ADODB.Connection
Public objCmnd As New ADODB.Command
Dim lcsConnectionString As String

'' Establish DB connection
lcsConnectionString = "DSN=" & gcsServer & _
";Uid=" & gcsUserId & _
";Pwd=" & gcsPassword & _
";Database=" & gcsDatabase
With objCnct
.ConnectionString = lcsConnectionString
.ConnectionTimeout = 10
.Properties("Prompt") = adPromptNever
.Open
End With

'' Set up stored proc
With objCmnd
.CommandText = "CERSP_ATTG_APPLY"
.CommandType = adCmdStoredProc
.ActiveConnection = objCnct
.Parameters.Refresh
.NamedParameters = True
End With

Do ...
lnRetCd = Execute_Sql(llCurrentRow)
Loop


Private Function Execute_Sql(plCurrentRow As Long) As Integer

Dim objRS As New ADODB.Recordset
Dim lvDestId As Variant
Dim lvSourceId As Variant
Dim lcsSql As String

On Error GoTo Execute_Sql_Error

' Prepare SQL in string to retrieve Datetime-stamps from DB
lcsSql = "select ATXR_DEST_ID, ATXR_SOURCE_ID " & _
"from CER_ATLP_PRINT_HST where " & _
"ATSY_ID = '" & RTrim(.Cells(plCurrentRow, 1).Value) & "'" & _
" and ATXR_DEST_ID = '...'" & _
" and ATLT_SEQ_NO = 0 and ATLP_SEQ_NO = 0"

Set objRS = objCnct.Execute(lcsSql)

lvDestId = objRS(0) ' ATXR_DEST_ID
lvSourceId = objRS(1) ' ATXR_SOURCE_ID

objRS.Close


' Prepare SP parameters
objCmnd("@pLOCK_TOKEN") = 3
objCmnd("@pLOCK_TOKEN_IND") = 1
objCmnd("@pATSY_ID") = RTrim(.Cells(plCurrentRow, 1).Value)
objCmnd("@pATXR_DEST_ID") = lvDestId
objCmnd("@pATLT_SEQ_NO") = 0
objCmnd("@pATLD_ID") = RTrim(.Cells(plCurrentRow, 5).Value)
objCmnd("@pATTG_TYPE") = "R"
objCmnd("@pATTG_REQUEST_DT") = Now
objCmnd("@pATTG_SUBMITTED_DT") = "1/1/1753"
objCmnd("@pATTG_CREATE_USUS") = RTrim(.Cells(plCurrentRow, 11).Value)
objCmnd("@pATTG_DESC") = RTrim(.Cells(plCurrentRow, 12).Value)
objCmnd("@pATTG_SITE_CODE") = RTrim(.Cells(plCurrentRow, 13).Value)
objCmnd("@pATTB_ID") = RTrim(.Cells(plCurrentRow, 6).Value)
objCmnd("@pATTG_ORIG_ATLT_ID") = "1/1/1753"
objCmnd("@pATDF_PHYS_DOC_NAME") = RTrim(.Cells(plCurrentRow, 5).Value)
objCmnd("@pATTG_LOCK_TOKEN") = 3
objCmnd("@pATXR_SOURCE_ID") = "1/1/1753"

objCmnd.Execute

Execute_Sql_Exit:

On Error Resume Next
Set objRS = Nothing
Execute_Sql = lnRetCd
Exit Function

Execute_Sql_Error:

' log the error
' .Cells(grngLastLog.Row, 3).Value = "Execute_Sql Error: " & Err.Number
& " - " & Err.Description

Err.Clear

Resume Execute_Sql_Exit

End Function

.

Relevant Pages

  • Re: Operation is not allowed...
    ... If the connection is open, can I execute two statements against it before I ... Dim cmsRS, cmsSQL ... Set cmsRS = Server.CreateObject ...
    (microsoft.public.inetserver.asp.general)
  • Re: Operation is not allowed...
    ... > If the connection is open, can I execute two statements against it ... > Dim cmsRS, cmsSQL ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)
  • execute stored proc in Excel
    ... i'm trying to execute a stored proc stored in Sybase in VBA EXCEL 2003. ... Dim DB As BSERDBConnect.BSERDataConnect ...
    (microsoft.public.excel.programming)
  • Unable to recognize stored procedure param
    ... execute from my application. ... Dim Connection As SqlConnection ... Dim Command As SqlCommand ...
    (microsoft.public.dotnet.languages.vb)
  • excute stored proc in VBA
    ... i'm trying to execute a stored proc stored in Sybase in VBA EXCEL 2003. ... Dim DB As BSERDBConnect.BSERDataConnect ...
    (microsoft.public.vb.database.ado)