ADO command parameters

[valeryk2000]

1) I have SS2K8. I created insert and update procedures. They work perfectly when run on the server.

2) My Access application does not use LINKED SQL SAerver table. We are using ADO to communicate with the server.

3) I call this procedure from an Access form using Command.Parameters.Refresh method. Does not work:

Run-time error '3708' Parameter object is improperly defined ... etc.

When I write parameter values into immediate window, then copy this string and run the procedure on the server - again it works perfectly.
====================================================

OK. I AM READY TO SWITCH from Refresh method to Create/Append parameter approach.

However ... Why my current code does not work? Any idea?
Thanks

 

[boblarson]

Why my current code does not work? Any idea?

Just a reminder that we are not mind readers here. If you want to know why your code doesn't work, you need to post the code you are using.

 

[valeryk2000]

I know. Sorry.
=============== Procedure =================
ALTERPROCEDURE [dbo].[sp_UpdateClinicalReviewEntry]
@ClinicalReviewID int,
@HospitalID nvarchar (50)=null,
@MRN nvarchar(10)=null,
@ReferralDate datetime=null,
@EventDate Datetime=null,
@DischDate Datetime=null,
@DischDateText nvarchar(20)=null,
@NotDischarged bit=null,
@EntryUser int=null,
@CaseSummary nvarchar(max)=null,
@NoteToQualityManager nvarchar(250)=null,
@PhysicianEntId nvarchar(5)=null,
@ProblemCategoryID int=null,
@RecommendCaseReview int=null,
@OtherProblemCategory nvarchar(200)=null,
@PsnRecordID nvarchar(10)=null,
@LastModifiedBy Nvarchar(8),
@LastModifiedDate DateTime

AS
BEGIN
SETNOCOUNTON;
update tblClinicalReview
set HospitalID=@HospitalID,
MRN=@MRN,
ReferralDate=@ReferralDate,
EventDate =@EventDate,
DischDate=@DischDate,
DischDateText=@DischDateText,
NotDischarged=@NotDischarged,
EntryUser=@EntryUser,
CaseSummary=@CaseSummary,
NoteToQualityManager=@NoteToQualityManager,
PhysicianEntID=@PhysicianEntId ,
ProblemCategoryID=@ProblemCategoryID,
RecommendCaseReview=@RecommendCaseReview,
OtherProblemCategory=@OtherProblemCategory,
PSNRecordID=@PsnRecordID,
LastModifedBy=@LastModifiedBy ,
LastModifedDate=@LastModifiedDate

Where ClinicalReviewID=@ClinicalReviewID
Return@@ROWCOUNT
END
======================================
++++++++++++++VBA Code ++++++++++++++++++++++
cmd.ActiveConnection = connSql
cmd.CommandText = "sp_UpdateClinicalReviewEntry"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(0).Direction = adParamReturnValue
cmd.Parameters(1).Value = lngCase
cmd.Parameters(2).Value = strHospital
'========= MRN =============
If Not IsNull(txtMRN.Value) Then
cmd.Parameters(3).Value = txtMRN.Value
Else
cmd.Parameters(3).Value = Null
End If
'++++++++++++ Referral Date
If Not IsNull(txtReferralDate.Value) Then
cmd.Parameters(4).Value = txtReferralDate.Value
Else
cmd.Parameters(4).Value = Null
End If
'--------------Event Date
If Not IsNull(txtEventDate.Value) Or txtEventDate.Value <> "" Then
cmd.Parameters(5).Value = txtEventDate.Value
Else
cmd.Parameters(5).Value = Null
End If
'************** Discharge Date
If Not IsNull(txtDischargeDate.Value) Then
cmd.Parameters(6).Value = txtDischargeDate.Value
Else
cmd.Parameters(6).Value = Null
End If
'=============== DischDateText
If Not IsNull(txtDischargeDateText.Value) Then
cmd.Parameters(7).Value = txtDischargeDateText.Value
Else
cmd.Parameters(7).Value = Null
End If
'++++++++ Not Disch
cmd.Parameters(8).Value = chkNotYetDischargsed.Value
'-------------- entry user

cmd.Parameters(9).Value = lngPersonID
'********************* Case Summary
If Not IsNull(txtCaseSummary.Value) Then
cmd.Parameters(10).Value = txtCaseSummary.Value
Else
cmd.Parameters(10).Value = Null
End If
'=================NoteTo Quality manager
If Not IsNull(txtNoteToQualityManager.Value) Then
cmd.Parameters(11).Value = txtNoteToQualityManager.Value
Else
cmd.Parameters(11).Value = Null
End If
'++++++++++++++++ PhysicianEntID
If Not IsNull(strPhysicianInvolvedID) Then
cmd.Parameters(12).Value = strPhysicianInvolvedID
Else
cmd.Parameters(12).Value = Null
End If
'----------------- ProblemCategoryID ------
cmd.Parameters(13).Value = cboProblemCategory.Column(1)
'================ Recommend Case Review
cmd.Parameters(14).Value = fraRecommendedCaseReview.Value
'+++++++++ Other ProblemCategory
If Not IsNull(txtProblemCategory.Value) Then
cmd.Parameters(15).Value = txtProblemCategory.Value
Else
cmd.Parameters(15).Value = Null
End If
'=============== PsnRecordID
If Not IsNull(txtPSNRecordID.Value) Then
cmd.Parameters(16).Value = txtPSNRecordID.Value
Else
cmd.Parameters(16).Value = Null
End If
'--------------- Modified By
cmd.Parameters(17).Value = uid
'=============== Modified date
cmd.Parameters(18).Value = Now()


cmd.Execute
+++++++++++++++++++++++++++++++++++++++++++++++

I think that the problem might be
@CaseSummary nvarchar(max)

Which ADO 2.8 parameter type would work with this type. Any of those:

adVarChar
adLongVarChar
adVarWChar
adLongVarWChar

Or ... ?