有Oracle、C#开发经验的过来看看...

Question

我最近用C#编写程序，试图通过调用OracleDataAdapter的Update方法结合DataSet更新数据，但遭到失败，具体做法是： 
  动态创建OracleCommand，编写Update语句，添加OracleParameter对象，设置SourceColumn属性； 
  将OracleCommand实例赋值给OracleDataAdapter的UpdateCommand，执行Update方法； 
  最后失败（再Update方法的执行中，错误提示：未处理的“System.Data.OracleClient.OracleException”类型的异常出现在 system.data.dll 中） 但是使用MS SQL Server时没有错误，功能正确执行完成，这是什么原因？

Answer 1

- $exception {System.Data.OracleClient.OracleException} System.Data.OracleClient.OracleException
- System.SystemException {"ORA-01036: 非法的变量名/编号\n"} System.SystemException
- System.Exception {"ORA-01036: 非法的变量名/编号\n" } System.Exception
System.Object {System.Data.OracleClient.OracleException} System.Object
_className null string
_COMPlusExceptionCode -532459699 int
_exceptionMethod <未定义的值> System.Reflection.MethodBase
_exceptionMethodString null string
_helpURL null string
_HResult -2146233087 int
_innerException { } System.Exception
_message "系统错误。" string
_remoteStackIndex 0 int
_remoteStackTraceString null string
_source null string
+ _stackTrace {System.Array} System.Object
_stackTraceString null string
_xcode -532459699 int
_xptrs 0 int
HelpLink null string
HResult -2146233087 int
InnerException { } System.Exception
Message "ORA-01036: 非法的变量名/编号\n" string
Source "System.Data" string
StackTrace "   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)\r\n   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)\r\n   at WindowsApplication1.TestForm.UpdateSBLB(SBLBData sblb) in d:\\生产基础类\\windowsapplication1\\tstform.cs:line 219\r\n   at WindowsApplication1.TestForm.button2_Click(Object sender, EventArgs e) in d:\\生产基础类\\windowsapplication1\\tstform.cs:line 167\r\n   at System.Windows.Forms.Control.OnClick(EventArgs e)\r\n   at System.Windows.Forms.Button.OnClick(EventArgs e)\r\n   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)\r\n   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)\r\n   at System.Windows.Forms.Control.WndProc(Message& m)\r\n   at System.Windows.Forms.ButtonBase.WndProc(Message& m)\r\n   at System.Windows.Forms.Button.WndProc(Message& m)\r\n   at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)\r\n   at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)\r\n   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)\r\n   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)\r\n   at System.Windows.Forms.ComponentManager.System.Windows.Forms.UnsafeNativeMethods+IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)\r\n   at System.Windows.Forms.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)\r\n   at System.Windows.Forms.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)\r\n   at System.Windows.Forms.Application.Run(Form mainForm)\r\n   at WindowsApplication1.TestForm.Main() in d:\\生产基础类\\windowsapplication1\\tstform.cs:line 132" string
+ TargetSite {System.Reflection.RuntimeMethodInfo} System.Reflection.MethodBase
code 1036 int
Code 1036 int
Message "ORA-01036: 非法的变量名/编号\n" string
message "ORA-01036: 非法的变量名/编号\n" string

Answer 2

把你的mail給我,我給一個從美國學來的東西給你看看[email protected]

Answer 3

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using SCLibrary;
using SCLibrary.Facade;
using SCLibrary.Data;namespace WindowsApplication1
{
/// <summary>
/// Form1 的摘要说明。
/// </summary>
public class TestForm : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Button button2;
private System.Windows.Forms.Button button3;
/// <summary>
/// 必需的设计器变量。
/// </summary>
/// 
private OracleDataAdapter dsAdapter; private const String BIANHAO_PARM      = "@BIANHAO";
private const String PAILIEXUHAO_PARM  = "@PAILIEXUHAO";
private const String SHEBEILEIBIE_PARM = "@SHEBEILEIBIE";
private const String BEIZHU_PARM       = "@BEIZHU";

private String cnString;
private OracleCommand loadCommand;
private OracleCommand insertCommand;
private OracleCommand updateCommand;
private System.Windows.Forms.DataGrid grd_SQL; private System.ComponentModel.Container components = null; public TestForm()
{
InitializeComponent(); cnString = "user id=ergl;data source=ergl;password=dddd";
dsAdapter = new OracleDataAdapter();
        
dsAdapter.TableMappings.Add("Table", SBLBData.TABLENAME);
} protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null) 
{
components.Dispose();
}
}
base.Dispose( disposing );
} #region Windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.button2 = new System.Windows.Forms.Button();
this.button3 = new System.Windows.Forms.Button();
this.grd_SQL = new System.Windows.Forms.DataGrid();
((System.ComponentModel.ISupportInitialize)(this.grd_SQL)).BeginInit();
this.SuspendLayout();
// 
// button1
// 
this.button1.Location = new System.Drawing.Point(8, 200);
this.button1.Name = "button1";
this.button1.TabIndex = 1;
this.button1.Text = "打开";
this.button1.Click += new System.EventHandler(this.button1_Click);
// 
// button2
// 
this.button2.Location = new System.Drawing.Point(96, 200);
this.button2.Name = "button2";
this.button2.TabIndex = 2;
this.button2.Text = "button2";
this.button2.Click += new System.EventHandler(this.button2_Click);
// 
// button3
// 
this.button3.Location = new System.Drawing.Point(184, 200);
this.button3.Name = "button3";
this.button3.TabIndex = 3;
this.button3.Text = "button3";
this.button3.Click += new System.EventHandler(this.button3_Click);
// 
// grd_SQL
// 
this.grd_SQL.DataMember = "变电设备类别";
this.grd_SQL.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.grd_SQL.Location = new System.Drawing.Point(8, 8);
this.grd_SQL.Name = "grd_SQL";
this.grd_SQL.Size = new System.Drawing.Size(384, 184);
this.grd_SQL.TabIndex = 4;
// 
// TestForm
// 
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(400, 238);
this.Controls.Add(this.grd_SQL);
this.Controls.Add(this.button3);
this.Controls.Add(this.button2);
this.Controls.Add(this.button1);
this.Name = "TestForm";
this.Text = "测试";
((System.ComponentModel.ISupportInitialize)(this.grd_SQL)).EndInit();
this.ResumeLayout(false); }
#endregion [STAThread]
static void Main() 
{
Application.Run(new TestForm());
}
private void button1_Click(object sender, System.EventArgs e)
{
DataSet dataSet;
SBLBFacade sblb;

sblb = new SBLBFacade();
dataSet = sblb.GetSBLBData("");
grd_SQL.DataSource = dataSet;
} private void button2_Click(object sender, System.EventArgs e)
{
DataRow  lbRow;
SBLBData dataSet = new SBLBData();
lbRow    = dataSet.Tables[SBLBData.TABLENAME].NewRow();
                    
lbRow[SBLBData.PAILIEXUHAO_FIELD]  = ((DataSet)grd_SQL.DataSource).Tables[SBLBData.TABLENAME].Rows[0][SBLBData.PAILIEXUHAO_FIELD];
lbRow[SBLBData.SHEBEILEIBIE_FIELD] = ((DataSet)grd_SQL.DataSource).Tables[SBLBData.TABLENAME].Rows[0][SBLBData.SHEBEILEIBIE_FIELD];
lbRow[SBLBData.BEIZHU_FIELD]       = ((DataSet)grd_SQL.DataSource).Tables[SBLBData.TABLENAME].Rows[0][SBLBData.BEIZHU_FIELD];
lbRow[SBLBData.BIANHAO_FIELD]      = ((DataSet)grd_SQL.DataSource).Tables[SBLBData.TABLENAME].Rows[0][SBLBData.BIANHAO_FIELD]; dataSet.Tables[SBLBData.TABLENAME].Rows.Add(lbRow);
                    
dataSet.AcceptChanges();
lbRow[SBLBData.BIANHAO_FIELD]      = ((DataSet)grd_SQL.DataSource).Tables[SBLBData.TABLENAME].Rows[0][SBLBData.BIANHAO_FIELD];
                    
UpdateSBLB(dataSet);
//SBLBData继承之DataSet
} private void button3_Click(object sender, System.EventArgs e)
{ } private OracleCommand GetUpdateCommand()
{
if (updateCommand == null)
{            
String updateSql = String.Empty;
StringBuilder sql = new StringBuilder(); sql.Append(String.Format("{0} = {1}, {2} = {3}, {4} = {5}",
SBLBData.PAILIEXUHAO_FIELD, PAILIEXUHAO_PARM, SBLBData.SHEBEILEIBIE_FIELD, SHEBEILEIBIE_PARM,
SBLBData.BEIZHU_FIELD, BEIZHU_PARM));

updateSql = String.Format("Update {0} Set {1} Where {2} = {3}", SBLBData.TABLENAME, sql.ToString(),
SBLBData.BIANHAO_FIELD, BIANHAO_PARM);
updateCommand = new OracleCommand(updateSql, new OracleConnection(cnString));
OracleParameterCollection sqlParams = updateCommand.Parameters;
            
sqlParams.Add(new OracleParameter(BIANHAO_PARM, OracleType.Int32));
sqlParams.Add(new OracleParameter(PAILIEXUHAO_PARM, OracleType.Int32));
sqlParams.Add(new OracleParameter(SHEBEILEIBIE_PARM, OracleType.VarChar, 40));
sqlParams.Add(new OracleParameter(BEIZHU_PARM, OracleType.VarChar, 100)); sqlParams[BIANHAO_PARM].SourceColumn = SBLBData.BIANHAO_FIELD;       
sqlParams[PAILIEXUHAO_PARM].SourceColumn = SBLBData.PAILIEXUHAO_FIELD;
sqlParams[SHEBEILEIBIE_PARM].SourceColumn = SBLBData.SHEBEILEIBIE_FIELD;
sqlParams[BEIZHU_PARM].SourceColumn = SBLBData.BEIZHU_FIELD;
}
            
return updateCommand;
}
public bool UpdateSBLB(SBLBData sblb)
{
if (dsAdapter == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}             dsAdapter.UpdateCommand = GetUpdateCommand();

            
dsAdapter.Update(sblb, SBLBData.TABLENAME);
if (sblb.HasErrors)
{
sblb.Tables[SBLBData.TABLENAME].GetErrors()[0].ClearErrors();
return false;
}
else
{
sblb.AcceptChanges();
return true;
}
} }
}

Answer 4

我是用
  System.Data.OleDb来做的，截至目前还没有发现有什么异常;)
  update语句
  都是这样写的
  "update table set name='"+txtname.text+"',address='"+txtaddress.text+"'";

Answer 5

据说Oracle里面的变量名前面是?而不是@
不知道是不是这个原因。

Answer 6

private const String BIANHAO_PARM      = ":BIANHAO";
private const String PAILIEXUHAO_PARM  = ":PAILIEXUHAO";
private const String SHEBEILEIBIE_PARM = ":SHEBEILEIBIE";
private const String BEIZHU_PARM       = ":BEIZHU";

Answer 7

qimini(循序渐进) :
  您好，问题已经解决，就是参数的问题。青岛我的另一篇问题中回答，哪里还有100分，希望能与你常联系。
[email protected]

Answer 8

sqlParams.Add(new OracleParameter("BIANHAO", OracleType.Int32));
sqlParams.Add(new OracleParameter("PAILIEXUHAO", OracleType.Int32));
sqlParams.Add(new OracleParameter("SHEBEILEIBIE", OracleType.VarChar, 40));
sqlParams.Add(new OracleParameter("BEIZHU", OracleType.VarChar, 100));

Answer 9

private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.button2 = new System.Windows.Forms.Button();
this.button3 = new System.Windows.Forms.Button();
this.grd_SQL = new System.Windows.Forms.DataGrid();
((System.ComponentModel.ISupportInitialize)(this.grd_SQL)).BeginInit();
this.SuspendLayout();
// 
// button1
// 
this.button1.Location = new System.Drawing.Point(8, 200);
this.button1.Name = "button1";
this.button1.TabIndex = 1;
this.button1.Text = "打开";
this.button1.Click += new System.EventHandler(this.button1_Click);
// 
// button2
// 
this.button2.Location = new System.Drawing.Point(96, 200);
this.button2.Name = "button2";
this.button2.TabIndex = 2;
this.button2.Text = "button2";
this.button2.Click += new System.EventHandler(this.button2_Click);
// 
// button3
// 
this.button3.Location = new System.Drawing.Point(184, 200);
this.button3.Name = "button3";
this.button3.TabIndex = 3;
this.button3.Text = "button3";
this.button3.Click += new System.EventHandler(this.button3_Click);
// 
// grd_SQL
// 
this.grd_SQL.DataMember = "变电设备类别";
this.grd_SQL.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.grd_SQL.Location = new System.Drawing.Point(8, 8);
this.grd_SQL.Name = "grd_SQL";
this.grd_SQL.Size = new System.Drawing.Size(384, 184);
this.grd_SQL.TabIndex = 4;
// 
// TestForm
// 
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(400, 238);
this.Controls.Add(this.grd_SQL);
this.Controls.Add(this.button3);
this.Controls.Add(this.button2);
this.Controls.Add(this.button1);
this.Name = "TestForm";
this.Text = "测试";
((System.ComponentModel.ISupportInitialize)(this.grd_SQL)).EndInit();
this.ResumeLayout(false); }
#endregion [STAThread]
static void Main() 
{
Application.Run(new TestForm());
}
private void button1_Click(object sender, System.EventArgs e)
{
DataSet dataSet;
SBLBFacade sblb;

sblb = new SBLBFacade();
dataSet = sblb.GetSBLBData("");
grd_SQL.DataSource = dataSet;
} private void button2_Click(object sender, System.EventArgs e)
{
DataRow  lbRow;
SBLBData dataSet = new SBLBData();
lbRow    = dataSet.Tables[SBLBData.TABLENAME].NewRow();
                    
lbRow[SBLBData.PAILIEXUHAO_FIELD]  = ((DataSet)grd_SQL.DataSource).Tables[SBLBData.TABLENAME].Rows[0][SBLBData.PAILIEXUHAO_FIELD];
lbRow[SBLBData.SHEBEILEIBIE_FIELD] = ((DataSet)grd_SQL.DataSource).Tables[SBLBData.TABLENAME].Rows[0][SBLBData.SHEBEILEIBIE_FIELD];
lbRow[SBLBData.BEIZHU_FIELD]       = ((DataSet)grd_SQL.DataSource).Tables[SBLBData.TABLENAME].Rows[0][SBLBData.BEIZHU_FIELD];
lbRow[SBLBData.BIANHAO_FIELD]      = ((DataSet)grd_SQL.DataSource).Tables[SBLBData.TABLENAME].Rows[0][SBLBData.BIANHAO_FIELD]; dataSet.Tables[SBLBData.TABLENAME].Rows.Add(lbRow);
                    
dataSet.AcceptChanges();
lbRow[SBLBData.BIANHAO_FIELD]      = ((DataSet)grd_SQL.DataSource).Tables[SBLBData.TABLENAME].Rows[0][SBLBData.BIANHAO_FIELD];
                    
UpdateSBLB(dataSet);
//SBLBData继承之DataSet
} private void button3_Click(object sender, System.EventArgs e)
{ } private OracleCommand GetUpdateCommand()
{
if (updateCommand == null)
{            
String updateSql = String.Empty;
StringBuilder sql = new StringBuilder(); sql.Append(String.Format("{0} = {1}, {2} = {3}, {4} = {5}",
SBLBData.PAILIEXUHAO_FIELD, PAILIEXUHAO_PARM, SBLBData.SHEBEILEIBIE_FIELD, SHEBEILEIBIE_PARM,
SBLBData.BEIZHU_FIELD, BEIZHU_PARM));

updateSql = String.Format("Update {0} Set {1} Where {2} = {3}", SBLBData.TABLENAME, sql.ToString(),
SBLBData.BIANHAO_FIELD, BIANHAO_PARM);
updateCommand = new OracleCommand(updateSql, new OracleConnection(cnString));
OracleParameterCollection sqlParams = updateCommand.Parameters;
            
sqlParams.Add(new OracleParameter(BIANHAO_PARM, OracleType.Int32));
sqlParams.Add(new OracleParameter(PAILIEXUHAO_PARM, OracleType.Int32));
sqlParams.Add(new OracleParameter(SHEBEILEIBIE_PARM, OracleType.VarChar, 40));
sqlParams.Add(new OracleParameter(BEIZHU_PARM, OracleType.VarChar, 100)); sqlParams[BIANHAO_PARM].SourceColumn = SBLBData.BIANHAO_FIELD;       
sqlParams[PAILIEXUHAO_PARM].SourceColumn = SBLBData.PAILIEXUHAO_FIELD;
sqlParams[SHEBEILEIBIE_PARM].SourceColumn = SBLBData.SHEBEILEIBIE_FIELD;
sqlParams[BEIZHU_PARM].SourceColumn = SBLBData.BEIZHU_FIELD;
}
            
return updateCommand;
}
public bool UpdateSBLB(SBLBData sblb)
{
if (dsAdapter == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}             dsAdapter.UpdateCommand = GetUpdateCommand();

            
dsAdapter.Update(sblb, SBLBData.TABLENAME);
if (sblb.HasErrors)
{
sblb.Tables[SBLBData.TABLENAME].GetErrors()[0].ClearErrors();
return false;
}
else
{
sblb.AcceptChanges();
return true;
}
} }
}