[mouse88]

Im having some difficulties with this exception. I was going to give up with my project but then i found an e-book and followed it exactly but still with no luck.

The exception is thrown when I am updating the dataset. I know that a record should be affected because I have checked the database to check that a record exists with the correct ID.

Could this be anything to to with the ID which is an autonumber column I have.

Any help would be appreciated.

Matt

If you want to see any code let me know. Its quite a lot for the updating as I have 107 fields for each record.

[modi123_1]

Yeah.. I'll need to see what your update statement looks like, your table structure, and how you are calling it.

[mouse88]

Ok no prob, sorry its quite a large chunk of code.

Heres the design view of the table. They are all text fields with a 255 char limit apart from the index which is an autonumber. Also they all allow zero length.



Heres where im loading in the data:

public FrmEdit(int mRowIndex, FrmMainMenu mMainMenuForm)
        {
            InitializeComponent();

            RowIndex = mRowIndex;
            MainMenuInst = mMainMenuForm;

            MainDataSet = new DataSet(); //Instansiate Data Set.

            string sql = "SELECT * FROM MainTable WHERE ID = " + (long)RowIndex; //Create Query.

            MainDataAdapter = new System.Data.OleDb.OleDbDataAdapter(sql, MainMenuInst.con); //Instantiate Data Adapter.

            OleDbCommandBuilder cb = new OleDbCommandBuilder(MainDataAdapter); //Open Connection.

            MainDataAdapter.Fill(MainDataSet, "SearchResults"); //Fill Data Set.

            DisplayData = MainDataSet.Tables["SearchResults"].Rows[0];
        }

Heres where I am changing the data in the dataset:

DataTable MainTable = MainDataSet.Tables["SearchResults"];
            DataRow MainDataRow = MainTable.Rows[0];

            MainDataRow.BeginEdit();

            MainDataRow["CallerName"] = TBCallerName.Text;
            MainDataRow["CallerCompany"] = TBCallerCompany.Text;
            MainDataRow["CallerAddress"] = TBCallerAddress.Text;
            MainDataRow["CallerContact"] = TBCallerContactNo.Text;
            MainDataRow["CallerFax"] = TBCallerFaxNo.Text;
            MainDataRow["CallerEmail"] = TBCallerEmail.Text;
            MainDataRow["CallerReference"] = TBCallerReference.Text;
            MainDataRow["PolicyNo"] = TBPolicyDetsPolicyNo.Text;
            MainDataRow["PolicyName"] = TBPolicyDetsName.Text;
            MainDataRow["PolicyBroker"] = CBPolicyDetsBroker.Text;
            MainDataRow["PolicyBrokerRef"] = TBPolicyDetsBrokerRef.Text;
            MainDataRow["PolicyVAT"] = CBPolicyVAT.Text;
            MainDataRow["PolicyHomeNo"] = TBPolicyDetsHomeNo.Text;
            MainDataRow["PolicyWorkNo"] = TBPolicyDetsWorkNo.Text;
            MainDataRow["PolicyMobNo"] = TBPolicyDetsMobNo.Text;
            MainDataRow["PolicyEmail"] = TBPolicyDetsEmail.Text;
            MainDataRow["PolicyAddress"] = TBPolicyDetsAddress.Text;
            MainDataRow["DriverName"] = TBDriverName.Text;
            MainDataRow["DriverDOB"] = TBDriverDOB.Text;
            MainDataRow["DriverLicType"] = CBDriverLicense.Text;
            MainDataRow["DriverExp"] = TBDriverExperience.Text;
            MainDataRow["DriverConv"] = TBDriverConv.Text;
            MainDataRow["DriverMed"] = TBDriverMeds.Text;
            MainDataRow["DriverOcc"] = TBDriverOcc.Text;
            MainDataRow["VehicleReg"] = TBVehicleRegistration.Text;
            MainDataRow["VehicleMake"] = TBVehicleMake.Text;
            MainDataRow["VehicleModel"] = TBVehicleModel.Text;
            MainDataRow["VehicleMods"] = TBVehicleMods.Text;
            MainDataRow["VehicleKeeper"] = TBVehicleKeeper.Text;
            MainDataRow["AccidentDate"] = TBAccidentDate.Text;
            MainDataRow["AccidentTime"] = TBAccidentTime.Text;
            MainDataRow["AccidentCause1"] = TBAccidentCause1.Text;
            MainDataRow["AccidentCause2"] = TBAccidentCause2.Text;
            MainDataRow["AccidentCircs"] = TBAccidentCircs.Text;
            MainDataRow["AccidentLocation"] = TBAccidentLocation.Text;
            MainDataRow["Witness1Name"] = TBWitness1Name.Text;
            MainDataRow["Witness1Address"] = TBWitness1Address.Text;
            MainDataRow["Witness1HomeTel"] = TBWitness1HomeTel.Text;
            MainDataRow["Witness1WorkTel"] = TBWitness1WorkTel.Text;
            MainDataRow["Witness1MobTel"] = TBWitness1MobTel.Text;
            MainDataRow["Witness2Name"] = TBWitness2Name.Text;
            MainDataRow["Witness2Address"] = TBWitness2Address.Text;
            MainDataRow["Witness2HomeTel"] = TBWitness2HomeTel.Text;
            MainDataRow["Witness2WorkTel"] = TBWitness2WorkTel.Text;
            MainDataRow["Witness2MobTel"] = TBWitness2MobTel.Text;
            MainDataRow["PoliceStation"] = TBPoliceStation.Text;
            MainDataRow["PoliceOfficer"] = TBPoliceOfficer.Text;
            MainDataRow["PoliceAddress"] = TBPoliceAddress.Text;
            MainDataRow["PoliceRef"] = TBPoliceRef.Text;
            MainDataRow["PoliceWorkTel"] = TBPoliceWorkTel.Text;
            MainDataRow["PoliceMobTel"] = TBPoliceMobTel.Text;
            MainDataRow["InjuriesPHPass"] = NumInjuriesPHPass.Value;
            MainDataRow["InjuriesTPPass"] = NumInjuriesTPPass.Value;
            MainDataRow["Injuries1Name"] = TBInjury1Name.Text;
            MainDataRow["Injuries1Status"] = CBInjury1Status.Text;
            MainDataRow["Injuries1Injuries"] = TBInjury1Details.Text;
            MainDataRow["Injuries1Detained"] = TBInjury1Detained.Text;
            MainDataRow["Injuries2Name"] = TBInjury2Name.Text;
            MainDataRow["Injuries2Status"] = CBInjury2Status.Text;
            MainDataRow["Injuries2Injuries"] = TBInjury2Details.Text;
            MainDataRow["Injuries2Detained"] = TBInjury2Detained.Text;
            MainDataRow["Injuries3Name"] = TBInjury3Name.Text;
            MainDataRow["Injuries3Status"] = CBInjury3Status.Text;
            MainDataRow["Injuries2Injuries"] = TBInjury3Details.Text;
            MainDataRow["Injuries3Detained"] = TBInjury3Detained.Text;
            MainDataRow["TPName"] = TBTPName.Text;
            MainDataRow["TPAddress"] = TBTPAddress.Text;
            MainDataRow["TPHomeTel"] = TBTPHomeTel.Text;
            MainDataRow["TPWorkTel"] = TBTPWorkTel.Text;
            MainDataRow["TPMobTel"] = TBTPMobTel.Text;
            MainDataRow["TPMake"] = TBTPMake.Text;
            MainDataRow["TPModel"] = TBTPModel.Text;
            MainDataRow["TPRegistration"] = TBTPRegistration.Text;
            MainDataRow["TPDamage"] = TBTPDamage.Text;
            MainDataRow["TPInsurer"] = TBTPInsurer.Text;
            MainDataRow["TPPolicyNo"] = TBTPPolicyNo.Text;
            MainDataRow["TPLiability"] = CBTPLiability.Text;
            MainDataRow["DamageClaiming"] = CBDMGClaiming.Text;
            MainDataRow["DamageDriveable"] = CBDMGDriveable.Text;
            MainDataRow["DamageLocation"] = TBDMGLocation.Text;
            MainDataRow["DamageTelNo"] = TBDMGTelNo.Text;
            MainDataRow["DamageStorage"] = NumDMGStorage.Value;
            MainDataRow["DamageDamage"] = TBDMGDamage.Text;
            MainDataRow["DamageDamageExtent"] = CBDMGExtent.Text;
            MainDataRow["DamageCost"] = TBDMGCost.Text;
            MainDataRow["DamageSupplier"] = CBDMGSupplier.Text;
            MainDataRow["CoverCover"] = CBCVRCover.Text;
            MainDataRow["CoverUse"] = CBCVRUse.Text;
            MainDataRow["CoverExcess"] = TBCVRExcess.Text;
            MainDataRow["CoverInception"] = TBCVRIncpetion.Text;
            MainDataRow["CoverDrivers"] = TBCVRDrivers.Text;
            MainDataRow["CoverScheme"] = TBCVRScheme.Text;
            MainDataRow["CoverOccupation"] = TBCVROccupation.Text;
            MainDataRow["CoverConv"] = TBCVRConvictions.Text;
            MainDataRow["CoverMeds"] = TBCVRMeds.Text;
            MainDataRow["CoverMods"] = TBCVRMods.Text;
            MainDataRow["CoverPaid"] = CBCVRPremium.Text;
            MainDataRow["HandlerName"] = TBHandlerName.Text;
            MainDataRow["HandlerDate"] = TBHandlerDate.Text;
            MainDataRow["HandlerTime"] = TBHandlerTime.Text;
            MainDataRow["Status"] = CBStatus.Text;
            MainDataRow["EditedBy1"] = TBEditBy1.Text;
            MainDataRow["EditedBy2"] = TBEditBy2.Text;
            MainDataRow["EditedBy3"] = TBEditBy3.Text;
            MainDataRow["EditedBy4"] = TBEditBy4.Text;
            MainDataRow["EditedBy5"] = TBEditBy5.Text;

            MainDataRow.EndEdit();

            #endregion

            #region "Update Database"

            MainDataAdapter.Update(MainDataSet, "SearchResults");

            MainDataSet.AcceptChanges();

            #endregion

And heres how I am constructing the update command:

private void AddParameters(OleDbCommand CMD, params string[] COLS)
        {          
            foreach (string COL in COLS)
            {
                CMD.Parameters.Add("@" + COL, OleDbType.Char, 0, COL);
            }
        }

        private void SetUpdateCommand()
        {
            MainDataAdapter.UpdateCommand = MainMenuInst.con.CreateCommand();

            MainDataAdapter.UpdateCommand.CommandText = 
                "UPDATE MainTable " +
                "SET CallerName = @CallerName, CallerCompany = @CallerCompany, CallerAddress = @CallerAddress, " +
                "CallerContact = @CallerContact, CallerFax = @CallerFax, CallerEmail = @CallerEmail, CallerReference = @CallerReference, " +
                "PolicyNo = @PolicyNo, PolicyName = @PolicyName, PolicyBroker = @PolicyBroker, PolicyBrokerRef = @PolicyBrokerRef, " +
                "PolicyVAT = @PolicyVAT, PolicyHomeNo = @PolicyHomeNo, PolicyWorkNo = @PolicyWorkNo, PolicyMobNo = @PolicyMobNo, PolicyEmail = @PolicyEmail, " +
                "PolicyAddress = @PolicyAddress, DriverName = @DriverName, DriverDOB = @DriverDOB, DriverLicType = @DriverLicType, " +
                "DriverExp = @DriverExp, DriverConv = @DriverConv, DriverMed = @DriverMed, DriverOcc = @DriverOcc, VehicleReg = @VehicleReg, " +
                "VehicleMake = @VehicleMake, VehicleModel = @VehicleModel, VehicleMods = @VehicleMods, VehicleKeeper = @VehicleKeeper, " +
                "AccidentDate = @AccidentDate, AccidentTime = @AccidentTime, AccidentCause1 = @AccidentCause1, AccidentCause2 = @AccidentCause2, " +
                "AccidentCircs = @AccidentCircs, AccidentLocation = @AccidentLocation, Witness1Name = @Witness1Name, Witness1Address = @Witness1Address, " +
                "Witness1HomeTel = @Witness1HomeTel, Witness1WorkTel = @Witness1WorkTel, Witness1MobTel = @Witness1MobTel, Witness2Name = @Witness2Name, " +
                "Witness2Address = @Witness2Address, Witness2HomeTel = @Witness2HomeTel, Witness2WorkTel = @Witness2WorkTel, Witness2MobTel = @Witness2MobTel, " +
                "PoliceStation = @PoliceStation, PoliceOfficer = @PoliceOfficer, PoliceAddress = @PoliceAddress, PoliceRef = @PoliceRef, " +
                "PoliceWorkTel = @PoliceWorkTel, PoliceMobTel = @PoliceMobTel, InjuriesPHPass = @InjuriesPHPass, InjuriesTPPass = @InjuriesTPPass, " +
                "Injuries1Name = @Injuries1Name, Injuries1Status = @Injuries1Status, Injuries1Injuries = @Injuries1Injuries, Injuries1Detained = @Injuries1Detained, " +
                "Injuries2Name = @Injuries2Name, Injuries2Status = @Injuries2Status, Injuries2Injuries = @Injuries2Injuries, " +
                "Injuries2Detained = @Injuries2Detained, Injuries3Name = @Injuries3Name, Injuries3Status = @Injuries3Status, Injuries3Injuries = @Injuries3Injuries, " +
                "Injuries3Detained = @Injuries3Detained, TPName = @TPName, TPAddress = @TPAddress, TPHomeTel = @TPHomeTel, TPWorkTel = @TPWorkTel, " +
                "TPMobTel = @TPMobTel, TPMake = @TPMake, TPModel = @TPModel, TPRegistration = @TPRegistration, TPDamage = @TPDamage, " +
                "TPInsurer = @TPInsurer, TPPolicyNo = @TPPolicyNo, TPLiability = @TPLiability, DamageClaiming = @DamageClaiming, " +
                "DamageDriveable = @DamageDriveable, DamageLocation = @DamageLocation, DamageTelNo = @DamageTelNo, DamageStorage = @DamageStorage, " +
                "DamageDamage = @DamageDamage, DamageDamageExtent = @DamageDamageExtent, DamageCost = @DamageCost, DamageSupplier = @DamageSupplier, " +
                "CoverCover = @CoverCover, CoverUse = @CoverUse, CoverExcess = @CoverExcess, CoverInception = @CoverInception, " +
                "CoverDrivers = @CoverDrivers, CoverScheme = @CoverScheme, CoverOccupation = @CoverOccupation, CoverConv = @CoverConv, " +
                "CoverMeds = @CoverMeds, CoverMods = @CoverMods, CoverPaid = @CoverPaid, HandlerName = @HandlerName, HandlerDate = @HandlerDate, " +
                "HandlerTime = @HandlerTime, Status = @Status, EditedBy1 = @EditedBy1, EditedBy2 = @EditedBy2, EditedBy3 = @EditedBy3, " +
                "EditedBy4 = @EditedBy4, EditedBy5 = @EditedBy5 " +
                "WHERE ID = @ID";

            AddParameters(MainDataAdapter.UpdateCommand, "ID", "CallerName", "CallerCompany", "CallerAddress", "CallerContact",
                "CallerFax", "CallerEmail", "CallerReference", "PolicyNo", "PolicyName", "PolicyBroker", "PolicyBrokerRef",
                "PolicyVAT", "PolicyHomeNo", "PolicyWorkNo", "PolicyMobNo", "PolicyEmail", "PolicyAddress", "DriverName",
                "DriverDOB", "DriverLicType", "DriverExp", "DriverConv", "DriverMed", "DriverOcc", "VehicleReg",
                "VehicleMake", "VehicleModel", "VehicleMods", "VehicleKeeper", "AccidentDate", "AccidentTime", "AccidentCause1",
                "AccidentCause2", "AccidentCircs", "AccidentLocation", "Witness1Name", "Witness1Address", "Witness1HomeTel",
                "Witness1WorkTel", "Witness1MobTel", "Witness2Name", "Witness2Address", "Witness2HomeTel", "Witness2WorkTel",
                "Witness2MobTel", "PoliceStation", "PoliceOfficer", "PoliceAddress", "PoliceRef", "PoliceWorkTel",
                "PoliceMobTel", "InjuriesPHPass", "InjuriesTPPass", "Injuries1Name", "Injuries1Status", "Injuries1Injuries",
                "Injuries1Detained", "Injuries2Name", "Injuries2Status", "Injuries2Injuries", "Injuries2Detained", "Injuries3Name",
                "Injuries3Status", "Injuries3Injuries", "Injuries3Detained", "TPName", "TPAddress", "TPHomeTel", "TPWorkTel",
                "TPMobTel", "TPMake", "TPModel", "TPRegistration", "TPDamage", "TPInsurer", "TPPolicyNo", "TPLiability",
                "DamageClaiming", "DamageDriveable", "DamageLocation", "DamageTelNo", "DamageStorage", "DamageDamage",
                "DamageDamageExtent", "DamageCost", "DamageSupplier", "CoverCover", "CoverUse", "CoverExcess", "CoverInception",
                "CoverDrivers", "CoverScheme", "CoverOccupation", "CoverConv", "CoverMeds", "CoverMods",
                "CoverPaid", "HandlerName", "HandlerDate", "HandlerTime", "Status", "EditedBy1", "EditedBy2",
                "EditedBy3", "EditedBy4", "EditedBy5");
        }

Hope you can help.

Thanks

Matt

[Sergio Tapia]

Is this an application you created and have access to the database schema?

If so I'd highly recommend you normalize your database. Have fields like Injruy1, Injury2, Injury3 will hurt you in the long run.

Try having something like this.

Person
ID
Name
LastName

Injury
ID 
IDPerson
Description

Now you can easily query to ask to retrieve all of the injuries a given person might have and you have no limit as to how many injuries a person can have stored in your DB.

[Bacanze]

Wow, seriously learn normalization, you database structure is really bad. You should have around 10 separate tables just from that one.

[mouse88]

Bacanze, on 01 August 2010 - 09:25 AM, said:

Wow, seriously learn normalization, you database structure is really bad. You should have around 10 separate tables just from that one.

Ok thanks for your advice but ill sort that out when I get this working after all I didn't ask for help on my database im looking for help on solving this exception.

I know what im doing as far as the database is concerned is bad practise and I will probably improve on that when I get this working. The app will only use the data for like 2-3 days and it will then be used to create a report which will be attached to another system. It will remain in the database but just for backup purposes in case the report goes missing.

Matt

This post has been edited by mouse88: 01 August 2010 - 12:14 PM

[modi123_1]

Are you sure your row exists in the database to begin with? Being you are using a command query and not a stored procedure call this will make it difficult to check. I would verify your key exists.

[mouse88]

modi123_1, on 02 August 2010 - 06:29 AM, said:

Are you sure your row exists in the database to begin with? Being you are using a command query and not a stored procedure call this will make it difficult to check. I would verify your key exists.

Well I can see that in the database the key is definitly present and when the exception is thrown my variable still shows the correct value.

Matt

[mouse88]

Finally got this working. I first tried setting the value of the @ID parameter to my stored rowindex number but still the exception. I then changed my updatecommand text to:

MainDataAdapter.UpdateCommand.CommandText = 
                "UPDATE MainTable " +
                "SET CallerName = @CallerName, CallerCompany = @CallerCompany, CallerAddress = @CallerAddress, " +
                "CallerContact = @CallerContact, CallerFax = @CallerFax, CallerEmail = @CallerEmail, CallerReference = @CallerReference, " +
                "PolicyNo = @PolicyNo, PolicyName = @PolicyName, PolicyBroker = @PolicyBroker, PolicyBrokerRef = @PolicyBrokerRef, " +
                "PolicyVAT = @PolicyVAT, PolicyHomeNo = @PolicyHomeNo, PolicyWorkNo = @PolicyWorkNo, PolicyMobNo = @PolicyMobNo, PolicyEmail = @PolicyEmail, " +
                "PolicyAddress = @PolicyAddress, DriverName = @DriverName, DriverDOB = @DriverDOB, DriverLicType = @DriverLicType, " +
                "DriverExp = @DriverExp, DriverConv = @DriverConv, DriverMed = @DriverMed, DriverOcc = @DriverOcc, VehicleReg = @VehicleReg, " +
                "VehicleMake = @VehicleMake, VehicleModel = @VehicleModel, VehicleMods = @VehicleMods, VehicleKeeper = @VehicleKeeper, " +
                "AccidentDate = @AccidentDate, AccidentTime = @AccidentTime, AccidentCause1 = @AccidentCause1, AccidentCause2 = @AccidentCause2, " +
                "AccidentCircs = @AccidentCircs, AccidentLocation = @AccidentLocation, Witness1Name = @Witness1Name, Witness1Address = @Witness1Address, " +
                "Witness1HomeTel = @Witness1HomeTel, Witness1WorkTel = @Witness1WorkTel, Witness1MobTel = @Witness1MobTel, Witness2Name = @Witness2Name, " +
                "Witness2Address = @Witness2Address, Witness2HomeTel = @Witness2HomeTel, Witness2WorkTel = @Witness2WorkTel, Witness2MobTel = @Witness2MobTel, " +
                "PoliceStation = @PoliceStation, PoliceOfficer = @PoliceOfficer, PoliceAddress = @PoliceAddress, PoliceRef = @PoliceRef, " +
                "PoliceWorkTel = @PoliceWorkTel, PoliceMobTel = @PoliceMobTel, InjuriesPHPass = @InjuriesPHPass, InjuriesTPPass = @InjuriesTPPass, " +
                "Injuries1Name = @Injuries1Name, Injuries1Status = @Injuries1Status, Injuries1Injuries = @Injuries1Injuries, Injuries1Detained = @Injuries1Detained, " +
                "Injuries2Name = @Injuries2Name, Injuries2Status = @Injuries2Status, Injuries2Injuries = @Injuries2Injuries, " +
                "Injuries2Detained = @Injuries2Detained, Injuries3Name = @Injuries3Name, Injuries3Status = @Injuries3Status, Injuries3Injuries = @Injuries3Injuries, " +
                "Injuries3Detained = @Injuries3Detained, TPName = @TPName, TPAddress = @TPAddress, TPHomeTel = @TPHomeTel, TPWorkTel = @TPWorkTel, " +
                "TPMobTel = @TPMobTel, TPMake = @TPMake, TPModel = @TPModel, TPRegistration = @TPRegistration, TPDamage = @TPDamage, " +
                "TPInsurer = @TPInsurer, TPPolicyNo = @TPPolicyNo, TPLiability = @TPLiability, DamageClaiming = @DamageClaiming, " +
                "DamageDriveable = @DamageDriveable, DamageLocation = @DamageLocation, DamageTelNo = @DamageTelNo, DamageStorage = @DamageStorage, " +
                "DamageDamage = @DamageDamage, DamageDamageExtent = @DamageDamageExtent, DamageCost = @DamageCost, DamageSupplier = @DamageSupplier, " +
                "CoverCover = @CoverCover, CoverUse = @CoverUse, CoverExcess = @CoverExcess, CoverInception = @CoverInception, " +
                "CoverDrivers = @CoverDrivers, CoverScheme = @CoverScheme, CoverOccupation = @CoverOccupation, CoverConv = @CoverConv, " +
                "CoverMeds = @CoverMeds, CoverMods = @CoverMods, CoverPaid = @CoverPaid, HandlerName = @HandlerName, HandlerDate = @HandlerDate, " +
                "HandlerTime = @HandlerTime, Status = @Status, EditedBy1 = @EditedBy1, EditedBy2 = @EditedBy2, EditedBy3 = @EditedBy3, " +
                "EditedBy4 = @EditedBy4, EditedBy5 = @EditedBy5 " +
                "WHERE ID = " + RowIndex;

So instead of using a parameter at the end ive just used my variable direct which works fine.

Strange but im ahppy its working.

Thanks for the help.