ASP.NET C# connect to Oracle database to update data failture

Question

My project is ASP.NET WebForm with C#. I wanted to update Oracle data. So I write the C# code as below:

But the cmdQry2.ExecuteNonQuery() returns zero and no data will be updated. Anyone knows whether SQL or C# have any problem?

DateTime dateTime = DateTime.Now;
string strDateTime = string.Format("{0:000}{1:00}{2:00}{3:00}{4:00}", dateTime.Year - 1911, dateTime.Month, dateTime.Day, dateTime.Hour, dateTime.Minute);
string SQL2 = "update \"UserProfile1\" set \"chLogInDT\" = :strDateTime, \"chLogOutDT\" = :strDateTime2, \"chUserSector\" = '電子書',\"chLogInStat\" = :aIP where RTrim(\"chUserID\") = :chUserID ";

Conn.Open();
using (var cmdQry2 = Conn.CreateCommand())
{
    string ip = CommonUse.GetIPAddress();
    cmdQry2.CommandText = SQL2;
    cmdQry2.CommandType = CommandType.Text;
    cmdQry2.Parameters.Add("chUserID", this.txtAccount.Text.Trim());
    cmdQry2.Parameters.Add("strDateTime", strDateTime);
    cmdQry2.Parameters.Add("strDateTime2", strDateTime);
    cmdQry2.Parameters.Add("aIP", ip);

    try
    {
        int n = cmdQry2.ExecuteNonQuery();
        Conn.Close();
    }
    catch (Exception ex)
    {
        Debug.WriteLine(ex.Message);
    }
}

Are you sure you have to quote the identifiers (table name, column names)? This is quite uncommon in Oracle. — Wernfried Domscheit
– Wernfried Domscheit, Commented Feb 1, 2018 at 9:52

Meenangel · Accepted Answer · 2018-02-03 17:26:32Z

1

If there is no exception thrown, then the update query is syntactically correct but it updates no rows.

This can only mean that the where clause:

RTrim("chUserID") = :chUserID

Does not match any rows in the table. Perhaps the trimmed string returned from this.txtAccount.Text.Trim() isn't quite the same as the value in the table.

Edit: also check that the BindByName property of the Oracle command object is set to true. Otherwise the bind variables may be set by the order they are added rather than by their name

edited Feb 3, 2018 at 17:26

Meenangel

32 bronze badges

answered Feb 2, 2018 at 15:11

Meenangel

663 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ASP.NET C# connect to Oracle database to update data failture

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related