Database - go through every field

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

At times users of my C# program will have faulty data in their Access database.
I would like to connect to their database and if necessary correct the data
in each field in every record in every table of their database. What is the
best way to accomplish this? I am interested in the concept but would also
like to see sample code. Thanks in advance for your help,
Jim
 
Jim said:
At times users of my C# program will have faulty data in their Access database.
Click to expand...

What do you mean by "faulty data"? Do you mean data that doesn't meet
their business rules, or do you mean data that causes Access to crash?
I would like to connect to their database and if necessary correct the data
in each field in every record in every table of their database. What is the
best way to accomplish this? I am interested in the concept but would also
like to see sample code.
Click to expand...

If you're talking about a record that causes Access to crash, you can't fix
this line by line. You have to run a repair-and-compact operation (which
you can do in code, or in the IDE). If that doesn't fix the problem, then
you have to copy the good data line by line into another database.
 
Access does not crash. The problem is that fields left uninitialized do not
work with the queries generated by the TableAdapters/DataSet designer. If a
DateTime, integer or string field is left uninitialized, a query that works
with these fields doesn't return the expected values. However if a DateTime
is changed to 1/1/1899 or an integer to 0 (zero) or a string to "", the
queries work as expected. I need to go through each field to make sure there
are no uninitialized values. I am looking for suggestions on the best way to
accomplish this along with some sample code. Thanks for your help,
Jim
 
Jim said:
Access does not crash. The problem is that fields left uninitialized do not
work with the queries generated by the TableAdapters/DataSet designer.
Click to expand...

I assume you are fixing the database table definitions to assign a default
value to these fields.
If a
DateTime, integer or string field is left uninitialized, a query that works
with these fields doesn't return the expected values. However if a DateTime
is changed to 1/1/1899 or an integer to 0 (zero) or a string to "", the
queries work as expected. I need to go through each field to make sure there
are no uninitialized values. I am looking for suggestions on the best way to
accomplish this along with some sample code.
Click to expand...

This is really a database design issue. The confusion caused by NULL
values in SQL tables is well-documented. In the meantime, you can work
around this by executing a set of queries like:

UPDATE personnel SET hiredate=#1/1/1899# WHERE hiredate IS NULL;
UPDATE personnel SET salary=0 WHERE salary IS NULL;
 
Back
Top