将数据插入Oracle DB不起作用 [英] Insert data into Oracle DB Not working

问题描述

我尝试将一些数据插入到Oracle表中。代码没有任何警告或警告。但是，没有数据插入到Oracle表中。相关代码如下。我错过了什么吗？在此先感谢您的咨询。

 ...... 
 string sTblName2 =Tbl_TestDestination; 
 OracleCommand command = new OracleCommand（）; 
 command.CommandText =select * from+ sTblName2; 
 command.CommandType = CommandType.Text; 
 command.Connection = oraclConn; 
 OracleDataAdapter oraAdapter = new OracleDataAdapter（command）; 
 OracleCommandBuilder cb = new OracleCommandBuilder（oraAdapter）; 
 DataSet dataSet = new DataSet（）; 
 oraAdapter.Fill（dataSet，sTblName2）; 
 
 for（int i = 0; i< tblSource.Rows.Count; i ++）{
 DataRow sRow = tblSource.Rows [i]; 
 DataRow dRow = dataSet.Tables [0] .NewRow（）; 
 
 //步骤4：从源表中读取记录
 dRow [dataSet.Tables [0] .Columns [TrkID]。ColumnName] = sRow [tblSource.Columns [TrkID ] .ColumnName]的ToString（）; 
 dRow [dataSet.Tables [0] .Columns [PointName]。ColumnName] = sRow [tblSource.Columns [PointName]。ColumnName] .ToString（）; 
 dRow [dataSet.Tables [0] .Columns [CircuitName]。ColumnName] = sRow [tblSource.Columns [CircuitName]。ColumnName] .ToString（）; 
 dRow [dataSet.Tables [0] .Columns [TrackName]。ColumnName] = sRow [tblSource.Columns [TrackName]。ColumnName] .ToString（）; 
 dRow [dataSet.Tables [0] .Columns [LRSSegID]。ColumnName] = sRow [tblSource.Columns [LRSSegID]。ColumnName] .ToString（）; 
 dRow [dataSet.Tables [0] .Columns [Chainmarker]。ColumnName] = Convert.ToDouble（sRow [tblSource.Columns [From_Measure]。ColumnName] .ToString（））; 
 dRow [dataSet.Tables [0] .Columns [Length]。ColumnName] = Convert.ToDouble（sRow [tblSource.Columns [Adj_Length]。ColumnName] .ToString（））; 
 dRow [dataSet.Tables [0] .Columns [Time]。ColumnName] = Convert.ToDateTime（DateTime.Now.ToShortDateString（））; 
 System.Threading.Thread.Sleep（1000）; // Miliseconds 
 
 //步骤5：将记录写入目的地表
 dataSet.Tables [sTblName2] .Rows.Add（dRow）; 
 dataSet.Tables [sTblName2] .AcceptChanges（）; 
 oraAdapter.Update（dataSet，sTblName2）; 
} 
 

解决方案

如果仔细看一下，你会发现问题在于你正在使用'@'表示参数减速，你必须使用'：'符号

 adapter.InsertCommand。 CommandText =  插入 + tblName +  （ID，TrkID，PointName，CircuitName，TrackName，LRSSegID，Time，Chainmarker，Length） + 
   values（：ID，：TrkID，：PointName，：CircuitName，：TrackName，：LRSSegID，：Time，：Chainmarker，：Length） ; 

这里是您解释的事情的解释

在OracleCommand调用的SQL语句中使用命名参数时CommandType.Text，您必须在参数名称前面加上冒号'：'。但是，在存储过程中，或者在代码中的其他位置引用命名参数时（例如，将OracleParameter对象添加到参数属性时），请不要在命名参数前添加冒号：。 .NET Framework数据提供程序for Oracle自动提供冒号。



http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters(v = vs.110）.aspx [ ^

I tried insert some data into an Oracle table. The code went through w/o any warning or alert. However, no data was inserted into the Oracle table. The related code is below. Did I miss anything? Thanks in advance for your advisory.

......
string sTblName2 = "Tbl_TestDestination";
OracleCommand command = new OracleCommand();
command.CommandText = "select * from " + sTblName2;
command.CommandType = CommandType.Text;
command.Connection = oraclConn;
OracleDataAdapter oraAdapter = new OracleDataAdapter(command);
OracleCommandBuilder cb = new OracleCommandBuilder(oraAdapter);
DataSet dataSet = new DataSet();
oraAdapter.Fill(dataSet, sTblName2);

for (int i = 0; i < tblSource.Rows.Count; i++)   {
    DataRow sRow = tblSource.Rows[i];
    DataRow dRow = dataSet.Tables[0].NewRow();

    // Step 4:  Read a record from Source Table
    dRow[dataSet.Tables[0].Columns["TrkID"].ColumnName] = sRow[tblSource.Columns["TrkID"].ColumnName].ToString();
    dRow[dataSet.Tables[0].Columns["PointName"].ColumnName] = sRow[tblSource.Columns["PointName"].ColumnName].ToString();
    dRow[dataSet.Tables[0].Columns["CircuitName"].ColumnName] = sRow[tblSource.Columns["CircuitName"].ColumnName].ToString();
    dRow[dataSet.Tables[0].Columns["TrackName"].ColumnName] = sRow[tblSource.Columns["TrackName"].ColumnName].ToString();
    dRow[dataSet.Tables[0].Columns["LRSSegID"].ColumnName] = sRow[tblSource.Columns["LRSSegID"].ColumnName].ToString();
    dRow[dataSet.Tables[0].Columns["Chainmarker"].ColumnName] = Convert.ToDouble(sRow[tblSource.Columns["From_Measure"].ColumnName].ToString());
    dRow[dataSet.Tables[0].Columns["Length"].ColumnName] = Convert.ToDouble(sRow[tblSource.Columns["Adj_Length"].ColumnName].ToString());
    dRow[dataSet.Tables[0].Columns["Time"].ColumnName] = Convert.ToDateTime(DateTime.Now.ToShortDateString());
    System.Threading.Thread.Sleep(1000);         // Miliseconds

    // Step 5:  Write Record to Destination Table
    dataSet.Tables[sTblName2].Rows.Add(dRow);
    dataSet.Tables[sTblName2].AcceptChanges();
    oraAdapter.Update(dataSet, sTblName2);
}

解决方案

If you take look at it carefully you will see the problem is that you are using '@' for parameter deceleration while you must use ':' symbol

adapter.InsertCommand.CommandText = "Insert into " + tblName + " (ID, TrkID, PointName, CircuitName, TrackName, LRSSegID, Time, Chainmarker, Length) " +
 "values(:ID, :TrkID, :PointName, :CircuitName, :TrackName, :LRSSegID, :Time, :Chainmarker, :Length) ";

here is the explanation of the thing that you explained
When using named parameters in an SQL statement called by an OracleCommand of CommandType.Text, you must precede the parameter name with a colon ':'. However, in a stored procedure, or when referring to a named parameter elsewhere in your code (for example, when adding OracleParameter objects to the Parameters property), do not precede the named parameter with a colon ':'. The .NET Framework Data Provider for Oracle supplies the colon automatically.

http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters(v=vs.110).aspx[^]

这篇关于将数据插入Oracle DB不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！