problem with different methods of invoking stored procedures through ADO
- From: Accessed <rwatson@xxxxxxxxxxxxxx>
- Date: Mon, 6 Apr 2009 20:05:37 -0700 (PDT)
Hi all
I may have missed something obvious, but the two methods of assigining
parameters when calling an SQL stored procedure produce different
results for a routine.
In the example below, the commented out lines
eg
' ,parameters.append .......
resulted in the stored procedure failing to execute an Update sql
statement in a MS SQL stored procedure, but by changing to
..parameters("@.....
the routine worked fine.
Can anyone suggest a reason for the differing performance ? The
parameter @CLAIM_ID is the crux of the problem, as when I hard code
the value ( 6469) in the WHERE clause, the UPDATE statement works
fine for the other values.
Many Thanks in advance
RW
========================================================
Code examples
VB
With connCmd
.ActiveConnection = GconCCG
.CommandType = adCmdStoredProc
.CommandText = "usp_Fixed_Allocation_DeAllocateClaim"
' Procedure fails to find the record if i use these lines
'.Parameters.Append .CreateParameter("@CLAIM_ID", adInteger,
adParamInput, lngCLAIM_ID)
'.Parameters.Append .CreateParameter("@NewComments", adVarChar,
adParamInput, 255, trim(strComments))
'.Parameters.Append .CreateParameter("@AllocReason", adInteger,
adParamInput, lngReallocationReason)
' these work fine
.Parameters("@CLAIM_ID") = lngCLAIM_ID
.Parameters("@NewComments") = Trim(strComments)
.Parameters("@AllocReason") = lngReallocationReason
.Execute RecordsAffected:=LngRecsAffected
End With
USP contains :
..........................
UPDATE dbo.CLAIM_QUEUE_ALLOCATION
SET
dbo.CLAIM_QUEUE_ALLOCATION.[CURRENT] = 'N',
REALLOCATION_REASON = @AllocReason,
COMMENTS = @NewComments,
REALLOCATION_DATE =@currentdate
WHERE (
CLAIM_ID =@Claim_Id -- 6469
and dbo.CLAIM_QUEUE_ALLOCATION.[CURRENT] = 'Y'
)
.
- Prev by Date: Re: Excessive CPU utliization
- Next by Date: Re: "Date()" doesn't update when application stays open
- Previous by thread: "Date()" doesn't update when application stays open
- Next by thread: restrict data entry until fields are complete
- Index(es):
Relevant Pages
|
