The sentence doesn't work. Not updating the status. (Working with C# and Oracle)

Erika Fosado 21 Reputation points
2021-08-17T20:38:50.827+00:00

Can anybody tell me what's going wrong? The console doesn't shows any error. What am I missing? Thanks.

private void Aceptar_Btn_Click(object sender, EventArgs e)
{
DataTable dtRecSer = new DataTable();
string qry = "UPDATE " + dbCtx.DbSchema + "TECHW_EMBARQUES_HDR SET FILEW_DONE= 'Y'" +
" WHERE FILEW_DONE = 'N'";
dbMgr.Connect();
dtRecSer = dbMgr.getData(qry);
dbMgr.Disconnect();
}

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,354 questions
{count} votes

Accepted answer
  1. Karen Payne MVP 35,196 Reputation points
    2021-08-17T22:33:44.387+00:00

    Here is what I use for performing updates where in this case OcsMessage is an instance of a class, modifying to use other containers as you see fit such as a DataRow or DataTable (where a DataTable makes zero sense unless updating all rows in the DataTable).

    public bool UpdateRecord(OcsMessage messageRecord)
    {
        mHasException = false;
    
        bool success = false;
    
        var updateStatement = @"
            UPDATE OCS_MESSAGES SET 
                OCS_MESSAGE_TXT      = :MessageText,
                OCS_LANG_CODE        = :LanguageCode,
                OCS_FORM_FIELD_NAME  = :FormFieldName,
                OCS_FORM_FIELD_ORDER = :FormFieldOrder
            WHERE ID = :Identifier";
    
        using (var cn = new OracleConnection() { ConnectionString = "TODO") })
        {
            using (var cmd = new OracleCommand() { Connection = cn })
            {
                try
                {
                    cmd.BindByName = true;
                    cmd.CommandText = updateStatement;
    
                    cmd.Parameters.Add(":MessageText", messageRecord.MessageText);
                    cmd.Parameters[":MessageText"].Size = 2000;
    
                    cmd.Parameters.Add(":LanguageCode", messageRecord.LanguageCode);
                    cmd.Parameters.Add(":FormFieldName", messageRecord.FormFieldName);
                    cmd.Parameters.Add(":FormFieldOrder", messageRecord.FormFieldOrder);
                    cmd.Parameters.Add(":Identifier", messageRecord.id);
    
                    cn.Open();
    
                    cmd.ExecuteNonQuery();
                    success = true;
    
                }
                catch (OracleException oex)
                {
                    // TODO
                }
                catch (Exception ex)
                {
                    // TODO                
                }
            }
        }
    
        return success;
    }
    

    Should never use string concatenation, always used parameters e.g. for Oracle :MessageText and BindByName.


1 additional answer

Sort by: Most helpful
  1. Viorel 112.9K Reputation points
    2021-08-17T20:49:37.857+00:00

    I think that if dbCtx.DbSchema does not end with ".", then you can add it: string qry = "UPDATE " + dbCtx.DbSchema + ".TECHW_EMBARQUES_HDR etc.".

    It is not clear if dbMgr.getData can be used for UPDATE statements. Maybe it is designed for SELECT only? Check if there are other appropriate functions.