expects parameter ??? , which was not supplied."

G

Guest

Hi Everyone,
I'm getting the above error when I try to execute a stored proc. I've been
trhough the SP and the vb function and can't find anything missing or
incorrect, unless I missed something. Here is a list of things I've tried:
1. Removed the offending parameter from the SP and VB Function. Everything
works.
2. Made sure that the parameters are using the correct parm type.
3. Made sure that I was not missing a parameter.
4. Made sure that no parameter would get passed in the code.
Here is the function that calls the SP. Its a long procedure, so please bear
with me:
Private Function SaveAdmissionNursingAssessment2(cnSQL As SqlConnection) as
Boolean
'Dim cnSQL As SqlConnection
Dim success as Boolean = True
Dim cmSQL As SqlCommand
Try
'cnSQL = OpenConnection()
if isnew then
cmSQL = New SqlCommand("intake_AddAdminNurseAssessment2", cnSQL)
cmsql.Transaction = SaveTrans
isnew = False
else
cmSQL = New SqlCommand("intake_UpdAdmissionNursingAssessment2", cnSQL)
cmsql.Transaction = SaveTrans
isDirty = False
end if
cmsql.CommandType = CommandType.StoredProcedure
cmsql.Parameters.Add("PatientID", sqldbtype.Varchar, 20 ).Value = fPatientId
cmsql.Parameters.Add("PreparedDate", sqldbtype.SmallDateTime).Value = Now()
'''(Cut down because the 30000 limit in the forum)
if flxFamilyHistory2(4,1) = true then
cmsql.Parameters.Add("fhBleeding", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhBleeding", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("@WhoMeBleedingWho", sqldbtype.Varchar, 20 ).Value
= iif(isdbnull(flxFamilyHistory2(4,3)), "", flxFamilyHistory2(4,3))
if flxFamilyHistory2(5,1) = true then
cmsql.Parameters.Add("fhTuberculosis", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhTuberculosis", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhTuberculosisWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(5,3)
if flxFamilyHistory2(6,1) = true then
cmsql.Parameters.Add("fhNervous", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhNervous", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhNervousWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(6,3)
if flxFamilyHistory2(7,1) = true then
cmsql.Parameters.Add("fhSuicide", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhSuicide", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhSuicideWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(7,3)
if flxFamilyHistory2(8,1) = true then
cmsql.Parameters.Add("fhEpilepsy", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhEpilepsy", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhEpilepsyWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(8,3)
if flxFamilyHistory2(9,1) = true then
cmsql.Parameters.Add("fhAllergy", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhAllergy", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhAllergyWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(9,3)
if flxFamilyHistory2(10,1) = true then
cmsql.Parameters.Add("fhStroke", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhStroke", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhStrokeWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(10,3)
if flxFamilyHistory2(11,1) = true then
cmsql.Parameters.Add("fhArteries", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhArteries", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhArteriesWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(11,3)
if flxFamilyHistory2(12,1) = true then
cmsql.Parameters.Add("fhSickleCellTraits", sqldbtype.SmallInt).Value
= 1
else
cmsql.Parameters.Add("fhSickleCellTraits", sqldbtype.SmallInt).Value
= 0
End If
cmsql.Parameters.Add("fhSickleCellTraitsWho", sqldbtype.Varchar, 20 ).Value
= flxFamilyHistory2(12,3)
if flxFamilyHistory2(13,1) = true then
cmsql.Parameters.Add("fhSickleCellDisease",
sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhSickleCellDisease",
sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhSickleCellDiseaseWho", sqldbtype.Varchar, 20
).Value = flxFamilyHistory2(13, 3)
if flxFamilyHistory2(14,1) = true then
cmsql.Parameters.Add("fhAnemia", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhAnemia", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhAnemiaWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(14,3)
if flxFamilyHistory2(15,1) = true then
cmsql.Parameters.Add("fhHereditaryDefects",
sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhHereditaryDefects",
sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhHereditaryDefectsWho", sqldbtype.Varchar, 20
).Value = flxFamilyHistory2(15,3)
if flxFamilyHistory2(16,1) = true then
cmsql.Parameters.Add("fhRenalDisease", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhRenalDisease", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhRenalDiseaseWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(16,3)
if flxFamilyHistory2(17,1) = true then
cmsql.Parameters.Add("fhHEP", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhHEP", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhHEPWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(17,3)
if flxFamilyHistory2(18,1) = true then
cmsql.Parameters.Add("fhHIV", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhHIV", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhHIVWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(18,3)
if flxFamilyHistory2(19,1) = true then
cmsql.Parameters.Add("fhSTD", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhSTD", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhSTDWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(19,3)
if flxFamilyHistory2(20,1) = true then
cmsql.Parameters.Add("fhOtherInfection", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhOtherInfection", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhOtherInfectionWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(20,3)
if flxFamilyHistory2(21,1) = true then
cmsql.Parameters.Add("fhOther", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhOther", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhOtherWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(21,3)
cmsql.Parameters.Add("pmcReason1", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(1,0)
cmsql.Parameters.Add("pmcReason2", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(2,0)
cmsql.Parameters.Add("pmcReason3", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(3,0)
cmsql.Parameters.Add("pmcReason4", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(4,0)
cmsql.Parameters.Add("pmcYear1", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(1,1)
cmsql.Parameters.Add("pmcYear2", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(2,1)
cmsql.Parameters.Add("pmcYear3", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(3,1)
cmsql.Parameters.Add("pmcYear4", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(4,1)
cmsql.Parameters.Add("pmcWhere1", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(1,2)
cmsql.Parameters.Add("pmcWhere2", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(2,2)
cmsql.Parameters.Add("pmcWhere3", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(3,2)
cmsql.Parameters.Add("pmcWhere4", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(4,2)
cmsql.Parameters.Add("pmcDoctor1", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(1,3)
cmsql.Parameters.Add("pmcDoctor2", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(2,3)
cmsql.Parameters.Add("pmcDoctor3", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(3,3)
cmsql.Parameters.Add("pmcDoctor4", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(4,3)
cmsql.Parameters.Add("cdBeenHospitalized", sqldbtype.SmallInt).Value =
chkSeenDoctorYes.Checked
cmsql.Parameters.Add("cdWhereTreated", sqldbtype.Varchar, 200 ).Value =
txtSeenDoctor.Text
cmsql.Parameters.Add("cdCanContact", sqldbtype.SmallInt).Value =
chkCurrentPhysicianYes.Checked
cmsql.Parameters.Add("cdPrivatePhysician", sqldbtype.Varchar, 200 ).Value =
txtCurrentPhysician.Text
cmsql.Parameters.Add("cdContactName1", sqldbtype.Varchar, 100 ).Value =
txtContactName1.Text
cmsql.Parameters.Add("cdContactName2", sqldbtype.Varchar, 100 ).Value =
txtContactName2.Text
cmsql.Parameters.Add("cdContactRelation1", sqldbtype.Varchar, 25 ).Value =
txtRelationship1.Text
cmsql.Parameters.Add("cdContactRelation2", sqldbtype.Varchar, 25 ).Value =
txtRelationship2.Text
cmsql.Parameters.Add("cdContactAddress1", sqldbtype.Varchar, 150 ).Value =
txtContactAddress1.Text
cmsql.Parameters.Add("cdContactAddress2", sqldbtype.Varchar, 150 ).Value =
txtContactAddress2.Text
cmsql.Parameters.Add("cdContactPhone1", sqldbtype.Varchar, 20 ).Value =
txtContactNumber1.Text
cmsql.Parameters.Add("cdContactPhone2", sqldbtype.Varchar, 20 ).Value =
txtContactNumber2.Text
cmSQL.ExecuteNonQuery()
SaveTrans.Commit
Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
SaveTrans.Rollback
success = False
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
SaveTrans.Rollback
success = False
finally
' Close and Clean up objects
'SaveTrans.Commit
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
SaveTrans.Dispose
SaveAdmissionNursingAssessment2 = success
End Try

Here is the stored proc:

CREATE PROCEDURE [dbo].[intake_AddAdminNurseAssessment2]
@PatientID VarChar (10),
@PreparedDate smalldatetime,
@Menarche VarChar (20),
@Cycle VarChar (20),
@Duration VarChar (20),
@PeriodsRegular SmallInt,
@BleedingIntercourse SmallInt,
@ArePeriods SmallInt,
@Pregnancies Int,
@Cramps SmallInt,
@MiscarriageCount Int,
@Heasdaches SmallInt,
@AbortionCount SmallInt,
@LastPapSmear smalldatetime,
@TroublePregnancy SmallInt,
@SpottingPeriods SmallInt,
@NumChildren SmallInt,
@LastPeriod smalldatetime,
@StillBirths SmallInt,
@Premenopausal SmallInt,
@Postmenopausal SmallInt,
@StartMenopausal VarChar (20),
@VaginalDischarge SmallInt,
@DischargeColor VarChar (15),
@DichargeOdor SmallInt,
@PresentlyPregnant SmallInt,
@PresentlyPregnantIfYes SmallInt,
@PreNatalCare VarChar (100),
@BirthControl SmallInt,
@BirthControlIfYes VarChar (50),
@FreqBirthControl SmallInt,
@LastMammogram smalldatetime,
@SelfBreastExam SmallInt,
@TesticularEnlargement SmallInt,
@UrineStream SmallInt,
@PenileLegion SmallInt,
@DischargePenis SmallInt,
@TesticularMasses SmallInt,
@ProstateProblems SmallInt,
@HepatitsB smalldatetime,
@MMR smalldatetime,
@FluVaccine smalldatetime,
@BCG smalldatetime,
@Pneumovax smalldatetime,
@Tetanus smalldatetime,
@ImmunationOther smalldatetime,
@HadSurgery SmallInt,
@Appendectomy SmallInt,
@AppendectomyComment VarChar (50),
@Hysterectomy SmallInt,
@HysterectomyComment VarChar (50),
@Ovarian SmallInt,
@OvarianComment VarChar (50),
@JointReplacement SmallInt,
@JointReplacementComment VarChar (50),
@Gallbladder SmallInt,
@GallbladderComment VarChar (50),
@Bypass SmallInt,
@BypassComment VarChar (50),
@OperationsOther SmallInt,
@OperationsOtherComment VarChar (50),
@Allergies1 VarChar (50),
@Allergies2 VarChar (50),
@Allergies3 VarChar (50),
@Medication1 VarChar (50),
@Medication2 VarChar (50),
@Medication3 VarChar (50),
@scNoProblems SmallInt,
@scFeeding SmallInt,
@scElimination SmallInt,
@scBathing SmallInt,
@scAmbulating SmallInt,
@scDress SmallInt,
@scHomeCare SmallInt,
@scDecribe VarChar (200),
@shSingle SmallInt,
@shMarried SmallInt,
@shSeperated SmallInt,
@shDivorced SmallInt,
@shWidowed SmallInt,
@shSignificantOther SmallInt,
@shWhomLiveWith VarChar (150),
@shIllicitDrugs SmallInt,
@shIllicitDrugsSpecify VarChar (100),
@shCoffee VarChar (5),
@shTea VarChar (5),
@shColas VarChar (5),
@shForeignTravel SmallInt,
@shForeignTravelSpecify varchar(150),
@shFumes SmallInt,
@shFumesSpecify VarChar (50),
@shTimeLostWork VarChar (50),
@paDepression SmallInt,
@paAnxiety SmallInt,
@paHostile SmallInt,
@paAgitate SmallInt,
@paConfused SmallInt,
@paHallucinations SmallInt,
@paVisual SmallInt,
@paAuditory SmallInt,
@paGustatory SmallInt,
@paOlfactory SmallInt,
@paTactile SmallInt,
@Treatment1 VarChar (100),
@Treatment2 VarChar (100),
@Treatment3 VarChar (100),
@Treatment4 VarChar (100),
@Treatment5 VarChar (100),
@Treatment6 VarChar (100),
@Treatment7 VarChar (100),
@PsycMedication1 VarChar (100),
@PsycMedication2 VarChar (100),
@PsycMedication3 VarChar (100),
@PsycMedication4 VarChar (100),
@fhCancer SmallInt,
@fhCancerWho VarChar (20),
@fhGlaucoma SmallInt,
@fhGlaucomaWho VarChar (20),
@fhDiabeted SmallInt,
@fhDiabetedWho VarChar (20),
@fhBleeding SmallInt,
/*
@BleedingWho Varchar(20),
*/
@WhoMeBleedingWho Varchar(20),
@fhTuberculosis SmallInt,
@fhTuberculosisWho VarChar (20),
@fhNervous SmallInt,
@fhNervousWho VarChar (20),
@fhSuicide SmallInt,
@fhSuicideWho VarChar (20),
@fhEpilepsy SmallInt,
@fhEpilepsyWho VarChar (20),
@fhAllergy SmallInt,
@fhAllergyWho VarChar (20),
@fhStroke SmallInt,
@fhStrokeWho VarChar (20),
@fhArteries SmallInt,
@fhArteriesWho VarChar (20),
@fhSickleCellTraits SmallInt,
@fhSickleCellTraitsWho VarChar (20),
@fhSickleCellDisease SmallInt,
@fhSickleCellDiseaseWho VarChar (20),
@fhAnemia SmallInt,
@fhAnemiaWho VarChar (20),
@fhHereditaryDefects SmallInt,
@fhHereditaryDefectsWho VarChar (20),
@fhRenalDisease SmallInt,
@fhRenalDiseaseWho VarChar (20),
@fhHEP SmallInt,
@fhHEPWho VarChar (20),
@fhHIV SmallInt,
@fhHIVWho VarChar (20),
@fhSTD SmallInt,
@fhSTDWho VarChar (20),
@fhOtherInfection SmallInt,
@fhOtherInfectionWho VarChar (20),
@fhOther SmallInt,
@fhOtherWho VarChar (20),
@pmcReason1 VarChar (50),
@pmcReason2 VarChar (50),
@pmcReason3 VarChar (50),
@pmcReason4 VarChar (50),
@pmcYear1 VarChar (10),
@pmcYear2 VarChar (10),
@pmcYear3 VarChar (10),
@pmcYear4 VarChar (10),
@pmcWhere1 VarChar (25),
@pmcWhere2 VarChar (25),
@pmcWhere3 VarChar (25),
@pmcWhere4 VarChar (25),
@pmcDoctor1 VarChar (25),
@pmcDoctor2 VarChar (25),
@pmcDoctor3 VarChar (25),
@pmcDoctor4 VarChar (25),
@cdBeenHospitalized SmallInt,
@cdWhereTreated VarChar (200),
@cdCanContact SmallInt,
@cdPrivatePhysician VarChar (200),
@cdContactName1 VarChar (100),
@cdContactName2 VarChar (100),
@cdContactRelation1 VarChar (25),
@cdContactRelation2 VarChar (25),
@cdContactAddress1 VarChar (150),
@cdContactAddress2 VarChar (150),
@cdContactPhone1 VarChar (20),
@cdContactPhone2 VarChar (20)
AS

Declare @LastValue int

Set @LastValue = (Select AdminNurseAsses2 from Support)
set @LastValue = @LastValue + 1
Update Support
Set AdminNurseAsses2 = @LastValue

Insert into AdmissionNursingAssessment2(
KeyId, PatientID, PreparedDate, Menarche, Cycle, Duration, PeriodsRegular,
BleedingIntercourse, ArePeriods, Pregnancies, Cramps,
MiscarriageCount, Heasdaches, AbortionCount, LastPapSmear, TroublePregnancy,
SpottingPeriods, NumChildren, LastPeriod, StillBirths,
Premenopausal, Postmenopausal, StartMenopausal, VaginalDischarge,
DischargeColor, DichargeOdor, PresentlyPregnant, PresentlyPregnantIfYes,
PreNatalCare, BirthControl, BirthControlIfYes, FreqBirthControl,
LastMammogram, SelfBreastExam, TesticularEnlargement, UrineStream,
PenileLegion,
DischargePenis, TesticularMasses, ProstateProblems, HepatitsB, MMR,
FluVaccine, BCG, Pneumovax, Tetanus, ImmunationOther, HadSurgery,
Appendectomy, AppendectomyComment, Hysterectomy, HysterectomyComment,
Ovarian, OvarianComment, JointReplacement, JointReplacementComment,
Gallbladder, GallbladderComment, Bypass, BypassComment, OperationsOther,
OperationsOtherComment, Allergies1, Allergies2, Allergies3, Medication1,
Medication2, Medication3, scNoProblems, scFeeding, scElimination, scBathing,
scAmbulating, scDress, scHomeCare, scDecribe, shSingle, shMarried,
shSeperated, shDivorced, shWidowed, shSignificantOther, shWhomLiveWith,
shIllicitDrugs, shIllicitDrugsSpecify, shCoffee, shTea, shColas,
shForeignTravel, shForeignTravelSpecify, shFumes, shFumesSpecify,
shTimeLostWork, paDepression, paAnxiety, paHostile, paAgitate, paConfused,
paHallucinations,
paVisual, paAuditory, paGustatory, paOlfactory, paTactile, Treatment1,
Treatment2, Treatment3, Treatment4, Treatment5, Treatment6, Treatment7,
PsycMedication1, PsycMedication2, PsycMedication3, PsycMedication4,
fhCancer, fhCancerWho, fhGlaucoma, fhGlaucomaWho, fhDiabeted,
fhDiabetedWho, fhBleeding,
fhBleedingWho,
fhTuberculosis, fhTuberculosisWho, fhNervous, fhNervousWho, fhSuicide,
fhSuicideWho, fhEpilepsy,
fhEpilepsyWho, fhAllergy, fhAllergyWho, fhStroke, fhStrokeWho, fhArteries,
fhArteriesWho, fhSickleCellTraits, fhSickleCellTraitsWho, fhSickleCellDisease,
fhSickleCellDiseaseWho, fhAnemia, fhAnemiaWho, fhHereditaryDefects,
fhHereditaryDefectsWho, fhRenalDisease, fhRenalDiseaseWho, fhHEP,
fhHEPWho, fhHIV, fhHIVWho, fhSTD, fhSTDWho, fhOtherInfection,
fhOtherInfectionWho, fhOther, fhOtherWho, pmcReason1, pmcReason2,
pmcReason3, pmcReason4, pmcYear1, pmcYear2, pmcYear3, pmcYear4, pmcWhere1,
pmcWhere2, pmcWhere3, pmcWhere4, pmcDoctor1,
pmcDoctor2, pmcDoctor3, pmcDoctor4, cdBeenHospitalized, cdWhereTreated,
cdCanContact, cdPrivatePhysician, cdContactName1, cdContactName2,
cdContactRelation1, cdContactRelation2, cdContactAddress1,
cdContactAddress2, cdContactPhone1, cdContactPhone2) Values(
@LastValue, @PatientID, @PreparedDate, @Menarche, @Cycle, @Duration,
@PeriodsRegular, @BleedingIntercourse, @ArePeriods,
@Pregnancies, @Cramps, @MiscarriageCount, @Heasdaches, @AbortionCount,
@LastPapSmear, @TroublePregnancy, @SpottingPeriods,
@NumChildren, @LastPeriod, @StillBirths, @Premenopausal, @Postmenopausal,
@StartMenopausal, @VaginalDischarge, @DischargeColor,
@DichargeOdor, @PresentlyPregnant, @PresentlyPregnantIfYes, @PreNatalCare,
@BirthControl, @BirthControlIfYes, @FreqBirthControl,
@LastMammogram, @SelfBreastExam, @TesticularEnlargement, @UrineStream,
@PenileLegion, @DischargePenis, @TesticularMasses,
@ProstateProblems, @HepatitsB, @MMR, @FluVaccine, @BCG, @Pneumovax,
@Tetanus, @ImmunationOther, @HadSurgery, @Appendectomy,
@AppendectomyComment, @Hysterectomy, @HysterectomyComment, @Ovarian,
@OvarianComment, @JointReplacement, @JointReplacementComment,
@Gallbladder, @GallbladderComment, @Bypass, @BypassComment,
@OperationsOther, @OperationsOtherComment, @Allergies1, @Allergies2,
@Allergies3, @Medication1, @Medication2, @Medication3, @scNoProblems,
@scFeeding, @scElimination, @scBathing, @scAmbulating, @scDress,
@scHomeCare, @scDecribe, @shSingle, @shMarried, @shSeperated, @shDivorced,
@shWidowed, @shSignificantOther, @shWhomLiveWith,
@shIllicitDrugs, @shIllicitDrugsSpecify, @shCoffee, @shTea, @shColas,
@shForeignTravel, @shForeignTravelSpecify, @shFumes, @shFumesSpecify,
@shTimeLostWork,
@paDepression, @paAnxiety, @paHostile, @paAgitate, @paConfused,
@paHallucinations, @paVisual, @paAuditory, @paGustatory, @paOlfactory,
@paTactile, @Treatment1, @Treatment2, @Treatment3, @Treatment4, @Treatment5,
@Treatment6, @Treatment7, @PsycMedication1,
@PsycMedication2, @PsycMedication3, @PsycMedication4, @fhCancer,
@fhCancerWho, @fhGlaucoma, @fhGlaucomaWho, @fhDiabeted,
@fhDiabetedWho, @fhBleeding,
@WhoMeBleedingWho,
@fhTuberculosis, @fhTuberculosisWho, @fhNervous, @fhNervousWho, @fhSuicide,
@fhSuicideWho, @fhEpilepsy, @fhEpilepsyWho, @fhAllergy, @fhAllergyWho,
@fhStroke, @fhStrokeWho, @fhArteries, @fhArteriesWho,
@fhSickleCellTraits, @fhSickleCellTraitsWho, @fhSickleCellDisease,
@fhSickleCellDiseaseWho, @fhAnemia, @fhAnemiaWho, @fhHereditaryDefects,
@fhHereditaryDefectsWho, @fhRenalDisease, @fhRenalDiseaseWho, @fhHEP,
@fhHEPWho, @fhHIV, @fhHIVWho, @fhSTD, @fhSTDWho,
@fhOtherInfection, @fhOtherInfectionWho, @fhOther, @fhOtherWho, @pmcReason1,
@pmcReason2, @pmcReason3, @pmcReason4, @pmcYear1,
@pmcYear2, @pmcYear3, @pmcYear4, @pmcWhere1, @pmcWhere2, @pmcWhere3,
@pmcWhere4, @pmcDoctor1, @pmcDoctor2, @pmcDoctor3,
@pmcDoctor4, @cdBeenHospitalized, @cdWhereTreated, @cdCanContact,
@cdPrivatePhysician, @cdContactName1, @cdContactName2,
@cdContactRelation1, @cdContactRelation2, @cdContactAddress1,
@cdContactAddress2, @cdContactPhone1, @cdContactPhone2)
GO


Have any ideas? Thanks for any help.
 
W

William \(Bill\) Vaughn

Which parameter is failing?

Ok, just a few thoughts:
1) I would add default values to each parameter in the SP--even if the value is NULL.
2) I would build the Command instance and populate the Parameters collection (completely) before setting the values. With this many parameters you could save time by re-using the instance instead of rebuilding it each time.
3) I would turn on the Profiler and see what's getting sent to SQL Server. This will tell you exactly what's wrong.

hth


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Michael said:
Hi Everyone,
I'm getting the above error when I try to execute a stored proc. I've been
trhough the SP and the vb function and can't find anything missing or
incorrect, unless I missed something. Here is a list of things I've tried:
1. Removed the offending parameter from the SP and VB Function. Everything
works.
2. Made sure that the parameters are using the correct parm type.
3. Made sure that I was not missing a parameter.
4. Made sure that no parameter would get passed in the code.
Here is the function that calls the SP. Its a long procedure, so please bear
with me:
Private Function SaveAdmissionNursingAssessment2(cnSQL As SqlConnection) as
Boolean
'Dim cnSQL As SqlConnection
Dim success as Boolean = True
Dim cmSQL As SqlCommand
Try
'cnSQL = OpenConnection()
if isnew then
cmSQL = New SqlCommand("intake_AddAdminNurseAssessment2", cnSQL)
cmsql.Transaction = SaveTrans
isnew = False
else
cmSQL = New SqlCommand("intake_UpdAdmissionNursingAssessment2", cnSQL)
cmsql.Transaction = SaveTrans
isDirty = False
end if
cmsql.CommandType = CommandType.StoredProcedure
cmsql.Parameters.Add("PatientID", sqldbtype.Varchar, 20 ).Value = fPatientId
cmsql.Parameters.Add("PreparedDate", sqldbtype.SmallDateTime).Value = Now()
'''(Cut down because the 30000 limit in the forum)
if flxFamilyHistory2(4,1) = true then
cmsql.Parameters.Add("fhBleeding", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhBleeding", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("@WhoMeBleedingWho", sqldbtype.Varchar, 20 ).Value
= iif(isdbnull(flxFamilyHistory2(4,3)), "", flxFamilyHistory2(4,3))
if flxFamilyHistory2(5,1) = true then
cmsql.Parameters.Add("fhTuberculosis", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhTuberculosis", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhTuberculosisWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(5,3)
if flxFamilyHistory2(6,1) = true then
cmsql.Parameters.Add("fhNervous", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhNervous", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhNervousWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(6,3)
if flxFamilyHistory2(7,1) = true then
cmsql.Parameters.Add("fhSuicide", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhSuicide", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhSuicideWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(7,3)
if flxFamilyHistory2(8,1) = true then
cmsql.Parameters.Add("fhEpilepsy", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhEpilepsy", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhEpilepsyWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(8,3)
if flxFamilyHistory2(9,1) = true then
cmsql.Parameters.Add("fhAllergy", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhAllergy", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhAllergyWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(9,3)
if flxFamilyHistory2(10,1) = true then
cmsql.Parameters.Add("fhStroke", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhStroke", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhStrokeWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(10,3)
if flxFamilyHistory2(11,1) = true then
cmsql.Parameters.Add("fhArteries", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhArteries", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhArteriesWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(11,3)
if flxFamilyHistory2(12,1) = true then
cmsql.Parameters.Add("fhSickleCellTraits", sqldbtype.SmallInt).Value
= 1
else
cmsql.Parameters.Add("fhSickleCellTraits", sqldbtype.SmallInt).Value
= 0
End If
cmsql.Parameters.Add("fhSickleCellTraitsWho", sqldbtype.Varchar, 20 ).Value
= flxFamilyHistory2(12,3)
if flxFamilyHistory2(13,1) = true then
cmsql.Parameters.Add("fhSickleCellDisease",
sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhSickleCellDisease",
sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhSickleCellDiseaseWho", sqldbtype.Varchar, 20
).Value = flxFamilyHistory2(13, 3)
if flxFamilyHistory2(14,1) = true then
cmsql.Parameters.Add("fhAnemia", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhAnemia", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhAnemiaWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(14,3)
if flxFamilyHistory2(15,1) = true then
cmsql.Parameters.Add("fhHereditaryDefects",
sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhHereditaryDefects",
sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhHereditaryDefectsWho", sqldbtype.Varchar, 20
).Value = flxFamilyHistory2(15,3)
if flxFamilyHistory2(16,1) = true then
cmsql.Parameters.Add("fhRenalDisease", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhRenalDisease", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhRenalDiseaseWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(16,3)
if flxFamilyHistory2(17,1) = true then
cmsql.Parameters.Add("fhHEP", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhHEP", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhHEPWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(17,3)
if flxFamilyHistory2(18,1) = true then
cmsql.Parameters.Add("fhHIV", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhHIV", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhHIVWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(18,3)
if flxFamilyHistory2(19,1) = true then
cmsql.Parameters.Add("fhSTD", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhSTD", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhSTDWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(19,3)
if flxFamilyHistory2(20,1) = true then
cmsql.Parameters.Add("fhOtherInfection", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhOtherInfection", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhOtherInfectionWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(20,3)
if flxFamilyHistory2(21,1) = true then
cmsql.Parameters.Add("fhOther", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhOther", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhOtherWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(21,3)
cmsql.Parameters.Add("pmcReason1", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(1,0)
cmsql.Parameters.Add("pmcReason2", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(2,0)
cmsql.Parameters.Add("pmcReason3", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(3,0)
cmsql.Parameters.Add("pmcReason4", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(4,0)
cmsql.Parameters.Add("pmcYear1", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(1,1)
cmsql.Parameters.Add("pmcYear2", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(2,1)
cmsql.Parameters.Add("pmcYear3", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(3,1)
cmsql.Parameters.Add("pmcYear4", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(4,1)
cmsql.Parameters.Add("pmcWhere1", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(1,2)
cmsql.Parameters.Add("pmcWhere2", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(2,2)
cmsql.Parameters.Add("pmcWhere3", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(3,2)
cmsql.Parameters.Add("pmcWhere4", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(4,2)
cmsql.Parameters.Add("pmcDoctor1", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(1,3)
cmsql.Parameters.Add("pmcDoctor2", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(2,3)
cmsql.Parameters.Add("pmcDoctor3", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(3,3)
cmsql.Parameters.Add("pmcDoctor4", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(4,3)
cmsql.Parameters.Add("cdBeenHospitalized", sqldbtype.SmallInt).Value =
chkSeenDoctorYes.Checked
cmsql.Parameters.Add("cdWhereTreated", sqldbtype.Varchar, 200 ).Value =
txtSeenDoctor.Text
cmsql.Parameters.Add("cdCanContact", sqldbtype.SmallInt).Value =
chkCurrentPhysicianYes.Checked
cmsql.Parameters.Add("cdPrivatePhysician", sqldbtype.Varchar, 200 ).Value =
txtCurrentPhysician.Text
cmsql.Parameters.Add("cdContactName1", sqldbtype.Varchar, 100 ).Value =
txtContactName1.Text
cmsql.Parameters.Add("cdContactName2", sqldbtype.Varchar, 100 ).Value =
txtContactName2.Text
cmsql.Parameters.Add("cdContactRelation1", sqldbtype.Varchar, 25 ).Value =
txtRelationship1.Text
cmsql.Parameters.Add("cdContactRelation2", sqldbtype.Varchar, 25 ).Value =
txtRelationship2.Text
cmsql.Parameters.Add("cdContactAddress1", sqldbtype.Varchar, 150 ).Value =
txtContactAddress1.Text
cmsql.Parameters.Add("cdContactAddress2", sqldbtype.Varchar, 150 ).Value =
txtContactAddress2.Text
cmsql.Parameters.Add("cdContactPhone1", sqldbtype.Varchar, 20 ).Value =
txtContactNumber1.Text
cmsql.Parameters.Add("cdContactPhone2", sqldbtype.Varchar, 20 ).Value =
txtContactNumber2.Text
cmSQL.ExecuteNonQuery()
SaveTrans.Commit
Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
SaveTrans.Rollback
success = False
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
SaveTrans.Rollback
success = False
finally
' Close and Clean up objects
'SaveTrans.Commit
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
SaveTrans.Dispose
SaveAdmissionNursingAssessment2 = success
End Try

Here is the stored proc:

CREATE PROCEDURE [dbo].[intake_AddAdminNurseAssessment2]
@PatientID VarChar (10),
@PreparedDate smalldatetime,
@Menarche VarChar (20),
@Cycle VarChar (20),
@Duration VarChar (20),
@PeriodsRegular SmallInt,
@BleedingIntercourse SmallInt,
@ArePeriods SmallInt,
@Pregnancies Int,
@Cramps SmallInt,
@MiscarriageCount Int,
@Heasdaches SmallInt,
@AbortionCount SmallInt,
@LastPapSmear smalldatetime,
@TroublePregnancy SmallInt,
@SpottingPeriods SmallInt,
@NumChildren SmallInt,
@LastPeriod smalldatetime,
@StillBirths SmallInt,
@Premenopausal SmallInt,
@Postmenopausal SmallInt,
@StartMenopausal VarChar (20),
@VaginalDischarge SmallInt,
@DischargeColor VarChar (15),
@DichargeOdor SmallInt,
@PresentlyPregnant SmallInt,
@PresentlyPregnantIfYes SmallInt,
@PreNatalCare VarChar (100),
@BirthControl SmallInt,
@BirthControlIfYes VarChar (50),
@FreqBirthControl SmallInt,
@LastMammogram smalldatetime,
@SelfBreastExam SmallInt,
@TesticularEnlargement SmallInt,
@UrineStream SmallInt,
@PenileLegion SmallInt,
@DischargePenis SmallInt,
@TesticularMasses SmallInt,
@ProstateProblems SmallInt,
@HepatitsB smalldatetime,
@MMR smalldatetime,
@FluVaccine smalldatetime,
@BCG smalldatetime,
@Pneumovax smalldatetime,
@Tetanus smalldatetime,
@ImmunationOther smalldatetime,
@HadSurgery SmallInt,
@Appendectomy SmallInt,
@AppendectomyComment VarChar (50),
@Hysterectomy SmallInt,
@HysterectomyComment VarChar (50),
@Ovarian SmallInt,
@OvarianComment VarChar (50),
@JointReplacement SmallInt,
@JointReplacementComment VarChar (50),
@Gallbladder SmallInt,
@GallbladderComment VarChar (50),
@Bypass SmallInt,
@BypassComment VarChar (50),
@OperationsOther SmallInt,
@OperationsOtherComment VarChar (50),
@Allergies1 VarChar (50),
@Allergies2 VarChar (50),
@Allergies3 VarChar (50),
@Medication1 VarChar (50),
@Medication2 VarChar (50),
@Medication3 VarChar (50),
@scNoProblems SmallInt,
@scFeeding SmallInt,
@scElimination SmallInt,
@scBathing SmallInt,
@scAmbulating SmallInt,
@scDress SmallInt,
@scHomeCare SmallInt,
@scDecribe VarChar (200),
@shSingle SmallInt,
@shMarried SmallInt,
@shSeperated SmallInt,
@shDivorced SmallInt,
@shWidowed SmallInt,
@shSignificantOther SmallInt,
@shWhomLiveWith VarChar (150),
@shIllicitDrugs SmallInt,
@shIllicitDrugsSpecify VarChar (100),
@shCoffee VarChar (5),
@shTea VarChar (5),
@shColas VarChar (5),
@shForeignTravel SmallInt,
@shForeignTravelSpecify varchar(150),
@shFumes SmallInt,
@shFumesSpecify VarChar (50),
@shTimeLostWork VarChar (50),
@paDepression SmallInt,
@paAnxiety SmallInt,
@paHostile SmallInt,
@paAgitate SmallInt,
@paConfused SmallInt,
@paHallucinations SmallInt,
@paVisual SmallInt,
@paAuditory SmallInt,
@paGustatory SmallInt,
@paOlfactory SmallInt,
@paTactile SmallInt,
@Treatment1 VarChar (100),
@Treatment2 VarChar (100),
@Treatment3 VarChar (100),
@Treatment4 VarChar (100),
@Treatment5 VarChar (100),
@Treatment6 VarChar (100),
@Treatment7 VarChar (100),
@PsycMedication1 VarChar (100),
@PsycMedication2 VarChar (100),
@PsycMedication3 VarChar (100),
@PsycMedication4 VarChar (100),
@fhCancer SmallInt,
@fhCancerWho VarChar (20),
@fhGlaucoma SmallInt,
@fhGlaucomaWho VarChar (20),
@fhDiabeted SmallInt,
@fhDiabetedWho VarChar (20),
@fhBleeding SmallInt,
/*
@BleedingWho Varchar(20),
*/
@WhoMeBleedingWho Varchar(20),
@fhTuberculosis SmallInt,
@fhTuberculosisWho VarChar (20),
@fhNervous SmallInt,
@fhNervousWho VarChar (20),
@fhSuicide SmallInt,
@fhSuicideWho VarChar (20),
@fhEpilepsy SmallInt,
@fhEpilepsyWho VarChar (20),
@fhAllergy SmallInt,
@fhAllergyWho VarChar (20),
@fhStroke SmallInt,
@fhStrokeWho VarChar (20),
@fhArteries SmallInt,
@fhArteriesWho VarChar (20),
@fhSickleCellTraits SmallInt,
@fhSickleCellTraitsWho VarChar (20),
@fhSickleCellDisease SmallInt,
@fhSickleCellDiseaseWho VarChar (20),
@fhAnemia SmallInt,
@fhAnemiaWho VarChar (20),
@fhHereditaryDefects SmallInt,
@fhHereditaryDefectsWho VarChar (20),
@fhRenalDisease SmallInt,
@fhRenalDiseaseWho VarChar (20),
@fhHEP SmallInt,
@fhHEPWho VarChar (20),
@fhHIV SmallInt,
@fhHIVWho VarChar (20),
@fhSTD SmallInt,
@fhSTDWho VarChar (20),
@fhOtherInfection SmallInt,
@fhOtherInfectionWho VarChar (20),
@fhOther SmallInt,
@fhOtherWho VarChar (20),
@pmcReason1 VarChar (50),
@pmcReason2 VarChar (50),
@pmcReason3 VarChar (50),
@pmcReason4 VarChar (50),
@pmcYear1 VarChar (10),
@pmcYear2 VarChar (10),
@pmcYear3 VarChar (10),
@pmcYear4 VarChar (10),
@pmcWhere1 VarChar (25),
@pmcWhere2 VarChar (25),
@pmcWhere3 VarChar (25),
@pmcWhere4 VarChar (25),
@pmcDoctor1 VarChar (25),
@pmcDoctor2 VarChar (25),
@pmcDoctor3 VarChar (25),
@pmcDoctor4 VarChar (25),
@cdBeenHospitalized SmallInt,
@cdWhereTreated VarChar (200),
@cdCanContact SmallInt,
@cdPrivatePhysician VarChar (200),
@cdContactName1 VarChar (100),
@cdContactName2 VarChar (100),
@cdContactRelation1 VarChar (25),
@cdContactRelation2 VarChar (25),
@cdContactAddress1 VarChar (150),
@cdContactAddress2 VarChar (150),
@cdContactPhone1 VarChar (20),
@cdContactPhone2 VarChar (20)
AS

Declare @LastValue int

Set @LastValue = (Select AdminNurseAsses2 from Support)
set @LastValue = @LastValue + 1
Update Support
Set AdminNurseAsses2 = @LastValue

Insert into AdmissionNursingAssessment2(
KeyId, PatientID, PreparedDate, Menarche, Cycle, Duration, PeriodsRegular,
BleedingIntercourse, ArePeriods, Pregnancies, Cramps,
MiscarriageCount, Heasdaches, AbortionCount, LastPapSmear, TroublePregnancy,
SpottingPeriods, NumChildren, LastPeriod, StillBirths,
Premenopausal, Postmenopausal, StartMenopausal, VaginalDischarge,
DischargeColor, DichargeOdor, PresentlyPregnant, PresentlyPregnantIfYes,
PreNatalCare, BirthControl, BirthControlIfYes, FreqBirthControl,
LastMammogram, SelfBreastExam, TesticularEnlargement, UrineStream,
PenileLegion,
DischargePenis, TesticularMasses, ProstateProblems, HepatitsB, MMR,
FluVaccine, BCG, Pneumovax, Tetanus, ImmunationOther, HadSurgery,
Appendectomy, AppendectomyComment, Hysterectomy, HysterectomyComment,
Ovarian, OvarianComment, JointReplacement, JointReplacementComment,
Gallbladder, GallbladderComment, Bypass, BypassComment, OperationsOther,
OperationsOtherComment, Allergies1, Allergies2, Allergies3, Medication1,
Medication2, Medication3, scNoProblems, scFeeding, scElimination, scBathing,
scAmbulating, scDress, scHomeCare, scDecribe, shSingle, shMarried,
shSeperated, shDivorced, shWidowed, shSignificantOther, shWhomLiveWith,
shIllicitDrugs, shIllicitDrugsSpecify, shCoffee, shTea, shColas,
shForeignTravel, shForeignTravelSpecify, shFumes, shFumesSpecify,
shTimeLostWork, paDepression, paAnxiety, paHostile, paAgitate, paConfused,
paHallucinations,
paVisual, paAuditory, paGustatory, paOlfactory, paTactile, Treatment1,
Treatment2, Treatment3, Treatment4, Treatment5, Treatment6, Treatment7,
PsycMedication1, PsycMedication2, PsycMedication3, PsycMedication4,
fhCancer, fhCancerWho, fhGlaucoma, fhGlaucomaWho, fhDiabeted,
fhDiabetedWho, fhBleeding,
fhBleedingWho,
fhTuberculosis, fhTuberculosisWho, fhNervous, fhNervousWho, fhSuicide,
fhSuicideWho, fhEpilepsy,
fhEpilepsyWho, fhAllergy, fhAllergyWho, fhStroke, fhStrokeWho, fhArteries,
fhArteriesWho, fhSickleCellTraits, fhSickleCellTraitsWho, fhSickleCellDisease,
fhSickleCellDiseaseWho, fhAnemia, fhAnemiaWho, fhHereditaryDefects,
fhHereditaryDefectsWho, fhRenalDisease, fhRenalDiseaseWho, fhHEP,
fhHEPWho, fhHIV, fhHIVWho, fhSTD, fhSTDWho, fhOtherInfection,
fhOtherInfectionWho, fhOther, fhOtherWho, pmcReason1, pmcReason2,
pmcReason3, pmcReason4, pmcYear1, pmcYear2, pmcYear3, pmcYear4, pmcWhere1,
pmcWhere2, pmcWhere3, pmcWhere4, pmcDoctor1,
pmcDoctor2, pmcDoctor3, pmcDoctor4, cdBeenHospitalized, cdWhereTreated,
cdCanContact, cdPrivatePhysician, cdContactName1, cdContactName2,
cdContactRelation1, cdContactRelation2, cdContactAddress1,
cdContactAddress2, cdContactPhone1, cdContactPhone2) Values(
@LastValue, @PatientID, @PreparedDate, @Menarche, @Cycle, @Duration,
@PeriodsRegular, @BleedingIntercourse, @ArePeriods,
@Pregnancies, @Cramps, @MiscarriageCount, @Heasdaches, @AbortionCount,
@LastPapSmear, @TroublePregnancy, @SpottingPeriods,
@NumChildren, @LastPeriod, @StillBirths, @Premenopausal, @Postmenopausal,
@StartMenopausal, @VaginalDischarge, @DischargeColor,
@DichargeOdor, @PresentlyPregnant, @PresentlyPregnantIfYes, @PreNatalCare,
@BirthControl, @BirthControlIfYes, @FreqBirthControl,
@LastMammogram, @SelfBreastExam, @TesticularEnlargement, @UrineStream,
@PenileLegion, @DischargePenis, @TesticularMasses,
@ProstateProblems, @HepatitsB, @MMR, @FluVaccine, @BCG, @Pneumovax,
@Tetanus, @ImmunationOther, @HadSurgery, @Appendectomy,
@AppendectomyComment, @Hysterectomy, @HysterectomyComment, @Ovarian,
@OvarianComment, @JointReplacement, @JointReplacementComment,
@Gallbladder, @GallbladderComment, @Bypass, @BypassComment,
@OperationsOther, @OperationsOtherComment, @Allergies1, @Allergies2,
@Allergies3, @Medication1, @Medication2, @Medication3, @scNoProblems,
@scFeeding, @scElimination, @scBathing, @scAmbulating, @scDress,
@scHomeCare, @scDecribe, @shSingle, @shMarried, @shSeperated, @shDivorced,
@shWidowed, @shSignificantOther, @shWhomLiveWith,
@shIllicitDrugs, @shIllicitDrugsSpecify, @shCoffee, @shTea, @shColas,
@shForeignTravel, @shForeignTravelSpecify, @shFumes, @shFumesSpecify,
@shTimeLostWork,
@paDepression, @paAnxiety, @paHostile, @paAgitate, @paConfused,
@paHallucinations, @paVisual, @paAuditory, @paGustatory, @paOlfactory,
@paTactile, @Treatment1, @Treatment2, @Treatment3, @Treatment4, @Treatment5,
@Treatment6, @Treatment7, @PsycMedication1,
@PsycMedication2, @PsycMedication3, @PsycMedication4, @fhCancer,
@fhCancerWho, @fhGlaucoma, @fhGlaucomaWho, @fhDiabeted,
@fhDiabetedWho, @fhBleeding,
@WhoMeBleedingWho,
@fhTuberculosis, @fhTuberculosisWho, @fhNervous, @fhNervousWho, @fhSuicide,
@fhSuicideWho, @fhEpilepsy, @fhEpilepsyWho, @fhAllergy, @fhAllergyWho,
@fhStroke, @fhStrokeWho, @fhArteries, @fhArteriesWho,
@fhSickleCellTraits, @fhSickleCellTraitsWho, @fhSickleCellDisease,
@fhSickleCellDiseaseWho, @fhAnemia, @fhAnemiaWho, @fhHereditaryDefects,
@fhHereditaryDefectsWho, @fhRenalDisease, @fhRenalDiseaseWho, @fhHEP,
@fhHEPWho, @fhHIV, @fhHIVWho, @fhSTD, @fhSTDWho,
@fhOtherInfection, @fhOtherInfectionWho, @fhOther, @fhOtherWho, @pmcReason1,
@pmcReason2, @pmcReason3, @pmcReason4, @pmcYear1,
@pmcYear2, @pmcYear3, @pmcYear4, @pmcWhere1, @pmcWhere2, @pmcWhere3,
@pmcWhere4, @pmcDoctor1, @pmcDoctor2, @pmcDoctor3,
@pmcDoctor4, @cdBeenHospitalized, @cdWhereTreated, @cdCanContact,
@cdPrivatePhysician, @cdContactName1, @cdContactName2,
@cdContactRelation1, @cdContactRelation2, @cdContactAddress1,
@cdContactAddress2, @cdContactPhone1, @cdContactPhone2)
GO


Have any ideas? Thanks for any help.
Click to expand...
 
P

Pablo Castro [MS]

It might be that one of the .Value properties of one of the parameters ends
up with null (Nothing in VB) value (e.g. the return value from some function
or something like that). Null/Nothing means "value not set by the client,
use the server's default", which fails if there is no default. If you
actually want a database null when the value in your app is null/nothing,
then you'll need to check for null in your function calls and if null set
the value to DBNull.Value.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.


Michael said:
Hi Everyone,
I'm getting the above error when I try to execute a stored proc. I've been
trhough the SP and the vb function and can't find anything missing or
incorrect, unless I missed something. Here is a list of things I've tried:
1. Removed the offending parameter from the SP and VB Function. Everything
works.
2. Made sure that the parameters are using the correct parm type.
3. Made sure that I was not missing a parameter.
4. Made sure that no parameter would get passed in the code.
Here is the function that calls the SP. Its a long procedure, so please
bear
with me:
Private Function SaveAdmissionNursingAssessment2(cnSQL As SqlConnection)
as
Boolean
'Dim cnSQL As SqlConnection
Dim success as Boolean = True
Dim cmSQL As SqlCommand
Try
'cnSQL = OpenConnection()
if isnew then
cmSQL = New SqlCommand("intake_AddAdminNurseAssessment2", cnSQL)
cmsql.Transaction = SaveTrans
isnew = False
else
cmSQL = New SqlCommand("intake_UpdAdmissionNursingAssessment2", cnSQL)
cmsql.Transaction = SaveTrans
isDirty = False
end if
cmsql.CommandType = CommandType.StoredProcedure
cmsql.Parameters.Add("PatientID", sqldbtype.Varchar, 20 ).Value =
fPatientId
cmsql.Parameters.Add("PreparedDate", sqldbtype.SmallDateTime).Value =
Now()
'''(Cut down because the 30000 limit in the forum)
if flxFamilyHistory2(4,1) = true then
cmsql.Parameters.Add("fhBleeding", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhBleeding", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("@WhoMeBleedingWho", sqldbtype.Varchar, 20 ).Value
= iif(isdbnull(flxFamilyHistory2(4,3)), "", flxFamilyHistory2(4,3))
if flxFamilyHistory2(5,1) = true then
cmsql.Parameters.Add("fhTuberculosis", sqldbtype.SmallInt).Value =
1
else
cmsql.Parameters.Add("fhTuberculosis", sqldbtype.SmallInt).Value =
0
End If
cmsql.Parameters.Add("fhTuberculosisWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(5,3)
if flxFamilyHistory2(6,1) = true then
cmsql.Parameters.Add("fhNervous", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhNervous", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhNervousWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(6,3)
if flxFamilyHistory2(7,1) = true then
cmsql.Parameters.Add("fhSuicide", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhSuicide", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhSuicideWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(7,3)
if flxFamilyHistory2(8,1) = true then
cmsql.Parameters.Add("fhEpilepsy", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhEpilepsy", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhEpilepsyWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(8,3)
if flxFamilyHistory2(9,1) = true then
cmsql.Parameters.Add("fhAllergy", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhAllergy", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhAllergyWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(9,3)
if flxFamilyHistory2(10,1) = true then
cmsql.Parameters.Add("fhStroke", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhStroke", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhStrokeWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(10,3)
if flxFamilyHistory2(11,1) = true then
cmsql.Parameters.Add("fhArteries", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhArteries", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhArteriesWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(11,3)
if flxFamilyHistory2(12,1) = true then
cmsql.Parameters.Add("fhSickleCellTraits",
sqldbtype.SmallInt).Value
= 1
else
cmsql.Parameters.Add("fhSickleCellTraits",
sqldbtype.SmallInt).Value
= 0
End If
cmsql.Parameters.Add("fhSickleCellTraitsWho", sqldbtype.Varchar,
20 ).Value
= flxFamilyHistory2(12,3)
if flxFamilyHistory2(13,1) = true then
cmsql.Parameters.Add("fhSickleCellDisease",
sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhSickleCellDisease",
sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhSickleCellDiseaseWho", sqldbtype.Varchar, 20
).Value = flxFamilyHistory2(13, 3)
if flxFamilyHistory2(14,1) = true then
cmsql.Parameters.Add("fhAnemia", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhAnemia", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhAnemiaWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(14,3)
if flxFamilyHistory2(15,1) = true then
cmsql.Parameters.Add("fhHereditaryDefects",
sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhHereditaryDefects",
sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhHereditaryDefectsWho", sqldbtype.Varchar, 20
).Value = flxFamilyHistory2(15,3)
if flxFamilyHistory2(16,1) = true then
cmsql.Parameters.Add("fhRenalDisease", sqldbtype.SmallInt).Value =
1
else
cmsql.Parameters.Add("fhRenalDisease", sqldbtype.SmallInt).Value =
0
End If
cmsql.Parameters.Add("fhRenalDiseaseWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(16,3)
if flxFamilyHistory2(17,1) = true then
cmsql.Parameters.Add("fhHEP", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhHEP", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhHEPWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(17,3)
if flxFamilyHistory2(18,1) = true then
cmsql.Parameters.Add("fhHIV", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhHIV", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhHIVWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(18,3)
if flxFamilyHistory2(19,1) = true then
cmsql.Parameters.Add("fhSTD", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhSTD", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhSTDWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(19,3)
if flxFamilyHistory2(20,1) = true then
cmsql.Parameters.Add("fhOtherInfection", sqldbtype.SmallInt).Value
= 1
else
cmsql.Parameters.Add("fhOtherInfection", sqldbtype.SmallInt).Value
= 0
End If
cmsql.Parameters.Add("fhOtherInfectionWho", sqldbtype.Varchar, 20 ).Value
=
flxFamilyHistory2(20,3)
if flxFamilyHistory2(21,1) = true then
cmsql.Parameters.Add("fhOther", sqldbtype.SmallInt).Value = 1
else
cmsql.Parameters.Add("fhOther", sqldbtype.SmallInt).Value = 0
End If
cmsql.Parameters.Add("fhOtherWho", sqldbtype.Varchar, 20 ).Value =
flxFamilyHistory2(21,3)
cmsql.Parameters.Add("pmcReason1", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(1,0)
cmsql.Parameters.Add("pmcReason2", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(2,0)
cmsql.Parameters.Add("pmcReason3", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(3,0)
cmsql.Parameters.Add("pmcReason4", sqldbtype.Varchar, 50 ).Value =
grdPrevMedicalCare(4,0)
cmsql.Parameters.Add("pmcYear1", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(1,1)
cmsql.Parameters.Add("pmcYear2", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(2,1)
cmsql.Parameters.Add("pmcYear3", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(3,1)
cmsql.Parameters.Add("pmcYear4", sqldbtype.Varchar, 10 ).Value =
grdPrevMedicalCare(4,1)
cmsql.Parameters.Add("pmcWhere1", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(1,2)
cmsql.Parameters.Add("pmcWhere2", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(2,2)
cmsql.Parameters.Add("pmcWhere3", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(3,2)
cmsql.Parameters.Add("pmcWhere4", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(4,2)
cmsql.Parameters.Add("pmcDoctor1", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(1,3)
cmsql.Parameters.Add("pmcDoctor2", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(2,3)
cmsql.Parameters.Add("pmcDoctor3", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(3,3)
cmsql.Parameters.Add("pmcDoctor4", sqldbtype.Varchar, 25 ).Value =
grdPrevMedicalCare(4,3)
cmsql.Parameters.Add("cdBeenHospitalized", sqldbtype.SmallInt).Value =
chkSeenDoctorYes.Checked
cmsql.Parameters.Add("cdWhereTreated", sqldbtype.Varchar, 200 ).Value =
txtSeenDoctor.Text
cmsql.Parameters.Add("cdCanContact", sqldbtype.SmallInt).Value =
chkCurrentPhysicianYes.Checked
cmsql.Parameters.Add("cdPrivatePhysician", sqldbtype.Varchar, 200 ).Value
=
txtCurrentPhysician.Text
cmsql.Parameters.Add("cdContactName1", sqldbtype.Varchar, 100 ).Value =
txtContactName1.Text
cmsql.Parameters.Add("cdContactName2", sqldbtype.Varchar, 100 ).Value =
txtContactName2.Text
cmsql.Parameters.Add("cdContactRelation1", sqldbtype.Varchar, 25 ).Value =
txtRelationship1.Text
cmsql.Parameters.Add("cdContactRelation2", sqldbtype.Varchar, 25 ).Value =
txtRelationship2.Text
cmsql.Parameters.Add("cdContactAddress1", sqldbtype.Varchar, 150 ).Value =
txtContactAddress1.Text
cmsql.Parameters.Add("cdContactAddress2", sqldbtype.Varchar, 150 ).Value =
txtContactAddress2.Text
cmsql.Parameters.Add("cdContactPhone1", sqldbtype.Varchar, 20 ).Value =
txtContactNumber1.Text
cmsql.Parameters.Add("cdContactPhone2", sqldbtype.Varchar, 20 ).Value =
txtContactNumber2.Text
cmSQL.ExecuteNonQuery()
SaveTrans.Commit
Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
SaveTrans.Rollback
success = False
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
SaveTrans.Rollback
success = False
finally
' Close and Clean up objects
'SaveTrans.Commit
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
SaveTrans.Dispose
SaveAdmissionNursingAssessment2 = success
End Try

Here is the stored proc:

CREATE PROCEDURE [dbo].[intake_AddAdminNurseAssessment2]
@PatientID VarChar (10),
@PreparedDate smalldatetime,
@Menarche VarChar (20),
@Cycle VarChar (20),
@Duration VarChar (20),
@PeriodsRegular SmallInt,
@BleedingIntercourse SmallInt,
@ArePeriods SmallInt,
@Pregnancies Int,
@Cramps SmallInt,
@MiscarriageCount Int,
@Heasdaches SmallInt,
@AbortionCount SmallInt,
@LastPapSmear smalldatetime,
@TroublePregnancy SmallInt,
@SpottingPeriods SmallInt,
@NumChildren SmallInt,
@LastPeriod smalldatetime,
@StillBirths SmallInt,
@Premenopausal SmallInt,
@Postmenopausal SmallInt,
@StartMenopausal VarChar (20),
@VaginalDischarge SmallInt,
@DischargeColor VarChar (15),
@DichargeOdor SmallInt,
@PresentlyPregnant SmallInt,
@PresentlyPregnantIfYes SmallInt,
@PreNatalCare VarChar (100),
@BirthControl SmallInt,
@BirthControlIfYes VarChar (50),
@FreqBirthControl SmallInt,
@LastMammogram smalldatetime,
@SelfBreastExam SmallInt,
@TesticularEnlargement SmallInt,
@UrineStream SmallInt,
@PenileLegion SmallInt,
@DischargePenis SmallInt,
@TesticularMasses SmallInt,
@ProstateProblems SmallInt,
@HepatitsB smalldatetime,
@MMR smalldatetime,
@FluVaccine smalldatetime,
@BCG smalldatetime,
@Pneumovax smalldatetime,
@Tetanus smalldatetime,
@ImmunationOther smalldatetime,
@HadSurgery SmallInt,
@Appendectomy SmallInt,
@AppendectomyComment VarChar (50),
@Hysterectomy SmallInt,
@HysterectomyComment VarChar (50),
@Ovarian SmallInt,
@OvarianComment VarChar (50),
@JointReplacement SmallInt,
@JointReplacementComment VarChar (50),
@Gallbladder SmallInt,
@GallbladderComment VarChar (50),
@Bypass SmallInt,
@BypassComment VarChar (50),
@OperationsOther SmallInt,
@OperationsOtherComment VarChar (50),
@Allergies1 VarChar (50),
@Allergies2 VarChar (50),
@Allergies3 VarChar (50),
@Medication1 VarChar (50),
@Medication2 VarChar (50),
@Medication3 VarChar (50),
@scNoProblems SmallInt,
@scFeeding SmallInt,
@scElimination SmallInt,
@scBathing SmallInt,
@scAmbulating SmallInt,
@scDress SmallInt,
@scHomeCare SmallInt,
@scDecribe VarChar (200),
@shSingle SmallInt,
@shMarried SmallInt,
@shSeperated SmallInt,
@shDivorced SmallInt,
@shWidowed SmallInt,
@shSignificantOther SmallInt,
@shWhomLiveWith VarChar (150),
@shIllicitDrugs SmallInt,
@shIllicitDrugsSpecify VarChar (100),
@shCoffee VarChar (5),
@shTea VarChar (5),
@shColas VarChar (5),
@shForeignTravel SmallInt,
@shForeignTravelSpecify varchar(150),
@shFumes SmallInt,
@shFumesSpecify VarChar (50),
@shTimeLostWork VarChar (50),
@paDepression SmallInt,
@paAnxiety SmallInt,
@paHostile SmallInt,
@paAgitate SmallInt,
@paConfused SmallInt,
@paHallucinations SmallInt,
@paVisual SmallInt,
@paAuditory SmallInt,
@paGustatory SmallInt,
@paOlfactory SmallInt,
@paTactile SmallInt,
@Treatment1 VarChar (100),
@Treatment2 VarChar (100),
@Treatment3 VarChar (100),
@Treatment4 VarChar (100),
@Treatment5 VarChar (100),
@Treatment6 VarChar (100),
@Treatment7 VarChar (100),
@PsycMedication1 VarChar (100),
@PsycMedication2 VarChar (100),
@PsycMedication3 VarChar (100),
@PsycMedication4 VarChar (100),
@fhCancer SmallInt,
@fhCancerWho VarChar (20),
@fhGlaucoma SmallInt,
@fhGlaucomaWho VarChar (20),
@fhDiabeted SmallInt,
@fhDiabetedWho VarChar (20),
@fhBleeding SmallInt,
/*
@BleedingWho Varchar(20),
*/
@WhoMeBleedingWho Varchar(20),
@fhTuberculosis SmallInt,
@fhTuberculosisWho VarChar (20),
@fhNervous SmallInt,
@fhNervousWho VarChar (20),
@fhSuicide SmallInt,
@fhSuicideWho VarChar (20),
@fhEpilepsy SmallInt,
@fhEpilepsyWho VarChar (20),
@fhAllergy SmallInt,
@fhAllergyWho VarChar (20),
@fhStroke SmallInt,
@fhStrokeWho VarChar (20),
@fhArteries SmallInt,
@fhArteriesWho VarChar (20),
@fhSickleCellTraits SmallInt,
@fhSickleCellTraitsWho VarChar (20),
@fhSickleCellDisease SmallInt,
@fhSickleCellDiseaseWho VarChar (20),
@fhAnemia SmallInt,
@fhAnemiaWho VarChar (20),
@fhHereditaryDefects SmallInt,
@fhHereditaryDefectsWho VarChar (20),
@fhRenalDisease SmallInt,
@fhRenalDiseaseWho VarChar (20),
@fhHEP SmallInt,
@fhHEPWho VarChar (20),
@fhHIV SmallInt,
@fhHIVWho VarChar (20),
@fhSTD SmallInt,
@fhSTDWho VarChar (20),
@fhOtherInfection SmallInt,
@fhOtherInfectionWho VarChar (20),
@fhOther SmallInt,
@fhOtherWho VarChar (20),
@pmcReason1 VarChar (50),
@pmcReason2 VarChar (50),
@pmcReason3 VarChar (50),
@pmcReason4 VarChar (50),
@pmcYear1 VarChar (10),
@pmcYear2 VarChar (10),
@pmcYear3 VarChar (10),
@pmcYear4 VarChar (10),
@pmcWhere1 VarChar (25),
@pmcWhere2 VarChar (25),
@pmcWhere3 VarChar (25),
@pmcWhere4 VarChar (25),
@pmcDoctor1 VarChar (25),
@pmcDoctor2 VarChar (25),
@pmcDoctor3 VarChar (25),
@pmcDoctor4 VarChar (25),
@cdBeenHospitalized SmallInt,
@cdWhereTreated VarChar (200),
@cdCanContact SmallInt,
@cdPrivatePhysician VarChar (200),
@cdContactName1 VarChar (100),
@cdContactName2 VarChar (100),
@cdContactRelation1 VarChar (25),
@cdContactRelation2 VarChar (25),
@cdContactAddress1 VarChar (150),
@cdContactAddress2 VarChar (150),
@cdContactPhone1 VarChar (20),
@cdContactPhone2 VarChar (20)
AS

Declare @LastValue int

Set @LastValue = (Select AdminNurseAsses2 from Support)
set @LastValue = @LastValue + 1
Update Support
Set AdminNurseAsses2 = @LastValue

Insert into AdmissionNursingAssessment2(
KeyId, PatientID, PreparedDate, Menarche, Cycle, Duration,
PeriodsRegular,
BleedingIntercourse, ArePeriods, Pregnancies, Cramps,
MiscarriageCount, Heasdaches, AbortionCount, LastPapSmear,
TroublePregnancy,
SpottingPeriods, NumChildren, LastPeriod, StillBirths,
Premenopausal, Postmenopausal, StartMenopausal, VaginalDischarge,
DischargeColor, DichargeOdor, PresentlyPregnant, PresentlyPregnantIfYes,
PreNatalCare, BirthControl, BirthControlIfYes, FreqBirthControl,
LastMammogram, SelfBreastExam, TesticularEnlargement, UrineStream,
PenileLegion,
DischargePenis, TesticularMasses, ProstateProblems, HepatitsB, MMR,
FluVaccine, BCG, Pneumovax, Tetanus, ImmunationOther, HadSurgery,
Appendectomy, AppendectomyComment, Hysterectomy, HysterectomyComment,
Ovarian, OvarianComment, JointReplacement, JointReplacementComment,
Gallbladder, GallbladderComment, Bypass, BypassComment, OperationsOther,
OperationsOtherComment, Allergies1, Allergies2, Allergies3, Medication1,
Medication2, Medication3, scNoProblems, scFeeding, scElimination,
scBathing,
scAmbulating, scDress, scHomeCare, scDecribe, shSingle, shMarried,
shSeperated, shDivorced, shWidowed, shSignificantOther, shWhomLiveWith,
shIllicitDrugs, shIllicitDrugsSpecify, shCoffee, shTea, shColas,
shForeignTravel, shForeignTravelSpecify, shFumes, shFumesSpecify,
shTimeLostWork, paDepression, paAnxiety, paHostile, paAgitate, paConfused,
paHallucinations,
paVisual, paAuditory, paGustatory, paOlfactory, paTactile, Treatment1,
Treatment2, Treatment3, Treatment4, Treatment5, Treatment6, Treatment7,
PsycMedication1, PsycMedication2, PsycMedication3, PsycMedication4,
fhCancer, fhCancerWho, fhGlaucoma, fhGlaucomaWho, fhDiabeted,
fhDiabetedWho, fhBleeding,
fhBleedingWho,
fhTuberculosis, fhTuberculosisWho, fhNervous, fhNervousWho, fhSuicide,
fhSuicideWho, fhEpilepsy,
fhEpilepsyWho, fhAllergy, fhAllergyWho, fhStroke, fhStrokeWho, fhArteries,
fhArteriesWho, fhSickleCellTraits, fhSickleCellTraitsWho,
fhSickleCellDisease,
fhSickleCellDiseaseWho, fhAnemia, fhAnemiaWho, fhHereditaryDefects,
fhHereditaryDefectsWho, fhRenalDisease, fhRenalDiseaseWho, fhHEP,
fhHEPWho, fhHIV, fhHIVWho, fhSTD, fhSTDWho, fhOtherInfection,
fhOtherInfectionWho, fhOther, fhOtherWho, pmcReason1, pmcReason2,
pmcReason3, pmcReason4, pmcYear1, pmcYear2, pmcYear3, pmcYear4, pmcWhere1,
pmcWhere2, pmcWhere3, pmcWhere4, pmcDoctor1,
pmcDoctor2, pmcDoctor3, pmcDoctor4, cdBeenHospitalized, cdWhereTreated,
cdCanContact, cdPrivatePhysician, cdContactName1, cdContactName2,
cdContactRelation1, cdContactRelation2, cdContactAddress1,
cdContactAddress2, cdContactPhone1, cdContactPhone2) Values(
@LastValue, @PatientID, @PreparedDate, @Menarche, @Cycle, @Duration,
@PeriodsRegular, @BleedingIntercourse, @ArePeriods,
@Pregnancies, @Cramps, @MiscarriageCount, @Heasdaches, @AbortionCount,
@LastPapSmear, @TroublePregnancy, @SpottingPeriods,
@NumChildren, @LastPeriod, @StillBirths, @Premenopausal, @Postmenopausal,
@StartMenopausal, @VaginalDischarge, @DischargeColor,
@DichargeOdor, @PresentlyPregnant, @PresentlyPregnantIfYes, @PreNatalCare,
@BirthControl, @BirthControlIfYes, @FreqBirthControl,
@LastMammogram, @SelfBreastExam, @TesticularEnlargement, @UrineStream,
@PenileLegion, @DischargePenis, @TesticularMasses,
@ProstateProblems, @HepatitsB, @MMR, @FluVaccine, @BCG, @Pneumovax,
@Tetanus, @ImmunationOther, @HadSurgery, @Appendectomy,
@AppendectomyComment, @Hysterectomy, @HysterectomyComment, @Ovarian,
@OvarianComment, @JointReplacement, @JointReplacementComment,
@Gallbladder, @GallbladderComment, @Bypass, @BypassComment,
@OperationsOther, @OperationsOtherComment, @Allergies1, @Allergies2,
@Allergies3, @Medication1, @Medication2, @Medication3, @scNoProblems,
@scFeeding, @scElimination, @scBathing, @scAmbulating, @scDress,
@scHomeCare, @scDecribe, @shSingle, @shMarried, @shSeperated, @shDivorced,
@shWidowed, @shSignificantOther, @shWhomLiveWith,
@shIllicitDrugs, @shIllicitDrugsSpecify, @shCoffee, @shTea, @shColas,
@shForeignTravel, @shForeignTravelSpecify, @shFumes, @shFumesSpecify,
@shTimeLostWork,
@paDepression, @paAnxiety, @paHostile, @paAgitate, @paConfused,
@paHallucinations, @paVisual, @paAuditory, @paGustatory, @paOlfactory,
@paTactile, @Treatment1, @Treatment2, @Treatment3, @Treatment4,
@Treatment5,
@Treatment6, @Treatment7, @PsycMedication1,
@PsycMedication2, @PsycMedication3, @PsycMedication4, @fhCancer,
@fhCancerWho, @fhGlaucoma, @fhGlaucomaWho, @fhDiabeted,
@fhDiabetedWho, @fhBleeding,
@WhoMeBleedingWho,
@fhTuberculosis, @fhTuberculosisWho, @fhNervous, @fhNervousWho,
@fhSuicide,
@fhSuicideWho, @fhEpilepsy, @fhEpilepsyWho, @fhAllergy, @fhAllergyWho,
@fhStroke, @fhStrokeWho, @fhArteries, @fhArteriesWho,
@fhSickleCellTraits, @fhSickleCellTraitsWho, @fhSickleCellDisease,
@fhSickleCellDiseaseWho, @fhAnemia, @fhAnemiaWho, @fhHereditaryDefects,
@fhHereditaryDefectsWho, @fhRenalDisease, @fhRenalDiseaseWho, @fhHEP,
@fhHEPWho, @fhHIV, @fhHIVWho, @fhSTD, @fhSTDWho,
@fhOtherInfection, @fhOtherInfectionWho, @fhOther, @fhOtherWho,
@pmcReason1,
@pmcReason2, @pmcReason3, @pmcReason4, @pmcYear1,
@pmcYear2, @pmcYear3, @pmcYear4, @pmcWhere1, @pmcWhere2, @pmcWhere3,
@pmcWhere4, @pmcDoctor1, @pmcDoctor2, @pmcDoctor3,
@pmcDoctor4, @cdBeenHospitalized, @cdWhereTreated, @cdCanContact,
@cdPrivatePhysician, @cdContactName1, @cdContactName2,
@cdContactRelation1, @cdContactRelation2, @cdContactAddress1,
@cdContactAddress2, @cdContactPhone1, @cdContactPhone2)
GO


Have any ideas? Thanks for any help.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why am I getting ForeignKeyConstraint requires child key value error? 12
Error: Failed to convert parameter value from a String to a Byte[]. (Need Help Urgently) 0
Prepared statement expects parameter which was not supplied 1
Error - procedure expects parameter, which was not supplied 1
Prepared statement '(@ID int...,' expects parameter @ID, which was not supplied 2

Top