none
Adding new table row to table [ID cannot be null] RRS feed

  • Question

  • Hi there,

    I am new to database programming and trying some stuff out. So I made a local db application that currently has 1 table. Called companies.

    CREATE TABLE [dbo].[Companies] (
        [Id]          INT           NOT NULL IDENTITY(1,1),
        [Name]        NVARCHAR (25) NULL,
        [PhoneNumber] NVARCHAR (50) NULL,
        [City]        NVARCHAR (50) NULL,
        [ZIP]         NVARCHAR (10) NULL,
        [Street]      NVARCHAR (50) NULL,
        [Number]      NVARCHAR (10) NULL,
        [Description] TEXT          NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );

    I now have a listbox on a windowsform control that shows me all companies in the database. Great! Now I would like to add a company by the click of a button.

        Private Sub btnAddCompany_Click(sender As Object, e As EventArgs) Handles btnAddCompany.Click
            Dim oNewCompany As InfinityDataSet.CompaniesRow = Me.InfinityDataSet.Companies.NewCompaniesRow()
    
            Me.InfinityDataSet.Companies.AddCompaniesRow(oNewCompany)
        End Sub

    But this gives me a error on the ID column. The value cannot be null, how can I set the ID value a auto increment?


    Wednesday, June 20, 2018 11:19 AM

All replies

  • Hi

    Maybe this could help as a pointer.

     
     myTable.Columns.Add("Column1")
          With myTable.Columns("Column1")
            .AutoIncrementSeed = 1
            .AutoIncrementStep = 3
            .AutoIncrement = True
          End With



    Regards Les, Livingston, Scotland



    • Edited by leshay Wednesday, June 20, 2018 11:47 AM
    Wednesday, June 20, 2018 11:42 AM
  • Hi

    Maybe this could help as a pointer.

     
     myTable.Columns.Add("Column1")
          With myTable.Columns("Column1")
            .AutoIncrementSeed = 1
            .AutoIncrementStep = 3
            .AutoIncrement = True
          End With



    Regards Les, Livingston, Scotland



    The table is created using the designer. How can I use the designer to achieve this? I believe this is already set in the column properties as you describe.
    Wednesday, June 20, 2018 12:23 PM
  • Best answer is: Dont use the designer >.< 

    But that is a matter of opinion.


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, June 20, 2018 12:59 PM
  • If you created a typed data set meaning on the form there is a BindingSource and TableAdapter and getting this error, this means the wizard did not see the id column as an identity column as a table with an identity column done with way will return the new key for the newly added record.

    The underlying INSERT would be similar to this and if not you need to re-check the column, ensure it's setup as a primary key (which it does appear to be yet acting like it's not).

    INSERT  INTO [Customer]
            ( CompanyName ,
              ContactName ,
              ContactTitle
            )
    VALUES  ( @CompanyName ,
              @ContactName ,
              @ContactTitle
            );
    SELECT  CAST(SCOPE_IDENTITY() AS INT);

    The above is against the following table definition

    USE [NorthWindDemo]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Customer](
    	[CustomerIdentifier] [INT] IDENTITY(1,1) NOT NULL,
    	[CompanyName] [NVARCHAR](40) NOT NULL,
    	[ContactName] [NVARCHAR](30) NULL,
    	[ContactTitle] [NVARCHAR](30) NULL,
    	[Address] [NVARCHAR](60) NULL,
    	[City] [NVARCHAR](15) NULL,
    	[Region] [NVARCHAR](15) NULL,
    	[PostalCode] [NVARCHAR](10) NULL,
    	[Country] [NVARCHAR](15) NULL,
    	[Phone] [NVARCHAR](24) NULL,
    	[Fax] [NVARCHAR](24) NULL,
    	[Standing] [INT] NULL,
     CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
    (
    	[CustomerIdentifier] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO

    If this was not a typed data set the insert would appear like the following (and with a typed data set it would be similar)

    Public Function AddNewCustomer(ByVal CompanyName As String, ByVal ContactName As String, ByVal ContactTitle As String, ByRef NewIdentifier As Integer) As Boolean
        Using cn As New SqlConnection With {.ConnectionString = My.Settings.ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText = InsertStatement
                cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
                cmd.Parameters.AddWithValue("@ContactName", ContactTitle)
                cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle)
                cn.Open()
                Try
                    NewIdentifier = CInt(cmd.ExecuteScalar)
                    Return True
                Catch ex As Exception
                    Return False
                End Try
            End Using
        End Using
    End Function
    

    Can you a) verify the table definition is showing up as a primary/identity in the designer b) make sure you are targeting the right table (I've seen coders target the wrong database/table in the past).

    Never use auto-incrementing key in the DataTable as in a multi-user environment this can go south fast if another user adds a record and now your auto-inc key is incorrect, off by one or more.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, June 20, 2018 1:02 PM
    Moderator
  • Hi Karen,

    I changed the property using the "dataset designer" to AutoIncrement=True AutoIncrementSeed=1 AutoIncrementStep=1. Now the initial error does not occur anymore

    I added a new code line ( I believe this saves the new company to the database file? )

            Dim oNewCompany As InfinityDataSet.CompaniesRow = Me.InfinityDataSet.Companies.NewCompaniesRow()
            oNewCompany.Name = InputBox("Enter the company name", "Add company")
            Me.InfinityDataSet.Companies.Rows.Add(oNewCompany)
    
            Me.CompaniesTableAdapter.Update(Me.InfinityDataSet.Companies)

    But this again results in a error.  

    System.Data.SqlClient.SqlException: 'Cannot insert explicit value for identity column in table 'Companies' when IDENTITY_INSERT is set to OFF.'

    My code from the database designer for the companies column.

    CREATE TABLE [dbo].[Companies] (
        [Id]          INT           IDENTITY (1, 1) NOT NULL,
        [Name]        NVARCHAR (25) NULL,
        [PhoneNumber] NVARCHAR (50) NULL,
        [City]        NVARCHAR (50) NULL,
        [ZIP]         NVARCHAR (10) NULL,
        [Street]      NVARCHAR (50) NULL,
        [Number]      NVARCHAR (10) NULL,
        [Description] TEXT          NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );


    Wednesday, June 20, 2018 1:28 PM
  • I created the same table using your create table.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim oNewCompany As InfinityDataSet.CompaniesRow =
                Me.InfinityDataSet.Companies.NewCompaniesRow()
    
            Me.InfinityDataSet.Companies.AddCompaniesRow(oNewCompany)
        End Sub
    
        Private Sub CompaniesBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) _
            Handles CompaniesBindingNavigatorSaveItem.Click
    
            Me.Validate()
            Me.CompaniesBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.InfinityDataSet)
    
        End Sub
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Me.CompaniesTableAdapter.Fill(Me.InfinityDataSet.Companies)
        End Sub
    End Class
    

    using the code above, first image is after pressing Button1, populated some data in the record, second image is after pressing save.

    Verified in SSMS (SQL-Server Management Studio)

    So the only difference between mine and yours is localDb for you, SQLEXPRESS instance for me. Try creating the database and table under SQLEXPRESS (if you don't have it, install it, its free). Try against SQLEXPRESS and see if you get the same error or not.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, June 20, 2018 1:40 PM
    Moderator