RE: executing sproc

Issue is resolved by adding Initialization string to DSN. This was needed
for the stored proc but not for the SQL statement. Can I pass this
initialization string via a Connection Execute?

"scw-tzg" wrote:

> 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: ExecuteNonQuery() returns -1
    ... affected for INSERT, DELETE and UPDATE statements. ... (presumably including calls to a stored proc) ... CalendarType As String, ByVal StartDate As Date, ByVal EndDate As Date) ... Dim RowsAffected As Integer ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: StoredProc Parameter Format
    ... You can pass 989,990,991 into a stored proc as string. ... Then build your SQL statement dynamically in stored proc as this: ... > SELECT RecordId ...
    (microsoft.public.sqlserver.programming)
  • Re: Whats the deal?
    ... "buc" wrote in message ... > stored proc in sql that returns a simple string. ... > This works great EXCEPT VB is removing the spaces from the string. ... > the string with a SQL statement ...
    (microsoft.public.dotnet.languages.vb)
  • Truncation of Argument with ADo Components
    ... I have encountered and interesting and frustrating problem with the ADO ... when I pass a string argument which is destined for an image ... column in the DB into a parameterised INSERT query, only the first character ... resorting to using a stored proc. ...
    (borland.public.delphi.database.ado)
  • ExecuteNonQuery() returns -1
    ... I am trying to insert new records in table via stored proc ... There is no exception thorwn but RowsAffected is ... CalendarType As String, ByVal StartDate As Date, ByVal EndDate As Date) ... Dim RowsAffected As Integer ...
    (microsoft.public.dotnet.framework.adonet)