How to add records to an Oracle table with foreign keys?

0

I'm trying to do CRUD operations in my Oracle DB through C# WPF.

I created a DataGrid displaying all the rows in my table, which I was able to connect to my DB. However, when I try to input values in the form on the left side, I get this error: System.InvalidOperationException: オブジェクトの現在の状態に問題があるため、操作は有効ではありません。

Project Code (プロジェクト) and Task Code (分類) belong to a different table and are referenced in my main table.

// Sample of how I fill the Project Code combobox
private void fillProjectComboBox()
        {
            string sql = "SELECT PROJECT_CODE, PROJECT_NAME FROM TASK_PROJECT";
            cmd.Connection = conn;

            try
            {
                cmd = new OracleCommand(sql, conn);
                OracleDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    string project = dr.GetString(0);
                    regProjectBox.Items.Add(project);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }

private void RegisterNewButton_Click(object sender, RoutedEventArgs e)
{
    String sql = "INSERT INTO TASK_INFO(USER_ID, ATTENDED_DATE, START_TIME, END_TIME, TASK_HOURS, PROJECT_CODE, TASK_CODE, TASK_DESCRIPTION)" +
        " VALUES(:USER_ID, :ATTENDED_DATE, :START_TIME, :END_TIME, :TASK_HOURS, :PROJECT_CODE, :TASK_CODE, :TASK_DESCRIPTION)";
    cmd = new OracleCommand(sql, conn);
    cmd.Parameters.Add("USER_ID", OracleDbType.Varchar2, 20).Value = "test";
    cmd.Parameters.Add("ATTENDED_DATE", OracleDbType.NChar, 10).Value = regDateTextBox.Text;
    cmd.Parameters.Add("START_TIME", OracleDbType.NChar, 5).Value = regStartTimeTextBox.Text;
    cmd.Parameters.Add("END_TIME", OracleDbType.NChar, 5).Value = regEndTimeTextBox.Text;
    cmd.Parameters.Add("TASK_HOURS", OracleDbType.Int16, 3).Value = int.TryParse(regTaskTimeTextBox.Text, out int result);
    cmd.Parameters.Add("TASK_DESCRIPTION", OracleDbType.Varchar2, 50).Value = regTaskDescTextBox.Text;
    cmd.Parameters.Add("PROJECT_CODE", OracleDbType.NChar, 9).Value = regProjectBox.SelectedValue;
    cmd.Parameters.Add("TASK_CODE", OracleDbType.NChar, 9).Value = regTaskBox.SelectedValue;

    try
    {
        int n = cmd.ExecuteNonQuery();
        if (n > 0)
        {
            MessageBox.Show("Done!");
            this.viewDataGrid();
            conn.Close();
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex);
    }
}

Is there something I should know about adding records into tables with foreign keys? In a different window, I was able to add new records into my Project Code and Task Code tables, so this is only a problem with my main table.

Edited to include error:

Exception thrown: 'Oracle.DataAccess.Client.OracleException' in Oracle.DataAccess.dll
Oracle.DataAccess.Client.OracleException (0x80004005): ORA-02291: integrity constraint (TIMESUSER.SYS_C007051) violated - parent key not found
   Location Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable, OracleLogicalTransaction m_OracleLogicalTransaction)
   Location Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck, OracleLogicalTransaction m_OracleLogicalTransaction)
   Location  Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
   Location Times.ViewListWindow.RegisterNewButton_Click(Object sender, RoutedEventArgs e) 場所 C:\Users\neicy\source\repos\Times\Times\ViewListWindow.xaml.cs:Line 231

Apparently, it was mainly a matter of ordering the cmd.Parameters.Add according to how you insert them in your query. Refer to this for more info CORRECTED VERSION:

private void fillProjectComboBox()
        {
            DataTable dtProject = new DataTable();
            string sql = "SELECT ID, PROJECT_CODE FROM TASK_PROJECT ORDER BY PROJECT_CODE";
            var cmd = new OracleCommand(sql, conn);
            using (OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(cmd))
            {
                oracleDataAdapter.Fill(dtProject);
            }
            regProjectBox.SelectedValuePath = "ID";
            regProjectBox.DisplayMemberPath = "PROJECT_CODE";
            regProjectBox.ItemsSource = dtProject.DefaultView;
        }

private void RegisterNewButton_Click(object sender, RoutedEventArgs e)
        {
            string sql = "INSERT INTO TASK_INFO(TASK_INFO_ID, USER_ID, ATTENDED_DATE, START_TIME, END_TIME, TASK_HOURS, PROJECT_ID, TASK_ID, TASK_DESCRIPTION)" +
                " VALUES(SEQ_TASK_INFO.NEXTVAL, :USER_ID, :ATTENDED_DATE, :START_TIME, :END_TIME, :TASK_HOURS, :PROJECT_ID, :TASK_ID, :TASK_DESCRIPTION)";
            var cmd = new OracleCommand(sql, conn);
            cmd.Parameters.Add("USER_ID", OracleDbType.Int16).Value = 1;
            cmd.Parameters.Add("ATTENDED_DATE", OracleDbType.NChar, 10).Value = regDateTextBox.Text;
            cmd.Parameters.Add("START_TIME", OracleDbType.NChar, 5).Value = regStartTimeTextBox.Text;
            cmd.Parameters.Add("END_TIME", OracleDbType.NChar, 5).Value = regEndTimeTextBox.Text;
            int taskHours;
            if (int.TryParse(regTaskTimeTextBox.Text, out taskHours))
            {
                cmd.Parameters.Add("TASK_HOURS", OracleDbType.Int16).Value = taskHours;
            }
            else
            {
                cmd.Parameters.Add("TASK_HOURS", OracleDbType.Int16).Value = 0;
            }
            cmd.Parameters.Add("PROJECT_ID", OracleDbType.Int16).Value = 1;
            cmd.Parameters.Add("TASK_ID", OracleDbType.Int16).Value = 1;
            cmd.Parameters.Add("TASK_DESCRIPTION", OracleDbType.Varchar2, 50).Value = regTaskDescTextBox.Text;

            try
            {
                int n = cmd.ExecuteNonQuery();
                if (n > 0)
                {
                    MessageBox.Show("Done!");
                    this.viewDataGrid();
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
                Console.WriteLine(ex.Source);
                Console.WriteLine(ex.Message);
            }

            //this.AUD(sql, 0);
            registerNewButton.IsEnabled = false;
            registerUpdateButton.IsEnabled = true;
            registerDeleteButton.IsEnabled = true;
        }
c#
wpf
oracle
asked on Stack Overflow Jul 23, 2019 by Neicy • edited Jul 25, 2019 by Neicy

1 Answer

0

First check which table the TIMESUSER.SYS_C007051 constraint refers to. As you have a field named USER_ID and the constraint is TIMESUSER.SYS_C007051, the problem could be that you don't have a user named test
Or maybe I am totally wrong because I don't remember if TIMESUSER here is a schema name or a table name. Without knowing which relationship is on error it's hard to guess.
EDIT I can't guess the true origin of the error but I am quite sure it's due to the NCHAR type usage. I don't think it's a good practice to use this datatype as PK/FK furthermore if the data content does not exactly match the field type.
Here how I would do things to avoid this issue (and many others that you might meet later) :
1. In the database, add some ID fields (numeric) to use as PK/FK and some sequences to increase their value (not needed if >= Oracle12c as there is an auto-increment datatype) :

-- Projects
CREATE TABLE TASK_PROJECT (
    ID INT NOT NULL,
    PROJECT_CODE NCHAR(9) NOT NULL,
    PROJECT_NAME NVARCHAR(255),
    CONSTRAINT PK_TASK_PROJECT PRIMARY KEY (ID) CLUSTERED
);
-- Unique index to keep sure that PROJECT_CODE remains unique in the table
CREATE UNIQUE INDEX UX_TASK_PROJECT_CODE  ON TASK_PROJECT(PROJECT_CODE);
-- Sequence to use to increment TASK_PROJECT.ID
CREATE SEQUENCE SEQ_TASK_PROJECT (
  START WITH 1
  INCREMENT BY 1
);

-- Tasks
CREATE TABLE TASK_TASK (
    ID INT NOT NULL,
    TASK_CODE NCHAR(9) NOT NULL,
    TASK_NAME NVARCHAR(255),
    CONSTRAINT PK_TASK_TASK PRIMARY KEY (ID) CLUSTERED
);
-- Unique index to keep sure that TASK_CODE remains unique in the table
CREATE UNIQUE INDEX UX_TASK_TASK_CODE  ON TASK_TASK(TASK_CODE);
-- Sequence to use to increment TASK_TASK.ID
CREATE SEQUENCE SEQ_TASK_TASK (
  START WITH 1
  INCREMENT BY 1
);

-- Users
CREATE TABLE TASK_USER (
    ID INT NOT NULL,
    USER_LOGIN VARCHAR2(50),
    CONSTRAINT PK_TASK_USER PRIMARY KEY (ID) CLUSTERED
);
-- Unique index to keep sure that USER_LOGIN remains unique in the table
CREATE UNIQUE INDEX UX_TASK_USER_LOGIN ON TASK_USER(USER_LOGIN);
-- Sequence to use to increment TASK_USER.ID
CREATE SEQUENCE SEQ_TASK_USER (
  START WITH 1
  INCREMENT BY 1
);

-- Task info
CREATE TABLE TASK_INFO (
    TASK_INFO_ID INT NOT NULL,
    USER_ID INT NOT NULL,
    PROJECT_ID INT NOT NULL,
    TASK_ID INT NOT NULL,
    ATTENDED_DATE NCHAR(10),
    START_TIME NCHAR(5),
    END_TIME NCHAR(5),
    TASK_HOURS INT,
    TASK_DESCRIPTION VARCHAR2(50),
    CONSTRAINT PK_TASK_INFO PRIMARY KEY (TASK_INFO_ID) CLUSTERED,
    CONSTRAINT FK_TASK_INFO_TASK_PROJECT FOREIGN KEY (PROJECT_ID) REFERENCES TASK_PROJECT(ID),
    CONSTRAINT FK_TASK_INFO_TASK_TASK FOREIGN KEY (TASK_ID) REFERENCES TASK_TASK(ID),
    CONSTRAINT FK_TASK_INFO_TASK_USER FOREIGN KEY (USER_ID) REFERENCES TASK_USER(ID)
);
-- Sequence to use to increment TASK_INFO.ID
CREATE SEQUENCE SEQ_TASK_INFO (
  START WITH 1
  INCREMENT BY 1
);
  1. On C# application side :

        // Sample of how I fill the Project Code combobox
        private void fillProjectComboBox()
        {
            DataTable dtProject = new DataTable();

            // For the moment, you don't seem to use the field PROJECT_NAME, so no need to retrieve it
            //string sql = "SELECT PROJECT_ID, PROJECT_CODE, PROJECT_NAME FROM TASK_PROJECT";
            // I added an ORDER BY PROJECT_CODE to ensure the order in the combo box
            string sql = "SELECT PROJECT_ID, PROJECT_CODE FROM TASK_PROJECT ORDER BY PROJECT_CODE";
            var cmd = new OracleCommand(sql, conn);
            using (OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(cmd))
            {
                oracleDataAdapter.Fill(dtProject);
            }
            regProjectBox.SelectedValuePath = "PROJECT_ID";
            regProjectBox.DisplayMemberPath = "PROJECT_CODE";
            regProjectBox.ItemsSource = dtProject.DefaultView;
        }

        private void RegisterNewButton_Click(object sender, RoutedEventArgs e)
        {
            // Here we use TASK_ID and PROJECT_ID instead of TASK_CODE and PROJECT_CODE
            string sql = "INSERT INTO TASK_INFO(TASK_INFO_ID, USER_ID, ATTENDED_DATE, START_TIME, END_TIME, TASK_HOURS, PROJECT_ID, TASK_ID, TASK_DESCRIPTION)" +
                " VALUES(SEQ_TASK_INFO.NEXTVAL, :USER_ID, :ATTENDED_DATE, :START_TIME, :END_TIME, :TASK_HOURS, :PROJECT_ID, :TASK_ID, :TASK_DESCRIPTION)";
            var cmd = new OracleCommand(sql, conn);
            // Here I changed the defintion of USER_ID from login to a numeric identifier  of this login
            cmd.Parameters.Add("USER_ID", OracleDbType.Int16).Value = 1; // Identifier related to the user "test"
            cmd.Parameters.Add("ATTENDED_DATE", OracleDbType.NChar, 10).Value = regDateTextBox.Text;
            cmd.Parameters.Add("START_TIME", OracleDbType.NChar, 5).Value = regStartTimeTextBox.Text;
            cmd.Parameters.Add("END_TIME", OracleDbType.NChar, 5).Value = regEndTimeTextBox.Text;
            // WRONG !!!- TryParse returns a boolean that indicates if the parsing was successful or not
            //cmd.Parameters.Add("TASK_HOURS", OracleDbType.Int16, 3).Value = int.TryParse(regTaskTimeTextBox.Text, out int result);
            int taskHours;
            if (int.TryParse(regTaskTimeTextBox.Text, out taskHours))
            {
                cmd.Parameters.Add("TASK_HOURS", OracleDbType.Int16).Value = taskHours;
            } else {
                // Handle task hours if no numeric value
                cmd.Parameters.Add("TASK_HOURS", OracleDbType.Int16).Value = 0;
            }
            cmd.Parameters.Add("TASK_DESCRIPTION", OracleDbType.Varchar2, 50).Value = regTaskDescTextBox.Text;
            // Change Project code to project id
            // Explict cast as int to be sure you assign a numeric value
            cmd.Parameters.Add("PROJECT_ID", OracleDbType.Int16).Value = (int)regProjectBox.SelectedValue;
            // Change Task code to task id
            // Explict cast as int to be sure you assign a numeric value
            cmd.Parameters.Add("TASK_ID", OracleDbType.Int16).Value = (int)regTaskBox.SelectedValue;
        }
answered on Stack Overflow Jul 23, 2019 by Olivier Depriester • edited Jul 25, 2019 by Olivier Depriester

User contributions licensed under CC BY-SA 3.0