无法使用OleDb执行SQL UPDATE [英] Unable to do an SQL UPDATE with OleDb
问题描述
我是这个新手，所以去吧.我有一个具有纬度和经度度坐标的表，我正在尝试在第三列中执行SQL UPDATE并将纬度和经度坐标转换为MGRS列中的MGRS.
我有一个名为tblMGRSAdded的表，它看起来像这样.
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
我可以在OleDbDataReader中进行转换，这是该代码.
I''m able to do the conversion in an OleDbDataReader and here''s the code for that.
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
我从大量的阅读中了解到，您不能使用OleDbDateReader的更新语句.很好，但是接下来我该如何进行更新才能进行转换.
我正在使用来自http://geoutility.codeplex.com/的名为GeoUtility.dll的文件.这是一个很棒的小工具，如果我只能解决这个问题，情况会好得多.
我不会尝试放下我尝试过的一些代码示例，但我确实知道这一点.我必须打开连接，并且必须以某种方式使用SQL UPDATE语句，但是从我的示例中，如何使用mgrsStr像上面的UPDATE语句那样执行UPDATE语句，以进行转换并更新表中的MGRS列?
任何帮助将非常感激.我一直在不停地努力，现在我简直不敢相信现在这么小的事情使我的工作陷入危险.
谢谢
MR
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
推荐答案
我能够进行更新而没有任何问题
我不确定您传递的纬度和经度值是否正确(37.51，-85.24 ??).在您的值上获取系统异常，说它无效.
我尝试使用latitude = 8.12345，Longitude = 50.56789进行了很好的工作，并且数据库也已更新，请参见下面我已使用的代码
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);
}
}
解决方案的控制台版本-UPDATE 2
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();
}
}
这篇关于无法使用OleDb执行SQL UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
