[RESOLVED] Confused - date update causing - Row cannot be located for updating

[anneq]

Hi,

I'm Using ADODB with mySQL. When updating if I exclude two dates on the update then all is ok, But if I changed them with the data from the screen and the recordset.update. I get the error that data has changed since you found it. I was wondering if anybody could help me out on a direction to look in, as I can't figure out why its the dates that are causing the issue. I am opening my recordset as follows

pRs.Open SQL1, gCnn, adOpenStatic, adLockOptimistic, adCmdText

thanks

Anne

[Static]

whats in SQL1?

(lets see the SQL )

[anneq]

SQL1 = "Select transplant.TDCSNO,transplant.TransplantNo,transplant.TransplantDate,transplant.TransplantStatus," _
         & "DonorHospital," _
         & "DonorCauseofDeath, DonorDOB,DonorAge,DonorAgeType," _
         & "DonorSex, DonorAOB,Pressors,ColdIshaemicTm," _
         & "KidneyUsed,ReferringCentre,TransplantPool," _
         & "DateofFailure,CauseofFailure,DateofDeath,CauseofDeath," _
         & "PancreasFunctioning,DatePancreasFailure," _
         & "CausePancreasFailure,DatePancreasObsFunctioning,EsrdCode,NoDat," _
         & "CMVRecipient,CMVDonor,Pairing," _
         & "CMVInfection,CMVDate,BioTmToZero,BioGs,BioIF,Biopsy," _
         & "AtnInterval,PoorClearence,DiabeticDet," _
         & "LastCreat3,LastCreat2,LastCreat1,LastCreatYr3,LastCreatYr2,LastCreatYr1,CompDetails, " _
         & "ReferringCenterCo,TreatmentCenter,TreatmentCenterCo,TreatmentCommenced,FollowupCenter, " _
         & "FollowUpCenterCo,ReferringCons,FollowupCons,FollowUpMrn,ReferringMrn, " _
         & "AddFailDetails,AddDeathDetails,InPatStay,PregnantDt,PregPreTx,BhCons,Rooms, " _
         & "AgeAtTx, AgeTypeAtTx, DonorHospitalCo,ESRDDxSubtype,EDTADX, " _
         & "DonorDetails, DrugDetails, ImmunoDetails,CreatDetails,PancreasDetails " _
         & " From transplant  " _
         & " where transplant.TDCSNo = '" & Trim$(TDCSPass) & "'" _
         & " and transplant.TransplantNo = '" & Trim$(TransplantNoPass) & "'"

I set the fields with the data from the screen as follows, If I comment out these lines then all is OK

[Highlight=VB]
rs.Fields("DateofFailure") = mskDtFail.Text

rs.Fields("DateofDeath") = mskDateDeath.Text
rs.update

[VBCODE/]

Does this help...If the data is not Null in the database then it updates OK, if the data is Null then it seems to have the problem.
thanks

Anne

[Static]

WOW thats quite the SQL statement

ok..
if th DB is NULL in the date fields it wont update the date fields..
but if its NOT NULL in the date fields it will update them???

what if u try this?

rs.Fields("DateofFailure") = "" & mskDtFail.Text
 
rs.Fields("DateofDeath") = "" & mskDateDeath.Text

seems odd.. ive run into errors trying to put null values IN but not updating NULL values...

[anneq]

Thanks but no luck.... The default in the database is 0000-00-00 although the default value is null - would this have anything to do with it...

Anne

[Static]

I wouldnt think it would make any difference...

is the Date in the textbox formatted properly?

rs.Fields("DateofFailure") = format(mskDtFail.Text,"yyyy-mm-dd")

[anneq]

Hiya,

I set the date in the database to null (via sql) and now it works. If anybody comes accross this post and can let me know why I would be greatful. But for now I must close the post and go update mytables to null

thanks for all your help

Anne