none
Dataset insert operation fails because primary key is required, and primary key value is random value generated by SQL Server RRS feed

  • Question

  • A table in sql server 2008 has a primary key which has a value assigned to it at insert via the default property. Simplest case, pull that table into a dataset and of course the primary key column is marked as required (allowdbnull is false). But I can't insert records because the dataset requires the PK to have a value and of course it doesn't until the new record is created. How can I solve this in a simple manner?

    I'm curious too how it is handled automatically if the PK is an identity column? There is something built into datasets which knows that the identity value will be handled by the db?

    Here is the bit of drag and drop generated code that fails on EndEdit() with the null value error

        Private Sub SaveToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click
            Me.Validate()
            Me.MyBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.MytDataSet)
        End Sub

    I looked around for an answer on this but didn't find a clear solution...but it must be a common issue.

    Monday, December 27, 2010 12:55 AM

All replies

  •  Hi rusticloud,

    Thanks for your post.

    According to your description, you want to insert a record by dateset(filled by dataadapter) automatically. But I am not clear about your "default property", I just guess what it is.

    If it is uniqueidentifier in your database, you should use Guid.NewGuid(); to get a unique key.

    Please follow up, if I misunderstanding.

    Thanks for understanding.

    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.

    Monday, December 27, 2010 8:23 AM
    Moderator
  • Hi Alan

    By "default property" I meant in sql server, the "Default Value or Binding" setting for the PK column.

    In this case I prefer not to use NewGuid(), but rather a random integer generated by this expression: (abs(checksum(newid())))

    I've usually used simple identity columns, and with those, the data table autoincrement property is set to true buy default, with an increment of -1. After the saver operation the value which sql server created as an identity column shows up. Is that intelligence somehow built in to data tables, that they automatically get the new identity value?

    In any case, how do I get the data table to cooperate with the sql server default value? If possible I'd prefer to keep the PK value generation in a single place, and ideally in sql server.

    Monday, December 27, 2010 5:07 PM
  • Hello,

    you should not face any issue in such condition. if your table has a primary key column of type integer and you set the "isidentity" property of that column to true to automatically generate the primary key at the SQL end.

    just specify the insert command of dataadapter explicitly and if your table has id,name and a address column, your insert command should be like "insert into table table1 (name,address) values ('myname', 'India')" ie don't mention the primary key column in the insert statement.


    Snehadeep(MSFT) SQL Developer Team
    Tuesday, December 28, 2010 6:29 AM
  • Hi Snehadeep - thanks for the reply but I think you missed the crucial part of what this thread is about - the PK has a random int value assigned by default, ie it's not an identity column. It's all in the previous messages.
    Tuesday, December 28, 2010 6:52 AM
  •  Hi ruticloud,

    Thanks for your feedback.

    I think @Snehadeep is right, your primary key column has a default value:

    (abs(checksum(newid())))

    I use write sample code to insert a record to database by dataset, my primary key is int(with default "(abs(checksum(newid())))" ), and it works ok on my computer.

     SqlConnection con = new SqlConnection(@"Data Source=alanfish-msft\sqlexpress;Initial Catalog=WeddyData;Integrated Security=True");

                con.Open();

                SqlDataAdapter sda = new SqlDataAdapter("select * from Test",con);

                SqlCommand ac = new SqlCommand("insert into Test(Name) values(@name)",sda.SelectCommand.Connection);

                ac.Parameters.Add("@name", SqlDbType.NVarChar, 20);

                ac.Parameters["@name"].SourceVersion = DataRowVersion.Original;

                ac.Parameters["@name"].SourceColumn = "Name";

                sda.InsertCommand = ac;

                sda.Fill(CustomersDataSet,"Test")

                DataTable dt= CustomersDataSet.Tables["Test"];

                DataRow dr = dt.NewRow();

                dr[1] = "test";

                dt.Rows.Add(dr);

                sda.Update(CustomersDataSet, "Test");

                con.Close();

     

    My datatable's structure in database  is :

    CREATE TABLE [dbo].[Test](

    [ID] [int] NOT NULL,

    [Name] [nchar](10) NULL

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_Test_ID]  DEFAULT (abs(checksum(newid()))) FOR [ID]

    GO

     

    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.

    Tuesday, December 28, 2010 8:58 AM
    Moderator
  • How is that random value generated for the PK?  can't we generate the random numbers from the application code?
    Snehadeep(MSFT) SQL Developer Team
    Tuesday, December 28, 2010 1:08 PM
  • Hi Snehadeep

    As I mentioned a couple of times it's preferred that the PK be generated at the database.

    If it's not possible then yes I know the random int value could be handled at the application.

    But I am asking if it's possible to handle at the database. After all, if one has a standard identity column, the dataset usually assigns a value like -1, -2, but after the dataset pushes the values down to the db the dataset is refreshed with the identity values. The -1, -2 initial values are replaced by 2345, 2347 or whatever in the datagrid after the save operation. Is it possible to do the same with a column that is not an identity column? I'd think so...thus my question.

    Tuesday, December 28, 2010 3:22 PM
  • So Alan how would you make it work with designer tools that vs offers?

    1. Create the database you show
    2. Create a new winforms project
    3. Add a datasource which refs that db and table
    4. Add a new form
    5. Drag and drop the Test table from the Data Sources tab on to the form.
    6. When your run the app and try to add a new record it will not save because the pk is null.

     

    What needs to be adjusted in the dataset designer in order for the new record to be saved, and to get the new random pk from the database? I'd like to know how to implement this using the tooling that vs provides, since I won't normally be creating the dataset in code.

    Monday, January 3, 2011 6:51 PM
  •  

    Hi rusticloud,

    Thanks for your feedback.

    If you want use the dataset which provided by VS, I think you may need provide a default value for the column, I can't find a good way.

    1. I use the last reply's sql create datatable.
    1. Add new Dataset(add dataadapter), dataset's name is "DataSet3"
    2. Here is my code:

    DataSet3 ds = new DataSet3();

               DataSet3TableAdapters.TestTableAdapter tta = new DataSet3TableAdapters.TestTableAdapter();

               tta.Adapter.InsertCommand = new SqlCommand("insert into Test(Name) values(@name)",tta.Connection);

               tta.Adapter.InsertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 20);

               tta.Adapter.InsertCommand.Parameters["@name"].SourceVersion = DataRowVersion.Original;

               tta.Adapter.InsertCommand.Parameters["@name"].SourceColumn = "Name";

               tta.Fill(ds.Test);

               DataSet3.TestRow  dr = ds.Test.NewTestRow();

               ds.Test.AddTestRow(0, "hah");

               tta.Update(ds.Test);

    Please have a try, the default value in dataset will not insert into Database, and it will use Database default setting.

    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.

    Tuesday, January 4, 2011 2:39 AM
    Moderator
  • Here is a vb version of the code you provided:

            Dim ds As DataSet3 = New DataSet3()
            Dim tta As DataSet3TableAdapters.TestTableAdapter = New DataSet3TableAdapters.TestTableAdapter()

            tta.Adapter.InsertCommand = New SqlCommand("insert into Test(Name) values(@name)", tta.Connection)
            tta.Adapter.InsertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 20)
            tta.Adapter.InsertCommand.Parameters("@name").SourceVersion = DataRowVersion.Original
            tta.Adapter.InsertCommand.Parameters("@name").SourceColumn = "Name"
            tta.Fill(ds.Test)
            Dim dr As DataSet3.TestRow = ds.Test.NewTestRow()
            ds.Test.AddTestRow(0, "hah")
            tta.Update(ds.Test)

    You're right - it does insert a new row, and uses the random pk from the db.

    I think what this means is that it's not the dataset or it's "test" datatable that is the issue. It's more the ado that handles the insert, or the the UI component (textbox or grid) that I've been testing with.

    Any ideas on that? Why would the drag and drop created forms or datagridviews cooperate with an identity column so well, but not work with a different type of db-generated PK value?

    Tuesday, January 4, 2011 3:36 AM
  • Hi rusticloud,

    Have you test my first reply's code, it works ok and we should not provide the default value for dataset column. I think the problem is the dataset which you drag from toolbox is Type dataset, so you should fill its all columns.

     

    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.

    Tuesday, January 4, 2011 4:01 AM
    Moderator
  • You mean because the dataset that was created by dragging from the toolbox is typed (you wrote Type), it's not able to resolve the random db generated pk?

    What do I know but I don't think so. To test your code (the most recent code, which I had success with), I changed your reference to DataSet3 to my own dataset, which was created via the Add New data source wizard.

    Also, a db generated pk identity column does work 'out of the box' with the data set wizard created dataset and data table, and any grid or form that consumes it which was created by drag and drop. There really should be some way to get the random ID to work...can you ask someone about this?

    Tuesday, January 4, 2011 4:14 AM
  • Hi rusticloud,

    Do you mean you want get the random ID from client instead of SQL Database? Would your please feel free to let me know? thanks!

    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.

    Tuesday, January 11, 2011 4:13 AM
    Moderator
  • No...the question is and always has been very simple!

    If I have the db set up to generate a random int pk value, why does one get a runtime error at insert for that pk when using the drag and drop visual designers to create the dataset and the form or grid? As I've written before 2x, a db generated pk identity column does work 'out of the box' with the wizard created dataset and data table, and any grid or form that consumes it which was created by drag and drop. If the form or grid can get the a new identity column pk value which the db has generated it should be able to get a new random int pk value which the db has generated.

    Tuesday, January 11, 2011 2:28 PM
  • I started a thread elsewhere (http://social.msdn.microsoft.com/Forums/en/adodotnetdataset/thread/c0735612-1132-40ac-bf62-9f6853c84d9d) on this subject and got nowhere with it. Although two msft people responded, they don't see to be able to figure out what I'm interested in. I don't think the issue is very esoteric and I hope that someone here can shed some light on this issue for me.

    My sql server database generates a random int pk for every insert. It's not an identity column; in sql server the default value for the pk column is set to an expression which returns a random int value. I have found that if I generate a dataset from that database, and then drag and drop a dataviewgrid onto a form, that inserts fail with the message "allowdbnull is false" 

    Here is the bit of drag and drop generated code that fails on EndEdit() with the null value error

        Private Sub SaveToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click
            Me.Validate()
            Me.MyBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.MytDataSet)
        End Sub

    The dataset, reflecting the db, knows that the pk value cannot be null. That makes sense but isn't there a way to inform the dataset (or the grid) that the db will provide the pk value and 'relax' a bit? If the pk column was a identity column, in which case the db would also generate the pk value, the dataset and dataviewgrid would allow the insert to take place.

    • Merged by Helen Zhou Friday, January 21, 2011 7:21 AM more suitable forum
    Monday, January 17, 2011 1:20 AM
  • Hi rusticloud,

    Thank you for your question.
    I am currently looking into this issue and will give you an update as soon as possible.


    Helen Zhou [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.

    Tuesday, January 18, 2011 9:51 AM
  • Rusti,

    How about trying this: remove the non-null constraint from your DataTable by setting the column to AllowDbNull to true.

    I don't do drag-and-drop data stuff ... this is one reason (doesn't always work correctly).


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, January 22, 2011 6:34 PM
  • I tried setting AllowDBNull to true and it doesn't help. If I set to AutoIncrement = false, it does accept a new value but it's a simple int value generated by the dataset like 1,2 or 3, not the random int from the db.
    Monday, January 31, 2011 7:53 PM
  • Helen anything new on this? I am perplexed that the seemingly simple and commonplace newbie questions I ask about in .net forums seem to be so hard to answer.
    Monday, January 31, 2011 7:56 PM
  • Rusti,

    I know how to handle this when I'm doing my own DataAccess using SqlDataAdapter rather than relying on the TableAdapters (which I never use), so my reply may not help you at all. But, without using  a TableAdapter, I rely on my Stored Proc to return a value for the new PK as a parameter. Unfortunately, I don't know how you'd set up that functionality using a TableAdapterManager.UpdateAll().


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, January 31, 2011 8:57 PM