Insert data into Oracle DB Not working

Question

0.00/5 (No votes)

See more:

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);
}

Posted 18-Jun-14 3:19am

Member 10454592

Add a Solution

Comments

Om Prakash Pant 18-Jun-14 9:29am

Check the example for insert here:
http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledataadapter.insertcommand(v=vs.110).aspx

[no name] 18-Jun-14 10:29am

Thanks for your feedback. I revised my code by referring to the link you provided withe adding the InsertCommand as(see below). But the result is the same - No data is saved.
adapter.InsertCommand = connection.CreateCommand();
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) ";
AddParameters(adapter.InsertCommand, "ID", "TrkID", "PointName", "CircuitName",
"TrackName", "LRSSegID", "Time", "Chainmarker", "Length");
...
private void AddParameters(OracleCommand cmd, params string[] cols) {
foreach (string column in cols) {
cmd.Parameters.Add("@" + column, OracleDbType.Char, 0, column);
}
}

2 solutions

Solution 2

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[^]

Posted 18-Jun-14 5:18am

Jafarinejadvazifehkhorani

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jafarinejadvazifehkhorani · Accepted Answer · 2014-06-18T04:51:00

Solution 1

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

SQL

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) ";

Posted 18-Jun-14 4:51am

Jafarinejadvazifehkhorani

Comments

[no name] 18-Jun-14 11:05am

Jafarine:
In another project, I used @ rather than :, and it worked well. I tried yours, to change @ to:, but it did not work well. I guess something wrong in my code. Then, I changed the code as below, it went through. I really don't know why. Anyway, I will Accept your solution. Thanks for help.

string s = "INSERT into " + sTblName2 +
" (ID, TrkID, PointName, CircuitName, TrackName, LRSSegID, Time, Chainmarker, Length) values(" +
"'" + dRow[dataSet.Tables[0].Columns["ID"].ColumnName] + "'," +
"'" + dRow[dataSet.Tables[0].Columns["TrkID"].ColumnName] + "'," +
"'" + dRow[dataSet.Tables[0].Columns["PointName"].ColumnName] + "'," +
"'" + dRow[dataSet.Tables[0].Columns["CircuitName"].ColumnName] + "'," +
"'" + dRow[dataSet.Tables[0].Columns["TrackName"].ColumnName] + "'," +
"'" + dRow[dataSet.Tables[0].Columns["LRSSegID"].ColumnName] + "'," +
"TO_DATE('22/April/2011 8:30:00AM','DD/MON/YY HH:MI:SSAM')," +
"'" + dRow[dataSet.Tables[0].Columns["Chainmarker"].ColumnName] + "'," +
"'" + dRow[dataSet.Tables[0].Columns["Length"].ColumnName] + "') ";
OracleCommand comm = new OracleCommand(s, oracleConn);
comm.ExecuteNonQuery();