none
"Column 'ID' does not allow nulls" exception throw from datagridview RRS feed

  • Question

  •  

    Hi ,

    In SQL Server , I create a table named "CodeType" with following main column:

      • ID , identical , default value = newid() , not allow null , primary key
      • Name, varchar(255) , not allow null

    In Visual Studio 2005 , I create a dataset (xsd file) , and drag the Table "CodeType" to it from toolbar "Server Explorer". And set the column ID as readonly = true , allownull = true.

    Then I create a windows form and insert a datagridview control from preceding dataset.

      • Column ID is read only .
      • Allow insert and update .

    My hope is to let SQL Server help to create the value for ID column. When I test it , the error "Column 'ID' does not allow nulls" appear.

     

    Later I update the "Insert" command of the dataset(xsd file) and try again, the error still occur.

     

    Would you please help to address the issue ?

    Thanks,

    Phlics

    • Moved by VMazurModerator Monday, June 6, 2011 10:36 AM (From:ADO.NET Managed Providers)
    Tuesday, January 10, 2006 7:28 AM

All replies

  • Hi All,

    I created a table in SQL server and there is a column named "ID" which type is guid and default value is newid(). This column also been set to primary key of the table.

    I also created a typed dataset in Visual Studio 2005 from preceding table , then in a windows form , used datagridview control to show and add new record to it(set the column of ID to readonly, I hope SQL Server help to set the value of ID column). But when I test it , after inserting a new record , the system always throw exception to say that the ID can not be null.

    Would you please help me to address the issue ?

     

    Thanks,

     

    Phlics

    Tuesday, January 10, 2006 6:25 AM
  • .xsd File do not pull through default values by default (forgive the pun) I'm looking to see if there is some hidden option to propegate the value but your only real options are to

    1. Set the default value in the dataset designer or

    2. Set the offending column value of the new row to the default value (ie GUID.NewGUID in this case)

     

    Wednesday, April 4, 2007 2:23 PM
  • Has anyone found an elegant solution to this problem? The DefaultValue approach doesn't work for me and setting the column value requires several casts
    ((SpecificRowType)(((DataRowView)bindingSource.Current).Row).id
    Saturday, August 25, 2007 6:59 PM
  • Here's a workaround:

     

    Drag your table into the DataSet designer to create a new TableAdapter and DataTable.

     

    Remove the primary key from the DataTable.  Set the ID column's AllowDBNull property to true, and ReadOnly to false.

     

    Create this event handler in your form:

     

    Code Snippet

    private void MyDataTable_RowChanged(object sender, DataRowChangeEventArgs e)

    {

       if (e.Action == DataRowAction.Add)

       {

          e.Row["ID"] = Guid.NewGuid();

       }

    }

     

    Add this event handler to the table's RowChanged event after the Fill command that loads it from the TableAdapter.  (This is very important; if this event handler is active while the Fill event is running, the ID values from the database will be replaced by new GUIDs generated on the front end.)

     

    Now every new row you add in the front end will have a unique identifier.  It won't be the unique identifier assigned by NEWID() in the database, but really the whole purpose of using GUIDs in the first place is so that you don't have to get your ID values from a central store.

     

    A downside of this approach is that the DataTable no longer has a primary key.  This will be an issue if you want to make it the target of foreign-key relationships in the front end (and the designer won't generate a FindByID() method, either).

    Sunday, August 26, 2007 5:48 AM
  • Thank you for the help. The think is that I have several master-details  relations in my project. I'll just stick with my previous solution, i.e.:
    Code Snippet
          private void PatientsEditViewDialog_Closing(object sender, CancelEventArgs e)
            {
                DatabaseDataSet.PatientsRow patient =
                    (DatabaseDataSet.PatientsRow) ((DataRowView) this.patientsBindingSource.Current).Row;
                if (patient.IsNull("Id") || patient.Id.Equals(Guid.Empty))
                {
                    patient.Id = Guid.NewGuid();
                }
                patient.BirthDate = this.birthDateDateTimePicker.Value;
                this.patientsBindingSource.EndEdit();
            }

    Monday, August 27, 2007 7:47 AM
  • A more elegant solution to the default colum property I have found below

     

    In the Dataset.vb generated (NOT! Dataset.Designer.vb) when you click view code from the dataset designer.

    Tweak the following code to suit your own situation

     

    Code Snippet

    Partial Public Class TypedDataSetClass

    Partial Class TypedDataTableName

    Private Sub TypedDataTableName_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs) _

    Handles MyBase.TableNewRow

    CType(e.Row, TypedDataRow)  = GetDefaultValueCustomFunction()

     

    End Sub

    End Class

    End Class

     

     

    For Example

    Code Snippet


    Partial Class DS_Accounts
        Partial Class tb_AccountDataTable

     

    Private Sub tb_AccountDataTable_TableNewRow(ByVal sender As Object, ByVal e As

    System.Data.DataTableNewRowEventArgs) Handles MyBase.TableNewRow
               

    CType(e.Row, tb_AccountRow).AccountID = Guid.NewGuid

    CType(e.Row, tb_AccountRow).InceptionDate = Now
           

    End Sub
       

    End Class

    End Class

     

    Monday, August 27, 2007 10:41 AM
  • John: Your solution seemed marvelous, but then I tried to implement it(in C#):

    Code Snippet

    partial class PatientsDataTable

    {

    protected override void OnTableNewRow(System.Data.DataTableNewRowEventArgs e)

    {

    base.OnTableNewRow(e);

    ((PatientsRow) e.Row).Id = Guid.NewGuid();

    }

    }

     

     

    but it seemed that this code is never called. After some googling I've found( http://www.madprops.org/cs/blogs/mabster/archive/2005/11/09/DataTable.OnTableNewRow.aspx ) that OnTableNewRow is not called unless someone has handled the TableNewRow event.

    Overriding EndEdit solved the problem:

    Code Snippet

    public override void EndInit()

    {

    base.EndInit();

    this.TableNewRow += delegate(object sender, DataTableNewRowEventArgs e) { };

    }

     

    Now everything works like a charm Smile Smile

     

    • Proposed as answer by Glen_Bones Saturday, June 4, 2011 7:05 PM
    Monday, August 27, 2007 4:59 PM
  • Or even better(IMHO)

    Code Snippet

    partial class PatientsDataTable

    {

    public override void EndInit()

    {

    base.EndInit();

    this.TableNewRow += new DataTableNewRowEventHandler ExaminationsDataTable_TableNewRow);

    }

    void ExaminationsDataTable_TableNewRow(object sender, DataTableNewRowEventArgs e)

    {

    ((PatientsRow)e.Row).Id = Guid.NewGuid();

    }

    }

     

     

    And that makes me wonder what OnTableNewRow method is for...
    Monday, August 27, 2007 5:41 PM
  • This solution worked fine with respect to avoiding the GUID is null error event. The new row shows up on the datagridview however when I look at the database itself the table doesn't have a new row in it. Apparently the AddtableRow function isn't called.

    Any ideas why and what to do?
    Friday, August 31, 2007 11:38 PM
  • Are you sure that you called tableAdapter.Update? Without it the data won't be physically written to the datasource.
    Saturday, September 1, 2007 8:45 AM
  • Yes that was the problem. Thanks for your help.
    Wednesday, September 5, 2007 4:39 PM
  • My solution was so simple I do not know if it is applicable to all but check anyway. It is elegant and easy.

    Visual Studio 2008

    1) Edit the DataSet

    2) Select the column/field in the table adapter,

    3) Right click on it and select Properties,

    4) Set the "AutoIncrement" property to true

    5) Save DataSet changes and test

    ..... THAT WORKED FOR ME.



    Saturday, June 4, 2011 7:33 PM
  • Glen, I don't think that works for a GUID. As soon as you set Autoincrement to True, it sets the datatype to Int32. If you set the datatype to Guid, it sets Autoincrement back to False.
    Friday, April 20, 2012 1:35 AM
  • I tested your problem, but in my test it was no problem at all

    Public Class Form1
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim dt As New TestTableDataSet.TableGuidDataTable
            Using da As New TestTableDataSetTableAdapters.TableGuidTableAdapter
                da.Fill(dt)
                Dim dr = dt.NewTableGuidRow
                dr.ID = Guid.NewGuid
                dr.Text = "Test"
                dt.AddTableGuidRow(dr)
            End Using
            DataGridView1.DataSource = dt
        End Sub
    End Class


    Success
    Cor


    • Edited by Cor Ligthert Friday, April 20, 2012 6:40 AM forgot the prim key tested afterwards again
    Friday, April 20, 2012 6:36 AM
  • Hi,

    Reading your original post I noticed that you say you want SQL Server to handle creating the new Guid.  Is that the case as the replies seem to look at handling it in the user interface.

    "My hope is to let SQL Server help to create the value for ID column. "

    Chris

    Monday, June 11, 2012 2:23 PM
  • thank you very much its work with me 


    Tuesday, July 10, 2018 8:05 PM