none
insert into with two tables using foreign key constraint RRS feed

  • Question

  • I am trying to insert two rows in tables that have a primary / foreign key relationship using DataSets in C# 4.0 but it does not insert anything.

    Here is what I have done:

    1)

    I created my tables (data and test) like this. where test has a one to many relationship to data.

    test1.date1 FK <-> PK Data.data

    (see script below)

    2) I connected a dataadaptor to my SQL 2008 server to C#

    3) I created a little code that tries to insert two rows

     

        RiskDataSet1TableAdapters.testTableAdapter ta = new RiskDataSet1TableAdapters.testTableAdapter();

                RiskDataSet1 ds = new RiskDataSet1();

                RiskDataSet1.dataRow dataRow = ds.data.NewdataRow();

                Guid key = Guid.NewGuid();

                dataRow.data = key;

                RiskDataSet1.testRow testRow = ds.test.NewtestRow();

                testRow.data1 = key;

                ta.Update(ds);

    4) run the code, without exceptions.

     

     

    CREATE TABLE [dbo].[data](

     [data] [uniqueidentifier] NOT NULL,

     CONSTRAINT [PK_data] PRIMARY KEY CLUSTERED 

    (

     [data] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    CREATE TABLE [dbo].[test](

    [data1] [uniqueidentifier] NOT NULL

    ) ON [PRIMARY]

     

    GO

     

    ALTER TABLE [dbo].[test]  WITH CHECK ADD  CONSTRAINT [FK_test_data] FOREIGN KEY([data1])

    REFERENCES [dbo].[data] ([data])

    GO

     

    ALTER TABLE [dbo].[test] CHECK CONSTRAINT [FK_test_data]

    GO

     

     


    Tuesday, April 26, 2011 8:54 PM

Answers

  •  

    Hi lasse,

    Welcome!

    Based on your code and description, I used your SQL script to create my datatable, here is my code and works on my computer.

     static void Main(string[] args)
        {
          DataSet1TableAdapters.dataTableAdapter dataAdapter = new DataSet1TableAdapters.dataTableAdapter();
          DataSet1TableAdapters.testTableAdapter testAdapter = new DataSet1TableAdapters.testTableAdapter();
          DataSet1 ds = new DataSet1();
          dataAdapter.Fill(ds.data);
          testAdapter.Fill(ds.test);
          DataSet1.testRow testRow = ds.test.NewtestRow();
          DataSet1.dataRow dataRow = ds.data.NewdataRow();
          Guid key = Guid.NewGuid();
          dataRow.data = key;
          testRow.data1 = key;
          testRow.SetParentRow(dataRow);
          ds.test.Rows.Add(testRow);
          ds.data.Rows.Add(dataRow);
          dataAdapter.Update(ds.data);
          testAdapter.Update(ds.test);
        }
    

     

    Here is a link about this:  http://www.codeproject.com/KB/database/relationaladonet.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Saturday, April 30, 2011 1:05 PM
    Moderator

All replies

  • I tweaked the code a little but get an exception now.

    Unhandled Exception: System.Data.SqlClient.SqlException: The INSERT statement co

    nflicted with the FOREIGN KEY constraint "FK_test_data". The conflict occurred i

    n database "Risk", table "dbo.data", column 'data'.

    The statement has been terminated.

       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArg

    s rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowU

    pdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMappi

    ng tableMapping)

       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable,

    DataTableMapping tableMapping)

       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)

       at TestDataSet.RiskDataSet1TableAdapters.testTableAdapter.Update(RiskDataSet1

     dataSet) in D:\dev\csharptests\TestDataSet\TestDataSet\TestDataSet\RiskDataSet1

    .Designer.cs:line 1443

       at TestDataSet.Program.Main(String[] args) in D:\dev\csharptests\TestDataSet\

    TestDataSet\TestDataSet\Program.cs:line 33

    Press any key to continue . . .

     

    code:

     

      RiskDataSet1TableAdapters.testTableAdapter ta = new RiskDataSet1TableAdapters.testTableAdapter();

                RiskDataSet1 ds = new RiskDataSet1();

                RiskDataSet1.dataRow dataRow = ds.data.NewdataRow();

                Guid key = Guid.NewGuid();

                dataRow.data = key;

                ds.data.AdddataRow(dataRow);

                RiskDataSet1.testRow testRow = ds.test.NewtestRow();

                testRow.data1 = key;

                ds.test.AddtestRow(testRow);

                ta.Update(ds);

    Tuesday, April 26, 2011 9:00 PM
  •  

    Hi lasse,

    Welcome!

    Based on your code and description, I used your SQL script to create my datatable, here is my code and works on my computer.

     static void Main(string[] args)
        {
          DataSet1TableAdapters.dataTableAdapter dataAdapter = new DataSet1TableAdapters.dataTableAdapter();
          DataSet1TableAdapters.testTableAdapter testAdapter = new DataSet1TableAdapters.testTableAdapter();
          DataSet1 ds = new DataSet1();
          dataAdapter.Fill(ds.data);
          testAdapter.Fill(ds.test);
          DataSet1.testRow testRow = ds.test.NewtestRow();
          DataSet1.dataRow dataRow = ds.data.NewdataRow();
          Guid key = Guid.NewGuid();
          dataRow.data = key;
          testRow.data1 = key;
          testRow.SetParentRow(dataRow);
          ds.test.Rows.Add(testRow);
          ds.data.Rows.Add(dataRow);
          dataAdapter.Update(ds.data);
          testAdapter.Update(ds.test);
        }
    

     

    Here is a link about this:  http://www.codeproject.com/KB/database/relationaladonet.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Saturday, April 30, 2011 1:05 PM
    Moderator