试用了一下 OracleDataAdapter 功能，但是upadate时遇到问题。数据不能更新。如下代码有什么问题？

Question

using System;
using System.Data;
using Oracle.DataAccess.Client;
using System.Web.Configuration;namespace oraModify
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                String connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
                OracleConnection con = new OracleConnection(connectionString);
                con.Open();                //// Create the command
                OracleCommand cmdSelect = new OracleCommand("", con);                // sql文
                OracleCommand cmdUpdate = new OracleCommand("", con);                // sql文
                OracleCommand cmdDelete = new OracleCommand("", con);                // sql文
                OracleCommand cmdInsert = new OracleCommand("", con);                //select statement
                string strSelectSql = "SELECT * FROM TBUSER WHERE  LOGINID = :I_LOGINID ";
                string strUpdateSql = "UPDATE TBUSER SET EFFECTSTRT = :I_EFFECTSTRT  WHERE  LOGINID = :I_LOGINID ";
                //string strDeleteSql = "DELETE FROM TBUSER  WHERE  LOGINID = :I_LOGINID";
                //string strInsertSql = "INSERT TB_USER(LOGINID,EFFECTSTRT,EFFECTEND) VALUES (:I_LOGINID, :I_EFFECTSTRT,:I_EFFECTEND )";                //command和检索sql文 关联
                cmdSelect.CommandText = strSelectSql;
                cmdUpdate.CommandText = strUpdateSql;
                //cmdDelete.CommandText = strDeleteSql;
                //cmdInsert.CommandText = strInsertSql;                //sql文中变量通过oracle参数传递
                OracleParameter oraParameter;                //当设置参数的时候
                //oracle参数值可通过SourceColumn属性与更新数据行的字段绑定
                //取得数据值的版本可通过SourceVersion来设定。
                //DataRowVersion.Current 取得数据行中修改后的数据值
                //DataRowVersion.Original 取得数据行中原始的数据值
                //Where条件里面的参数值往往设置为取DataRowVersion.Original版本
                //更新或者插入的参数值往往设置为取DataRowVersion.Current版本                //--------------------------------------------------
                //取得用的oracle command
                oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
                oraParameter.SourceColumn = "LOGINID";//检索字段值
                oraParameter.SourceVersion = DataRowVersion.Current;
                oraParameter.Value = "60";
                cmdSelect.Parameters.Add(oraParameter);//字符型
                //--------------------------------------------------
                //更新用的oracle command
                oraParameter = new OracleParameter("I_EFFECTSTRT", OracleDbType.Date);
                oraParameter.SourceColumn = "EFFECTSTRT";//更新字段值
                oraParameter.SourceVersion = DataRowVersion.Current;
                oraParameter.Value = Convert.ToDateTime("2010/02/14").Date;
                cmdUpdate.Parameters.Add(oraParameter);                oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
                oraParameter.SourceColumn = "LOGINID";//检索字段值
                oraParameter.SourceVersion = DataRowVersion.Original;
                oraParameter.Value = "60";
                cmdUpdate.Parameters.Add(oraParameter);
                ////--------------------------------------------------
                ////删除用的oracle command
                //oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
                //oraParameter.SourceColumn = "LOGINID";//检索字段值
                //oraParameter.SourceVersion = DataRowVersion.Original;
                //oraParameter.Value = "50";
                //cmdDelete.Parameters.Add(oraParameter);                ////--------------------------------------------------
                ////追加用的oracle command
                //oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2);
                //oraParameter.SourceColumn = "LOGINID";//追加字段值
                //oraParameter.SourceVersion = DataRowVersion.Current;
                //oraParameter.Value = "70";
                //cmdInsert.Parameters.Add(oraParameter);                //oraParameter = new OracleParameter("I_EFFECTSTRT", OracleDbType.Varchar2);
                //oraParameter.SourceColumn = "EFFECTSTRT";//追加字段值
                //oraParameter.Value = "2010/02/14";
                //oraParameter.SourceVersion = DataRowVersion.Current;
                //cmdInsert.Parameters.Add(oraParameter);                //oraParameter = new OracleParameter("I_EFFECTEND", OracleDbType.Varchar2);
                //oraParameter.SourceColumn = "EFFECTEND";//追加字段值
                //oraParameter.Value = "2010/02/14";
                //oraParameter.SourceVersion = DataRowVersion.Current;
                //cmdInsert.Parameters.Add(oraParameter);                using (OracleDataAdapter dataAdapter = new OracleDataAdapter())
                {
                    int nRecCount = 0;
                    DataSet i_Data = new DataSet();                    dataAdapter.SelectCommand = cmdSelect;
                    dataAdapter.Fill(i_Data, "TB_USER");
                    //dataAdapter.InsertCommand = cmdInsert;//追加command设置
                    dataAdapter.UpdateCommand = cmdUpdate;//更新command设置
                    //dataAdapter.DeleteCommand = cmdDelete;//删除command设置
                    nRecCount = dataAdapter.Update(i_Data, "TB_USER");//数据更新
                    //this.GridView1.DataSource = i_Data;
                    //GridView1.DataBind();//将控件及其所有子控件绑定到指定的数据源
                }
                // Close and Dispose OracleConnection object
                con.Close();
                con.Dispose();            }
            catch (Exception ex)
            {                throw ex;
            }
        }
    }
}

Answer 1

using System; 
using System.Data; 
using Oracle.DataAccess.Client; 
using System.Web.Configuration; namespace oraModify 
{ 
    public partial class _Default : System.Web.UI.Page 
    { 
        protected void Page_Load(object sender, EventArgs e) 
        { 
            try 
            { 
                String connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString; 
                OracleConnection con = new OracleConnection(connectionString); 
                con.Open();                 //// Create the command 
                OracleCommand cmdSelect = new OracleCommand("", con); 
                // sql文 
                OracleCommand cmdUpdate = new OracleCommand("", con);                 //select statement 
                string strSelectSql = "SELECT * FROM TBUSER WHERE  LOGINID = :I_LOGINID "; 
                string strUpdateSql = "UPDATE TBUSER SET EFFECTSTRT = :I_EFFECTSTRT  WHERE  LOGINID = :I_LOGINID ";                 //command和检索sql文 关联 
                cmdSelect.CommandText = strSelectSql; 
                cmdUpdate.CommandText = strUpdateSql;                 //sql文中变量通过oracle参数传递 
                OracleParameter oraParameter;                 //-------------------------------------------------- 
                //取得用的oracle command 
                oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2); 
                oraParameter.SourceColumn = "LOGINID";//检索字段值 
                oraParameter.SourceVersion = DataRowVersion.Current; 
                oraParameter.Value = "60"; 
                cmdSelect.Parameters.Add(oraParameter);//字符型 
                //-------------------------------------------------- 
                //更新用的oracle command 
                oraParameter = new OracleParameter("I_EFFECTSTRT", OracleDbType.Date); 
                oraParameter.SourceColumn = "EFFECTSTRT";//更新字段值 
                oraParameter.SourceVersion = DataRowVersion.Current; 
                oraParameter.Value = Convert.ToDateTime("2010/02/14").Date; 
                cmdUpdate.Parameters.Add(oraParameter);                 oraParameter = new OracleParameter("I_LOGINID", OracleDbType.Varchar2); 
                oraParameter.SourceColumn = "LOGINID";//检索字段值 
                oraParameter.SourceVersion = DataRowVersion.Original; 
                oraParameter.Value = "60"; 
                cmdUpdate.Parameters.Add(oraParameter);                 using (OracleDataAdapter dataAdapter = new OracleDataAdapter()) 
                { 
                    int nRecCount = 0; 
                    DataSet i_Data = new DataSet();                     dataAdapter.SelectCommand = cmdSelect; 
                    dataAdapter.Fill(i_Data, "TB_USER"); 
                    //dataAdapter.InsertCommand = cmdInsert;//追加command设置 
                    dataAdapter.UpdateCommand = cmdUpdate;//更新command设置 
                    nRecCount = dataAdapter.Update(i_Data, "TB_USER");//数据更新 
                } 
                // Close and Dispose OracleConnection object 
                con.Close(); 
                con.Dispose(); 
            } 
            catch (Exception ex) 
            { 
                throw ex; 
            } 
        } 
    } 
} 整理了一下，方便看。：）

Answer 2

你看看生成的Update语句是否正确？

Answer 3

断点调试，得到Update语句在数据库中执行；

Answer 4

这个问题我也遇到过  解决办法  就是你把数据源里的数据重新赋值就OKforeach（datarow row in dt.rows)
{
    row[0]=row[0].tostring();
}然后再执行 dataAdapter.Update(dt）；就可以了我也一直想搞明白 dt是不是有update的标志

Answer 5

我也考虑是不是dataAdapter.Update(dt）的写法有问题。编辑表格时这个写法可以节省代码。

Answer 6

String connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString; 
                
using (OracleConnection connection = new OracleConnection(connectionString))
        {            
                OracleDataAdapter dataAdpater = new OracleDataAdapter(
   "SELECT * FROM TBUSER", connection);            dataAdpater.UpdateCommand = new SqlCommand(
               "UPDATE TBUSER SET EFFECTSTRT = I_EFFECTSTRT  WHERE  LOGINID = I_LOGINID ", connection);
 
            OracleParameter parameter = new OracleParameter("I_EFFECTSTRT", SqlDbType.Date);            parameter.SourceColumn = "EFFECTSTRT";//更新字段值            dataAdpater.UpdateCommand.Parameters.Add(parameter);            parameter = dataAdpater.UpdateCommand.Parameters.Add(
              "I_LOGINID", SqlDbType.Varchar2);            //看看这里 I_LOGINID 的长度，需要的话设置一下长度            parameter.SourceColumn = "LOGINID";
            parameter.SourceVersion = DataRowVersion.Original;            //关键
            DataSet dataSet = new DataSet();
            dataAdpater.Fill(dataSet, "TBUSER");            //这里只是更新了第一行,如果你想更新LOGINID  为60的所有记录，遍历DataSet 分别赋值即可
            DataRow row = dataSet.Tables["TBUSER"].Rows[0];
            row["EFFECTSTRT"] = Convert.ToDateTime("2010/02/14").Date;            dataAdpater.Update(dataSet, "TBUSER");
            
        }