how to handle NoNullAllowed exception in VS 2005 Express and Microsoft SQL server 2000

how to handle NoNullAllowed exception in VS 2005 Express and Microsoft SQL server 2000

• Niedo

  What is going on here is that the database is probably set to not allow nulls and you are attempting to write a null to the database which is causing the problem.

  You have a couple of options here.

  • Allow nulls in the database (which you say you don't want to do)
  • Enter a default value in the database - this way a null will result in the default value being written to the database.
  • Put some validation on you UI Code to detect that the textbox is blank and not even try to update the database and display a message to ther user that they need to enter a value. Only when you have valid data will you issue an update.

  I would personally validate the data on the UI and put a default value in. This way you are protecting against invalid data being entered and if something does manage to creep into you code that you hadn't anticipate a default value will be enter. This default value may simply be a space character.

  
  

• rcat

  Spotty,

  Thanks for your help.

  I'm still stuck:

  1) Using DataBase Explorer/Open Table definition I entered default Values.

  When you click on the add button of the binding navigator, fields are still displayed as blank. You cannot modify the binding navigator behaviour.

  After several tests I observed that whatever you do, as soon as a nonNullField is displayed you have to put something in it before any additional action on the binding navigator.(This seems obvious, but it is the core of our discussion. Imagine you push the add button but you change your mind and you choose another item to update using a listbox for example: the crash will occur.)

  Things happen as if there are 2 process in parallel: the one that the user program cannot control wich is generated by the user choice and the one that that you control which is totally useless.(example: test if textbox.text ="") because the interrupt being processed happens too late.

  2) Suppose that you use a default value. I have just been saying that it doesn't work, but , worse, next time you push the add button with a blank field you are throwned a duplicate error!

  Therefore I still wonder how to avoid breaking the code.

  I apologize for being so long to explain this problem.

  VB is a wonderful product and I cannot imagine that this kind of situation has not been met and solved so far.

  Best Regards

  Christ

  
  

• Comanche

  OK - with databinding there is a xsd file that is used.

  Correct me if I'm wrong on assumptions etc. of what you've checked.

  using the server explorer you have establish that the fields in the database either do not allow nulls and have a default value or that the allow nulls.

  The xsd file which is the typed dataset, select the smart tag and edit in data designer. You should select the table, look at the properties and also ensure that those each of the fields is marked correctly with allowing null's and/or default value.

  This field is created when you initially use a table with databinding but if you subsequently go and change the database then this will not get refreshed automatically.

  Check that these are matching.

  Duplicate Errors
  No as far as the duplicate errors go, this sounds as though this is because youve got a unique constraint or primary key index established on a field that you will be providing a value to. As you aren't providing a value and its using a default the second calling would generate the duplicate error. So you need to check a number of things - check that the use of the default values wont violate any uniqueness constraints, primary index's etc.

  The fact that it generating a duplicate error on the second call to me indicates its using some default value - whether its a null or your expected default value is what is in doubt. So I'd check that the xsd dataset values for the column match those in the database column.

  If this is something like an ID field which you are setting to a default value and this is whats generating the duplicate then you may want to consider the column type and settings its identity property. That way if its an insert operation then this will generate a next number ID.

  
  

• DMTUC10

  Hi Spotty. Thank you for your cooperation.

  I'll try to answer precisely to your questions.

  OK - with databinding there is a xsd file that is used.

  True

  using the server explorer you have establish that the fields in the database either do not allow nulls and have a default value or that the allow nulls.

  True

  Hereunder a copy of definitions becoming from DataBase Explorer

  FirstName nvarchar(50) Allow Nulls Unchecked

  Default Value or Binding (N'Default_fstnm')
  LastName nvarchar(50) " " Unchecked

  Default Value or Binding (N'Default_ lstnm')
  StreetAddress nvarchar(50) Allow Nulls Checked
  City nvarchar(50) " " Checked
  Phone nvarchar(50) " " Checked

  The xsd file which is the typed dataset, select the smart tag and edit in data designer. You should select the table, look at the properties and also ensure that those each of the fields is marked correctly with allowing null's and/or default value.

  Although it is non user code, I have read carefully the dataset designer.

  I'm not expert enough (... not at all, I'm learning VB) to make an opinion on this lecture.

  Hereupon a copy of the core properties .

  <System.Diagnostics.DebuggerNonUserCodeAttribute()> _

  Public Property FirstName() As String

  Get

  Return CType(Me(Me.tableAddresses.FirstNameColumn),String)

  End Get

  Set

  Me(Me.tableAddresses.FirstNameColumn) = value

  End Set

  End Property

  <System.Diagnostics.DebuggerNonUserCodeAttribute()> _

  Public Property LastName() As String

  Get

  Return CType(Me(Me.tableAddresses.LastNameColumn),String)

  End Get

  Set

  Me(Me.tableAddresses.LastNameColumn) = value

  End Set

  End Property

  <System.Diagnostics.DebuggerNonUserCodeAttribute()> _

  Public Property StreetAddress() As String

  Get

  Try

  Return CType(Me(Me.tableAddresses.StreetAddressColumn),String)

  Catch e As System.InvalidCastException

  Throw New System.Data.StrongTypingException("The value for column 'StreetAddress' in table 'Addresses' is DBNull.", e)

  End Try

  End Get

  Set

  Me(Me.tableAddresses.StreetAddressColumn) = value

  End Set

  End Property

  <System.Diagnostics.DebuggerNonUserCodeAttribute()> _

  Public Property City() As String

  Get

  Try

  Return CType(Me(Me.tableAddresses.CityColumn),String)

  Catch e As System.InvalidCastException

  Throw New System.Data.StrongTypingException("The value for column 'City' in table 'Addresses' is DBNull.", e)

  End Try

  End Get

  Set

  Me(Me.tableAddresses.CityColumn) = value

  End Set

  End Property

  <System.Diagnostics.DebuggerNonUserCodeAttribute()> _

  Public Property Phone() As String

  Get

  Try

  Return CType(Me(Me.tableAddresses.PhoneColumn),String)

  Catch e As System.InvalidCastException

  Throw New System.Data.StrongTypingException("The value for column 'Phone' in table 'Addresses' is DBNull.", e)

  End Try

  End Get

  Set

  Me(Me.tableAddresses.PhoneColumn) = value

  End Set

  End Property

  That sounds good to me!

  This field is created when you initially use a table with databinding but if you subsequently go and change the database then this will not get refreshed automatically.

  Frankly, I no longer understand . Which field are you talking about Anyway, as I didn't change my database everything should be OK! How can I see a field marked correctly with allowing null etc.

  Check that these are matching. Same as Above.

  I think that at this point and before going further it is necessary to make a break.

  Can you please tell a little bit more on how to answer to your questions

  There is a very simple way to simplify . The problem can be quickly reproduced.

  What I did is: From VB 2005 Express Start Page/How Do I / Data Access/ .. and the five steps from "Storing & Accessing Data" to "Adding or Modifying your records".

  It will take 10 minutes to go though these steps , create the FirstDataBase Application and then reproduce easily the problem.

  Once again Thank you for your help and I hope that my last suggestion will help you see what is going on.

  As regard to the duplication subject I ' ll work on it asap.

  Christ

  
  
• how to handle NoNullAllowed exception in VS 2005 Express and Microsoft SQL server 2000