I have a problem on the integration of foreign keys with SQL Server.
When entering information, the user can select from a dropdown list additional data (itself registered in database). We have implemented a system that allows, when selecting a value from the dropdown list, retrieve its code (primary key) and store it in an invisible field of the window. Upon validation of the seizure, the value of this invisible field is used to be inserted into the column referencing the foreign key.
However if the user selects any data in the dropdown, the invisible field is empty. This causes an error in violation of referential integrity because the SQL query tries to insert a blank value (empty string) rather than NULL. This error is normal because no line of the referenced table is linked to an empty code.
Do you know if it is possible to make automatically consider the blank fields as NULL (STRING_Null, NUMBER_Null) ? Could the provider used to connect to the database to support this ?
The technical information are :
- Team Developper version 6.2 SP3r
- SQL Server 2012
- OLEDB
- Provider SQLNCLI11