none
Setting Columns to Null RRS feed

  • Question

  • Using VS2005, VB.Net, Dot Net 2.0, SQL Server 2000

    If have some Columns (varchar and numbers) in a DataRow that I want to set to DBNull in the database. The fields as defined as Nullable in SQL DB table.

    I have some fields that are populated when loaded but I want to update to be null using the ADO.Net data objects.

    If I try and use Row("FieldName") = DBNull.Value a 0 gets written to number fields (not sure what gets written to varchar fields.)

    What code should be used to update fields to Null using ADO.Net dataobjects?

    'data object defined at form level
    Load().....
                daForm = New SqlClient.SqlDataAdapter
                dsForm = New DataSet
                dtForm = New DataTable
                row = Nothing
                daForm.SelectCommand = New SqlCommand("SELECT * FROM Site WHERE Site_ID='" & PK & "' AND Instance_ID=" & Instance_ID, con)
                cbForm = New SqlCommandBuilder(daForm) ' used form update and delete
                daForm.Fill(dsForm)
                daForm.FillSchema(dsForm, System.Data.SchemaType.Mapped)
                dtForm = dsForm.Tables(0)
                row = dtForm.Rows(0) ' shortcut reference to dtForm.Rows(0) - only one record is loaded
    
    Monday, December 7, 2009 6:02 PM

Answers

  • I figured out a way (not tested in all cases) to "null out" a field. For example if the users blanks out a form field (not a bound control) and I want to update the DB I use the metadata to find the DefaultValue and make the assignment.


    row!Contact_Mail_ID = dtForm.Columns(

    "Contact_Mail_ID").DefaultValue

    • Marked as answer by Bob Heitzman Thursday, December 10, 2009 5:57 PM
    Thursday, December 10, 2009 5:57 PM

All replies

  • Make sure the actual DataColumn inside of the DataTable is nullable. You should have AllowDBNull property of the DataColumn set to True to accept DBNull values
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, December 8, 2009 11:18 AM
    Moderator
  • Here's the scope on a relevant column:

    ?dtform.Columns("Contact_Mail_ID")
    {System.Data.DataColumn}
        AllowDBNull: True
        AutoIncrement: False
        AutoIncrementSeed: 0
        AutoIncrementStep: 1
        Caption: "Contact_Mail_ID"
        ColumnMapping: Element {1}
        ColumnName: "Contact_Mail_ID"
        Container: Nothing
        DataType: {Name = "Int32" FullName = "System.Int32"}
        DateTimeMode: UnspecifiedLocal {3}
        DefaultValue: {System.DBNull}
        DesignMode: False
        Expression: ""
        ExtendedProperties: Count = 0
        MaxLength: -1
        Namespace: ""
        Ordinal: 22
        Prefix: ""
        ReadOnly: False
        Site: Nothing
        Table: {System.Data.DataTable}
        Unique: False
    
    Wednesday, December 9, 2009 5:06 PM
  • I figured out a way (not tested in all cases) to "null out" a field. For example if the users blanks out a form field (not a bound control) and I want to update the DB I use the metadata to find the DefaultValue and make the assignment.


    row!Contact_Mail_ID = dtForm.Columns(

    "Contact_Mail_ID").DefaultValue

    • Marked as answer by Bob Heitzman Thursday, December 10, 2009 5:57 PM
    Thursday, December 10, 2009 5:57 PM