Problem updating MSSQL database with relationship between two tables

[CarrahaG]

Hello

We are programing in Delphi 2006 using a MSSQL database. We are having a problem updating two tables which have a relationship between them.


The database and tables are defined as follows:

Database: FDD


Table: OPN_ITM_CHANGES

Field Type
---------- ------------- ------------
BATCHES char(31) Primary Key
CUS_NO varchar(12)
DOC_DT smalldatetime
BATCH_ID smallint Foreign Key




Table: BATCHES

Field Type
---------- ------------- ------------
BATCH_ID smallint Primary Key
USER_ID varchar(10)



We have configured the components as follows:



1. TSQLConnection component called SQLConnectionFDD.
2. TSQLDataSet component called SQLDataSetOPN_ITM_CHANGES which has the SQL Connection value set to SQLConnectionFDD.
3. TDataSetProvider component called DataSetProviderOPN_ITM_CHANGES which has the DataSet value set to SQLDataSetOPN_ITM_CHANGES.
4. TClientDataSet component called ClientDataSetOPN_ITM_CHANGES which has the ProviderName set to DataSetProviderOPN_ITM_CHANGES
5. TDataSource component called DataSourceOPN_ITM_CHANGES which has the DataSet set to ClientDataSetOPN_ITM_CHANGES.


6. TSQLDataSet component called SQLDataSetBATCHES which has the SQL Connection value set to SQLConnectionFDD.
7. TDataSetProvider component called DataSetProviderBATCHES which has the DataSet value set to SQLDataSetBATCHES.
8. TClientDataSet component called ClientDataSetBATCHES which has the ProviderName set to DataSetProviderBATCHES
9. TDataSource component called DataSourceBATCHES which has the DataSet set to ClientDataSetOPN_ITM_ID.



We keep getting the following error when we are applying the updates to the database:

Project FixDueDates.exe raised exception class EAccessViolation with message 'Access vilation at address 0152B19B in module 'dbxmss30.dll', Read of address 030000F4.


The error occurs at the 3 line of code below:


if DataModuleFDD.ClientDataSetOPN_ITM_CHANGES.ChangeCount > 0 then
DataModuleFDD.ClientDataSetOPN_ITM_CHANGES.ApplyUpdates(0);
if DataModuleFDD.ClientDataSetBATCHES.ChangeCount > 0 then
DataModuleFDD.ClientDataSetBATCHES.ApplyUpdates(0);

Any idea what could be causing the error?


Regards,
Georges

 

[VintageWine]

I cannot give you a "this is it" answer, but can give you some directions to investigate.

1. Are you sure that "DataModuleFDD.ClientDataSetBATCHES.ChangeCount" is the line that is erroring. I suspect that it might be the ApplyUpdates(0) in the previous line. The Delphi debugger often highlights the following line.

2. Try capturing the SQL that is being run, then run that SQL in SQL Server Manager or similar.

Good Luck

 

[CarrahaG]

Hello VintageWine

I got rid of the error by changing the order in which the tables are updated.

See below:

if DataModuleFDD.ClientDataSetBATCHES.ChangeCount > 0 then
DataModuleFDD.ClientDataSetBATCHES.ApplyUpdates(0);

if DataModuleFDD.ClientDataSetOPN_ITM_CHANGES.ChangeCount > 0 then
DataModuleFDD.ClientDataSetOPN_ITM_CHANGES.ApplyUpdates(0);

It seems to be that the table with the primary key that provides the relationship needs to be updated first. I am not sure if the solution I did is the appropriate one since it was easy in this case due to the fact I had a simple one-to-many relationship.