none
Auto Increment Not Working - SQL Identity field RRS feed

  • Question

  • I am new to Visual Basic & SQL server but have some experience in Access & VBA. But this is a steep learning curve!

    I am banging my head against a wall with a problem..

    All of my tables in this database are in the dataset. The main one being tblItems with a PK 'ITEMID'

    I have 2 forms - the first one is used to lookup an item the second displays the item's full details.

    On the first form (lookup) I have a 'Add New' button which launches the second form with the code - frmProductDetail.VItemsBindingSource.AddNew()

    This opens the form with empty boxes as expected. I have a 'Save' button on the second form with the following code -

            Dim row As SASHItemsDataSet.tblItemsRow
            row = SASHItemsDataSet.tblItems.NewRow
            With row
                .ITEMCODE = txtItemCode.Text
                .ITEMDESCRIPTION = txtItemDescription.Text
                .CATEGORY = cmbItemCategory.SelectedValue
                .PURCHCOST = txtPurchCost.Text
                .SELLCOST = txtSellPrice.Text
                .UNIT = cmbUOM.SelectedValue
                .VATID = cmbVAT.SelectedValue
                .WHLOCATION = cmbWHLoc.SelectedValue
            End With

            SASHItemsDataSet.tblItems.Rows.Add()

            Try
                Me.Validate()
                Me.VItemsBindingSource.EndEdit()

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "UPDATE FAILED")
            End Try

    My problem is I get the error msg box with the following error 'Column 'ITEMID' does not allow nulls'

    This field is set as a auto incrementing identity field with all the correct settings shown in Visual Studio so it shouldn't be coming back as null.

    I have Googled for hours & tried all sorts with no luck..

    I have clearly gone wrong somewhere but I can't work out where... any help appreciated!

    James


    Friday, April 24, 2015 3:07 PM

Answers

  • Looks like I have made progress. Since changing the forms data source to the table in the dataset rather than the view the ITEMID field shows -1 when the form opens to add a new row. Now I have another problem... there are combo boxes for fields such as the item category, VAT code, warehouse location. When I select an item from the combo box it get 'stuck' on that field and I can't move on to any other field.

    James
    Thursday, April 30, 2015 11:56 AM

All replies

  • An identity column should not be a part of the INSERT query. It is auto generated by SQL Server and does not need to be specified.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, April 24, 2015 8:30 PM
  • That was one of the things I found when Googling & I have checked the INSERT query in the dataset which doesn't include ITEMID.

    James

    Friday, April 24, 2015 10:12 PM
  • Then you should be good as far as the INSERT statement is concerned. If the exception still occurs then it would appear that ITEMID is not functioning as an Identity column (which is a database table configuration issue). You many want to use SSMS to drop the current ITEMID column and then create a new one as an Identity column.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, April 25, 2015 1:24 AM
  • Thanks Paul, 

    That's one thing I have yet to try! Will give it a go and see what happens.

    James

    Sunday, April 26, 2015 9:13 AM
  • This is the dataset in the VS project. I think everything looks OK!

    Sunday, April 26, 2015 6:15 PM
  • The physical SQL database might be out-of-sync with the model.  Verify the table in SQL Server actually has the IDENTITY property.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, April 26, 2015 7:25 PM
  • Hello James,

    Could you please share the table script you are working with and please also share some more code with us? In your original post, the code seems to be not able to identify what causes this exception.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 27, 2015 5:48 AM
    Moderator
  • This is the code on frmProductLookup that opens the form...

            frmProductDetail.Show()
            frmProductDetail.VItemsBindingSource.AddNew()

    This is the code on frmProductDetail_Load...

            'TODO: This line of code loads data into the 'SASHItemsDataSet.tblVAT' table. You can move, or remove it, as needed.
            Me.TblVATTableAdapter.Fill(Me.SASHItemsDataSet.tblVAT)
            'TODO: This line of code loads data into the 'SASHItemsDataSet.tblWarehouseLocations' table. You can move, or remove it, as needed.
            Me.TblWarehouseLocationsTableAdapter.Fill(Me.SASHItemsDataSet.tblWarehouseLocations)
            'TODO: This line of code loads data into the 'SASHItemsDataSet.tblStockUnits' table. You can move, or remove it, as needed.
            Me.TblStockUnitsTableAdapter.Fill(Me.SASHItemsDataSet.tblStockUnits)
            'TODO: This line of code loads data into the 'SASHItemsDataSet.tblItemCategory' table. You can move, or remove it, as needed.
            Me.TblItemCategoryTableAdapter.Fill(Me.SASHItemsDataSet.tblItemCategory)
            'TODO: This line of code loads data into the 'SASHItemsDataSet.vItems' table. You can move, or remove it, as needed.
            'Me.VItemsTableAdapter.Fill(Me.SASHItemsDataSet.vItems)
            Me.VItemsTableAdapter.Fill(Me.SASHItemsDataSet.vItems)
            Me.VItemsBindingSource.Position = Me.VItemsBindingSource.Find("ITEMID", _passedText)
            Me.txtWKSName.Text = Environment.MachineName
            Me.txtSellPrice.Text = FormatCurrency(Me.txtSellPrice.Text)
            Me.txtPurchCost.Text = FormatCurrency(Me.txtPurchCost.Text)
            Me.txtPriceIVAT.Text = FormatCurrency(Me.txtSellPrice.Text + (Me.txtSellPrice.Text * 0.2))

    On the tblItemTableAdapter this is the Command Text for the Insert Command...

    INSERT INTO tblItems
                             (ITEMCODE, ITEMDESCRIPTION, UNIT, WHLOCATION, VATID, PREFSUPPLIER, CATEGORY, PURCHCOST, SELLCOST, INACTIVE)
    VALUES        (@ITEMCODE,@ITEMDESCRIPTION,@UNIT,@WHLOCATION,@VATID,@PREFSUPPLIER,@CATEGORY,@PURCHCOST,@SELLCOST,@INACTIVE)

    SQL Server Management Studio clearly shows the column as an Identity column. If I add a row through SSMS it does create an PK identity automatically. I have also dropped the ITEMID column & recreated through SSMS which has no effect.

    I am now considering creating a separate form just for adding an item but I have managed it in Access but am just struggling with VB!

    Thanks,

    James


    Thursday, April 30, 2015 9:01 AM
  • Looks like I have made progress. Since changing the forms data source to the table in the dataset rather than the view the ITEMID field shows -1 when the form opens to add a new row. Now I have another problem... there are combo boxes for fields such as the item category, VAT code, warehouse location. When I select an item from the combo box it get 'stuck' on that field and I can't move on to any other field.

    James
    Thursday, April 30, 2015 11:56 AM
  • Hello James,

    >> Since changing the forms data source to the table in the dataset rather than the view the ITEMID field shows -1 when the form opens to add a new row.

    It is glad to see that you get a workaround for your original issue.

    >> Now I have another problem... there are combo boxes for fields such as the item category

    For a new issue, please open a new thread and provide a detail clarification about it. From your provided description, it seems to be related with the data control binding, then the windows form or wpf forum is properly used to discuss about the control binding.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 1, 2015 5:47 AM
    Moderator