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;
}
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
);
// 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;
}
User contributions licensed under CC BY-SA 3.0