Unable to do an SQL UPDATE with OleDb

Question

0.00/5 (No votes)

See more:

I'm new to this so here goes. I have a table that has latitude and longitude degree coordinates and I'm trying to do an SQL UPDATE in the third column and convert the latitude and longitude coordinates into MGRS in the MGRS column.

I have a table called tblMGRSAdded and in it, it looks like this.

Latitude  Longitude  MGRS

37.51       -85.24

 37.19       -93.55

44.07       -93.79

I'm able to do the conversion in an OleDbDataReader and here's the code for that.

C#

using System.Data.OleDb;
using System.Data;
using GeoUtility.GeoSystem;
 
OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Project\local.mdb");
thisConnection.Open(); 
OleDbCommand thisCommand = thisConnection.CreateCommand();
thisCommand.CommandText = "SELECT * from tblMGRSAdded";
OleDbDataReader thisReader = thisCommand.ExecuteReader();
       while (thisReader.Read())
            {
                Geographic geoF = new Geographic(Convert.ToDouble(thisReader["longitude"]), Convert.ToDouble(thisReader["latitude"]));
                MGRS mgrs = (MGRS)geoF;
                string mgrsStr = mgrs.Zone + mgrs.Band + " " + mgrs.Grid + " " +
                       mgrs.EastString.Substring(0, 4) + " " + mgrs.NorthString.Substring(0, 4);
                Console.WriteLine("\t{0}\t{1}\t{2}", thisReader["latitude"], thisReader["longitude"], mgrsStr);
            }
thisReader.Close();
thisConnection.Close();
Console.ReadKey();

I get a result that looks like this.

Latitude  Longitude  MGRS

37.51      -85.24        16S FG 5555 5290

37.19      -93.55        15S VB 5118 1609

44.07      -93.79        15T VJ 3673 7995

I realize from tonnes of reading that you can't use an update statement from an OleDbDateReader. That's fine but then how do I do the update then for the conversion.

I'm using something called GeoUtility.dll from http://geoutility.codeplex.com/. It's a great little tool and things would be much better if I can only figure this out.

I'm not going to attempt to put down some of the code examples that I've tried but I do know this. I have to open the connection and I have to somehow use a SQL UPDATE statement but from my example how would my UPDATE statement look using mgrsStr like up above to make the conversion and update the column MGRS in the table??

Any help would be much appreciated. I've been working on this none stop and right now I can't believe something so small has now put my job in jeopardy.

Thanks

MR

Posted 7-Oct-11 17:07pm

mzrax

Updated 7-Oct-11 18:22pm

Mehdi Gholam

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bala Selvanayagam · Accepted Answer · 2011-10-08T04:49:00

I was able to update without any issues

I am not sure whether the latitude and the Longitude values you are passing is correct (37.51,-85.24??) . getting a system exception on your values saying its NOT VALID.

I tried with latitude=8.12345, Longitude=50.56789 and worked very well and the database is also updated, Please see the code below i have used

private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb");
                thisConnection.Open();
                OleDbCommand thisCommand = thisConnection.CreateCommand();
                thisCommand.CommandText = "SELECT * from tblMGRSAdded";
                OleDbDataReader thisReader = thisCommand.ExecuteReader();
                while (thisReader.Read())
                {
                    Geographic geoF = new Geographic(Convert.ToDouble(thisReader["longitude"]), Convert.ToDouble(thisReader["latitude"]));
                    MGRS mgrs = (MGRS)geoF;
                    

                    string mgrsStr = mgrs.Zone + mgrs.Band + " " + mgrs.Grid + " " +
                           mgrs.EastString.Substring(0, 4) + " " + mgrs.NorthString.Substring(0, 4);

                    OleDbCommand thisUpdateCommand = thisConnection.CreateCommand();
                    thisUpdateCommand.CommandText = "update tblMGRSAdded set MGRS='" + mgrsStr + "' where ID=" + thisReader["Id"].ToString();
                    thisUpdateCommand.ExecuteNonQuery();  
                    MessageBox.Show(mgrsStr);                     
                }
                thisReader.Close();
                thisConnection.Close();

                MessageBox.Show("OK"); 
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);  
            }
        }

Console version of the solution - UPDATE 2

static void Main(string[] args)
      {
          try
          {
              OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb");
              thisConnection.Open();
              OleDbCommand thisCommand = thisConnection.CreateCommand();
              thisCommand.CommandText = "SELECT * from tblMGRSAdded";
              OleDbDataReader thisReader = thisCommand.ExecuteReader();
              while (thisReader.Read())
              {
                  Geographic geoF = new Geographic(Convert.ToDouble(thisReader["longitude"]), Convert.ToDouble(thisReader["latitude"]));
                  MGRS mgrs = (MGRS)geoF;
                  string mgrsStr = mgrs.Zone + mgrs.Band + " " + mgrs.Grid + " " +
                         mgrs.EastString.Substring(0, 4) + " " + mgrs.NorthString.Substring(0, 4);
                  Console.WriteLine("\t{0}\t{1}\t{2}", thisReader["latitude"], thisReader["longitude"], mgrsStr);

                  //UPDATE ROUTINE
                  using (OleDbCommand thisUpdateCommand = thisConnection.CreateCommand())
                  {

                      thisUpdateCommand.CommandText = "update tblMGRSAdded set MGRS='" + mgrsStr + "' where ID=" + thisReader["Id"].ToString();
                      thisUpdateCommand.ExecuteNonQuery();
                  }

                 // Console.WriteLine("Database updated for this record!!....Press any key to continue");
                 // Console.ReadKey();
              }
              thisReader.Close();
              thisConnection.Close();
              Console.WriteLine("COMPLETED!!!!!....Press any key to exit");
              Console.ReadKey();
          }
          catch (Exception ex)
          {
              Console.WriteLine(ex.Message);
              Console.WriteLine("ERROR!!!!!....Press any key to exit");
              Console.ReadKey();
          }
      }