CODEWITHSUNDEEP
c#asp.netoracle-databaseqodp.net
Akira Chen
Akira Chen

Reputation: 73

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

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

Upvotes: 0

Views: 654

Answers (1)

Meenangel
Meenangel

Reputation: 66

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

Upvotes: 1

Related Questions