Finding a field

[gmcconville]

Hi, need some advice please

New to Database programming, but what I want to do is update a database everyday with information I am getting from a file

So what I want to do is the following

Do until End Of File
Read ProductCode and ProductDescription from File
Record = Database.Columns["Code"].Find(ProductCode)
If no Record found then
Add a new record with ProductCode and Description
else
Update Record // Description may have changed
End If
Loop

The file may contain 4000 to 5000 records and I don't know what the best way is to search the database.

Would it be to loop through each record testing if ProductCode matches, or is there already a search feature there
I presume using DataSet.Table["Products"].Select[search criteria] would not be a good choice as its going through the loop so many times?

The database connection I have is
Adapter = new OleDbDataAdapter("Select * from Products",
@"Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Stock.mdb");
Set = new DataSet();
Adapter.Fill(Set, "Products");

I hope this is easy to understand, and I appreciate your help

[nelo]

I think the best way is to search the query the database for the product. Do not load all the data into memory by using Adapter/DataSet approach. Use the OleDBCommand to create and execute a query that will let you know whether or not the product exists (e.g. SELECT * FROM Products WHERE ProductCode = <the specific code in here>). When you call the ExecuteScalar method the result will tell you whether or not the record already exists. If it doesn't you can add it. If it does you can update it. You could do both operations with another set of OleDbCommands. Here's a link to the documentation OleDbCommand (http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand%28VS.71%29.aspx). That should get you started...