Link to home
Start Free TrialLog in
Avatar of tman43
tman43

asked on

How to fill empty cells of one Microsoft Access database using C#

I have a Microsoft Access database with columns MTRS, Lat_Y, and Lon_X filled with data all cells, 680,000 rows. I have a second similar Access database with data for MTRS but empty cells for Lat_Y, and Lon_X, 1600 rows. How do I fill the Lat_Y and Lon_X cells in the second smaller database for matching values of MTRS? I typically use OleDbConnection in C# to manipulate Access databases. I am weak on Access queries. I also have ArcGIS and QGIS but have limited experience with these.

Thanks,

Tim Sprout
SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tman43
tman43

ASKER

Thanks for showing me the query logic. That will prove useful in the future. I ended up using a two ValuePair dictionary.

//create ValuePair structure
        struct ValuePair
        {
            public string Lat_Y;
            public string Lon_X;
        }

//create dictionary using ValuePair structure
        Dictionary<string, ValuePair> dictionary = new Dictionary<string, ValuePair>();

//read large parent database that has all cells filled and load dictionary
        private void btnDictionaryTest_Click(object sender, EventArgs e)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "D:" + "AccessDatabase.mdb";
            using (OleDbConnection connection = new OleDbConnection(strConn))
            {
                string strOleDb = "Select MTRS, Latitude, Longitude from tblRowMap";
                OleDbCommand command = new OleDbCommand(strOleDb, connection);

                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    string mTRS = reader.GetString(0);
                    string lat_Y = reader.GetValue(1).ToString();
                    string lon_X = reader.GetValue(2).ToString();
                    dictionary.Add(mTRS, new ValuePair { Lat_Y = lat_Y, Lon_X = lon_X });
                }
                reader.Close();
            }
//read smaller child database that has empty cells that need filling (I used a DataGridView) and
//retrieve values for these keys
              if (dictionary.ContainsKey(mTRS) == true)
            {
                  string latGrid2 = dictionary[mtrsGrid2].Lat_Y.ToString();
                  string lonGrid2 = dictionary[mtrsGrid2].Lon_X.ToString();                
             }
//INSERT these and other required fields into third empty database