如何更新使用OLEDB参数的表？ [英] how to update a table using oledb parameters?
问题描述
我有它有三个字段，分别LM_ code，M_Name，说明表。 LC_ code是一个自动生成的字符串ID，保持此我更新M_Name和说明。我用正常的更新命令，该值传递运行，但字段不会得到更新。我希望使用OLEDB参数字段可以被更新。
下面是我的code。
公共无效修改（）
{
查询字符串=更新Master_Accounts设置（M_Name ='+ M_Name +'，说明='+说明+'），其中LM_ code ='+ LM_ code +';
DataManager.RunExecuteNonQuery（ConnectionString.Constr，查询）;
}
在DataManager类我执行的查询字符串。
公共静态无效RunExecuteNonQuery（字符串构造方法，查询字符串）
{ OleDbConnection的MyConnection的=新的OleDbConnection（构造方法）;
尝试
{
myConnection.Open（）;
OleDbCommand的myCommand =新的OleDbCommand（查询，MyConnection的）;
myCommand.ExecuteNonQuery（）;
}
赶上（异常前）
{
字符串消息= ex.Message;
扔恩;
} 最后
{
如果（myConnection.State == ConnectionState.Open）
myConnection.Close（）;
}}私人无效toolstModify_Click_1（对象发件人，EventArgs的发送）
{
txtam code.Enabled = TRUE;
jewellery.LM_ code = txtam code.Text;
jewellery.M_Name = txtaccname.Text;
jewellery.Desc = txtdesc.Text;
jewellery.Modify（）;
的MessageBox.show（数据更新成功与）;}
这惹恼了我，扭曲的小OLEDB，所以我会在这里发布我的解决方案为后人。这是一个老的文章，但似乎是个不错的地方。
OLEDB无法识别命名的参数，但它显然没有认识到你的尝试的传达命名参数，所以你可以用它来你的优势，使您的SQL语义，更容易理解。只要他们在同一顺序是过去了，它会接受一个变量作为命名参数。
我用这个来更新网络文件夹一个简单的Access数据库。
使用（康涅狄格州的OleDbConnection =新的OleDbConnection（CONNSTRING））
{
conn.Open（）;
OleDbCommand的CMD = conn.CreateCommand（）; 的for（int i = 0; I＆LT; Customers.Count;我++）
{
cmd.Parameters.Add（新OleDbParameter（@ VAR1，客户[I] .Name点））
cmd.Parameters.Add（新OleDbParameter（@ VAR2，客户[I] .PhoneNum））
cmd.Parameters.Add（新OleDbParameter（@ VAR3，客户[I] .ID））
cmd.Parameters.Add（新OleDbParameter（@ VAR4，客户[I] .Name点））
cmd.Parameters.Add（新OleDbParameter（@ VAR5，客户[I] .PhoneNum）） cmd.CommandText =UPDATE客户提供集名称= @ VAR1，电话= @ VAR2+
WHERE ID = @ VAR3 AND（名称＆LT;＆GT; @ VAR4或电话＆LT;＆GT; @ VAR5）;
cmd.ExecuteNonQuery（）;
cmd.Parameters.Clear（）;
}
}
这可能看起来像一个多余的code，而且是你在技术上重复自己，但是这使得它更容易的世界，当你玩连接的非点以后.....
I am having a table which has three fields, namely LM_code,M_Name,Desc. LC_code is a autogenerated string Id, keeping this i am updating M_Name and Desc. I used normal update command, the value is passing in runtime but the fields are not getting updated. I hope using oledb parameters the fields can be updated.
Here is my code.
public void Modify()
{
String query = "Update Master_Accounts set (M_Name='" + M_Name + "',Desc='" + Desc + "') where LM_code='" + LM_code + "'";
DataManager.RunExecuteNonQuery(ConnectionString.Constr, query);
}
In DataManager Class i am executing the query string.
public static void RunExecuteNonQuery(string Constr, string query)
{
OleDbConnection myConnection = new OleDbConnection(Constr);
try
{
myConnection.Open();
OleDbCommand myCommand = new OleDbCommand(query, myConnection);
myCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
string Message = ex.Message;
throw ex;
}
finally
{
if (myConnection.State == ConnectionState.Open)
myConnection.Close();
}
}
private void toolstModify_Click_1(object sender, EventArgs e)
{
txtamcode.Enabled = true;
jewellery.LM_code = txtamcode.Text;
jewellery.M_Name = txtaccname.Text;
jewellery.Desc = txtdesc.Text;
jewellery.Modify();
MessageBox.Show("Data Updated Succesfully");
}
This annoyed me, screwy little OleDB, so I'll post my solution here for posterity. It's an old post but seems like a good place.
OleDB doesn't recognize named parameters, but it apparently does recognize that you're trying to convey a named parameter, so you can use that to your advantage and make your SQL semantic and easier to understand. So long as they're passed in the same order, it'll accept a variable as a named parameter.
I used this to update a simple Access database in a network folder.
using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
OleDbCommand cmd = conn.CreateCommand();
for (int i = 0; i < Customers.Count; i++)
{
cmd.Parameters.Add(new OleDbParameter("@var1", Customer[i].Name))
cmd.Parameters.Add(new OleDbParameter("@var2", Customer[i].PhoneNum))
cmd.Parameters.Add(new OleDbParameter("@var3", Customer[i].ID))
cmd.Parameters.Add(new OleDbParameter("@var4", Customer[i].Name))
cmd.Parameters.Add(new OleDbParameter("@var5", Customer[i].PhoneNum))
cmd.CommandText = "UPDATE Customers SET Name=@var1, Phone=@var2" +
"WHERE ID=@var3 AND (Name<>@var4 OR Phone<>@var5)";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
It may look like an excess of code, and yes you're technically repeating yourself, but this makes it worlds easier when you're playing connect-the-dots later on.....
这篇关于如何更新使用OLEDB参数的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
