none
BindingNavigatorAddNewItem shows -1 in the text box then tries to add Null to the DB RRS feed

  • Question

  • Hi,

    I'm using VS2008 for a VB Windows Forms app that uses a SQL Express 2008 R2 DB.

    I have created my table and added it by dragging onto the form (Details view). When I click "+" on the bindingnavigator, a "-1" appears in one of the text boxes (ita a Numeric field). When I change this value and click save, I get an error that I can't add a Null value to the table. Why doesn't the text box value go to the db?

    How can I intercept this Insert action somewhere in the BindingNavigator code to debug it?

    Thanks in advance,

    Steve

    Friday, July 23, 2010 3:15 PM

Answers

  • Hi Steve,

    When you click the add button(“+”  button) on the BindingNavigator, a "-1" appears in one of text boxes,  this always means that the column corresponding to this text box is an Identity Column in your database, and this column’s values are auto-incremented. When you insert a new row, the database will generate the identity key for this new row. So you do not need (had better not) to assign a new value for that NCRID, just leave it to “-1” (“-2”, “-3”,…) and let database decide what value to use. You just need to provide values for other columns which do not allow null values, then click save button. After saving, the newly created identity value for that new row will also been retrieved and showed in the textbox.

     

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, July 26, 2010 8:50 AM
    Moderator
  • If a primary key is not an automatic Identity field, you have to set the primary key of your datatables even before you add a datarow to the datatable.

    The nicest way is using the primary key object, however I always simply set it, but that is easy because I use GUID's as primary keys.

    In your case it is probably a little bit more difficult to create unique keys.

    But you have to create a procedure for that.

    If what I write is the case, can you then rephrase your question in this forum.

    Probably there are more who have an answer for you how to solve that in VB.

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/threads?page=1


    Success
    Cor
    Tuesday, July 27, 2010 9:27 AM
  • Hi,

    I've fixed this now. The problem was caused by Foreign key relationships which I defined BEFORE I changed the NCRID field from Identity to Integer. When I deleted these and re-created them, the NCRID field began to behave how I expected it to.

    Thanks to all for your help and encouragement.

    Regards

    Steve

    Tuesday, July 27, 2010 3:43 PM

All replies

  • Hi,

     

    I've tried doing the same in code, and I get the same error, the value of NCRID can't be Null. But I'm setting it to ba an integer. Here's my code:

        Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click

            Dim taNCR As New NCRDataSet1TableAdapters.NCRsTableAdapter
            Dim newNCRRow As NCRDataSet1.NCRsRow

            newNCRRow = NCRDataSet1.NCRs.NewRow

            newNCRRow.CustomerName = ComboBox1.Text
            newNCRRow.CustomerAccount = ComboBox1.SelectedValue
            newNCRRow.OriginIDFk = ComboBox2.SelectedValue
            newNCRRow.AreaAffectedIDFk = ComboBox3.SelectedValue
            newNCRRow.InvestigatedByIDFk = ComboBox4.SelectedValue
            newNCRRow.NCRID = CInt(lblNCRNo.Text)
            newNCRRow.RaisedBy = strUserName
            newNCRRow.DateCreated = Now
            newNCRRow.IncidentDate = dtIncident.Text

            NCRDataSet1.NCRs.Rows.Add(newNCRRow)
            taNCR.Update(NCRDataSet1.NCRs)

        End Sub

    Here's the error:

    Cannot insert the value NULL into column 'NCRID', table 'NCR.dbo.NCRs'; column does not allow nulls. INSERT fails. The statement has been terminated.

    There is Definitely an Integer value in the datatable, it falls over on the update command.

    Any help/comments appreciated.

    Thanks,
    Steve

    Friday, July 23, 2010 4:46 PM
  • Hi,

    I will move it to Ado.net Dataset forum, where you can get better reply.

     

    Best regards,
    Guang-Ming Bian - MSFT


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Monday, July 26, 2010 3:08 AM
  • Hi Steve,

    When you click the add button(“+”  button) on the BindingNavigator, a "-1" appears in one of text boxes,  this always means that the column corresponding to this text box is an Identity Column in your database, and this column’s values are auto-incremented. When you insert a new row, the database will generate the identity key for this new row. So you do not need (had better not) to assign a new value for that NCRID, just leave it to “-1” (“-2”, “-3”,…) and let database decide what value to use. You just need to provide values for other columns which do not allow null values, then click save button. After saving, the newly created identity value for that new row will also been retrieved and showed in the textbox.

     

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, July 26, 2010 8:50 AM
    Moderator
  • Hi Alex,

    Before I had this problem, I changed the properties of that field from Identity to a straight integer. I then built a new form from scratch by dragging the dataset onto the form. That where the problem started. Even if I don't type anything in the text box, I still get the error message about the null value. I then built another form and this time tried to send a value using code, but that didn't work either.

    So it looks like a database problem. I hope I don't have to rebuild the database. Is there some property I can change on the column that I changed from Identity? This column is the Primary Key column, and there are relationships set up with other tables.

    Thanks for your input,

    Steve

    Tuesday, July 27, 2010 8:18 AM
  • If a primary key is not an automatic Identity field, you have to set the primary key of your datatables even before you add a datarow to the datatable.

    The nicest way is using the primary key object, however I always simply set it, but that is easy because I use GUID's as primary keys.

    In your case it is probably a little bit more difficult to create unique keys.

    But you have to create a procedure for that.

    If what I write is the case, can you then rephrase your question in this forum.

    Probably there are more who have an answer for you how to solve that in VB.

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/threads?page=1


    Success
    Cor
    Tuesday, July 27, 2010 9:27 AM
  • Hi Steve,

     

    There are basically two ways for you to avoid the Exception you got.

     

    First, in the Typed DataSet designer, the properties panel of the column NCRID, there is a property named “ReadOnly” which is setted to True for the Identity column, you can try to change it to False to avoid the Exception you got.


    Second, there is another property named “DefaultValue”, you can assign a default value to it which can also
    avoid the exception you got.  

     

    However, please note that even you could set new value to NCRID and avoid the  exception, as long as the NCRID column in database is an identity column, the value you assigned will be ignored when you save the changes to the database. DataBase will generate the identity key value for NCRID of the new row. And the newly created identity key will been retrieved and showed in the textbox after saving.

     

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, July 27, 2010 10:25 AM
    Moderator
  • Alex,

    Because I did not think about that it was an identity column and I missed that one, (I thought good catch from Alex), I marked direct your reply as answer.

    But now the OP replies this.

     I changed the properties of that field from Identity to a straight integer


    Success
    Cor
    Tuesday, July 27, 2010 2:04 PM
  • Hi,

    I've fixed this now. The problem was caused by Foreign key relationships which I defined BEFORE I changed the NCRID field from Identity to Integer. When I deleted these and re-created them, the NCRID field began to behave how I expected it to.

    Thanks to all for your help and encouragement.

    Regards

    Steve

    Tuesday, July 27, 2010 3:43 PM