以 Oracle 序列作为主键在数据绑定表单中添加新行

发布于 2024-09-05 16:20:31 字数 1248 浏览 5 评论 0原文

我正在将 C# 与 Oracle 11g 连接。我有一个使用 Oracle 数据适配器填充的数据表。

OracleDataAdapter da;
DataTable dt = new DataTable();

da = new OracleDataAdapter("SELECT * FROM Author", con);
da.Fill(dt);

我有几个文本框将数据绑定到数据表中的各个行。

txtAuthorID.DataBindings.Add("Text", dt, "AUTHORID");
txtFirstName.DataBindings.Add("Text", dt, "FIRSTNAME");
txtLastName.DataBindings.Add("Text", dt, "LASTNAME");
txtAddress.DataBindings.Add("Text", dt, "ADDRESS");
txtTelephone.DataBindings.Add("Text", dt, "TELEPHONE");
txtEmailAddress.DataBindings.Add("Text", dt, "EMAIL");

我在文本框下方还有一个 DataGridView，显示数据表的内容。

dgvAuthor.DataSource = dt;

现在，当我想添加新行时，我会

bm.AddNew();

在 Form_Load 中将 bm 定义为当

BindingManagerBase bm;
bm = this.BindingContext[dt];

输入并验证所有信息后单击“保存”按钮时，我会这样做

this.BindingContext[dt].EndCurrentEdit();

try
{
da.Update(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

但是问题出现在我通常在数据库（使用 SQL Plus），我使用 my_pk_sequence.nextval 作为主键。但是当我在此方法中添加新行时如何指定呢？

我发现了这个异常 ORA-01400: 无法将 NULL 插入 ("SYSMAN".AUTHOR.AUTHORID") 这很明显，因为没有为主键指定任何内容。如何解决这个问题？提前非常感谢:)

原文

I am connecting C# with Oracle 11g. I have a DataTable which i fill using an Oracle Data Adapter.

OracleDataAdapter da;
DataTable dt = new DataTable();

da = new OracleDataAdapter("SELECT * FROM Author", con);
da.Fill(dt);

I have few text boxes that I have databound to various rows in the data table.

txtAuthorID.DataBindings.Add("Text", dt, "AUTHORID");
txtFirstName.DataBindings.Add("Text", dt, "FIRSTNAME");
txtLastName.DataBindings.Add("Text", dt, "LASTNAME");
txtAddress.DataBindings.Add("Text", dt, "ADDRESS");
txtTelephone.DataBindings.Add("Text", dt, "TELEPHONE");
txtEmailAddress.DataBindings.Add("Text", dt, "EMAIL");

I also have a DataGridView below the Text Boxes, showing the contents of the DataTable.

dgvAuthor.DataSource = dt;

Now when I want to add a new row, i do

bm.AddNew();

where bm is defined in Form_Load as

BindingManagerBase bm;
bm = this.BindingContext[dt];

And when the save button is clicked after all the information is entered and validated, i do

this.BindingContext[dt].EndCurrentEdit();

try
{
da.Update(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

However the problem comes where when I usually enter a row to the database (using SQL Plus) , I use a my_pk_sequence.nextval for the primary key. But how do i specify that when i add a new row in this method?

I catch this exception
ORA-01400: cannot insert NULL into ("SYSMAN".AUTHOR.AUTHORID") which is obvious because nothing was specified for the primary key. How do get around this? Thanx a lot in advance :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

简单 2024-09-12 16:20:31

一种解决方案是创建将在插入时自动生成值的触发器。
像这样的东西：

CREATE OR REPLACE TRIGGER trigger_name 
   BEFORE INSERT ON SYSMAN.AUTHOR.AUTHORID 
   REFERENCING 
      OLD AS OLD 
      NEW AS NEW 
   FOR EACH ROW      
   BEGIN
       SELECT my_sequence.NEXTVAL
       INTO :NEW.AUTHORID
       FROM DUAL;
   END;

编辑：

这是来自维基百科关于 DUAL 表的引用：

DUAL 表是一种特殊的单行表，默认情况下存在于所有 Oracle 数据库安装中。

由于 DUAL 表在 ORACLE 中很重要，因此您可以在以下资源中了解它：

One solution is to create trigger that will automatically generate value on insert.
Something like this:

CREATE OR REPLACE TRIGGER trigger_name 
   BEFORE INSERT ON SYSMAN.AUTHOR.AUTHORID 
   REFERENCING 
      OLD AS OLD 
      NEW AS NEW 
   FOR EACH ROW      
   BEGIN
       SELECT my_sequence.NEXTVAL
       INTO :NEW.AUTHORID
       FROM DUAL;
   END;

Edit:

Here is quote from Wikipedia on DUAL table: