locked
Inserting a NULL value through a TableAdapter RRS feed

  • Question

  • User-1809947162 posted

    I have tableadapters for almost all data access, so of course records are inserted into the database with statements like: -
        Dim taColumns As New GroupTableAdapters.GroupTableColumnsTableAdapter
        .....
        taColumns.Insert(value, value, value, ......     )

    One of the values has been a Guid field, defined in the database as Uniqueidentifier with the Allow Nulls option checked.  If the taColumns.Insert(....) statement has "Nothing" in the corresponding position then the row is correctly created with a NULL column.   However sometimes this column is not null, so I need to be able to write logic like this: -

        Dim Guidvalue as ????
        ....
        If condition Then
             Guidvalue = Nothing
        Else
             Guidvalue = a real guid value
        End If
        ....
        taColumns.Insert(........, Guidvalue, ....)

    I can't find a way of doing this.  Whether I define Guidvalue As Nullable(of GUID) or simply as Guid, and whether I assign Nothing or Guid.Empty,  I end up with a non-NULL value of an empty guid (all zeros) on the database.  When defined As Nullable(of Guid) and nothing has been assigned, then an exception is raised.

     Of course if I were updating the table I'd have a TableRow and be able to use the tablerow.SetxxxxxNull method, but you can't use this when you are creating a new record.

    Thank you,

    Robert Barnes.

    Friday, May 8, 2009 5:34 AM

Answers

  • User-1809947162 posted

    Sort of. I found that the following approach works, even it isn't terribly elegant.  

    For this discussion, imagine that

    1.    I have defined a table, called "MyTable", that has fields A, B, C, D, E, and F.   A is a key field (let's assume it's a GUID) and must always be present, the remaining fields have a variety of formats, and any or all of them can be NULL.    When Null, I want a real NULL on the database, not a blank string, zero numeric value, empty guid, etc. 

    2.    I have defined taMyTable, tbMyTable, and trMyTable as tableadaptor, table, and tablerow, based on this definition.   Within tbMyTable I have defined a method GetDataById(A) that returns the record with key value A.

    As far as I can tell, the only way to create a NULL value with an Insert statement is to leave the corresponding element out.

    It would be easy if, instead of writing taMytable.insert (A, B, C, D, E, F) you could write something like this: -
        trMyTable.A = guid.newguid
        If BValue is wanted THEN            <=   "BValue is wanted" is not real code,
             trMyTable.B = BValue
        Else
             trMyTable.SetBNull
        End If
        etc
        taMyTable.Insert(trMytable)

    Unfortunately this won't work as you can't refer to trMyTable here because it doesn't exist, and the first reference to it, trMyTable.A = guid.newguid, will throw an error.   There may be a way of saying "Give me a dummy record in memory", but if there is I haven't found it.

    HOWEVER, you can do this.   The process is to create an initial record with minimal data, then read it back and update it: -

         Dim NewKey as guid = guid.newguid
         taMyTable.Insert (NewKey,,,,,,)
         trMyTable = tbMytable.GetDataById(Newkey)

        If BValue is wanted THEN           
             trMyTable.B = BValue
        End If
        '   etc for C, D, E, F
        taMyTable.Update(TrMyTable)

    Given that you are immediately reading back the record you've just created, I hope that the system is smart enough to optimize the database read, but in any event it seems very fast and it is certainly easy.

    I hope this helps,

    Regards, Robert

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 25, 2010 6:54 PM

All replies

  • User-1757793930 posted

    Not sure if this really helps but i will throw it out there. What about this? This does two different inserts based on condition.

     

      Dim Guidvalue as ????
        ....
        If condition Then
             Guidvalue = Nothing
        Else
             Guidvalue = a real guid value
        End If
        ....
        taColumns.Insert(........, Guidvalue, ....)
    
    Dim myGuid As Guid = Guid.NewGuid.ToString()
    ' Some worker stuff
    '
    ' end
    
    If condition Then
        taColumns.Insert(a,b,c,d,e)
    Else
        ta.Coulmns.Insert(a,b,c,d,e,myGuid)
    End If
     
    Friday, May 8, 2009 6:27 AM
  • User-1809947162 posted

     I had already thought of that solution, and in this case it is what I will do if there's no better solution.  It will work here because there is only one nullable column that I'm concerned about, so it is feasible to have such logic.  In a more general case however, where there are N nullable fields you have 2^N possibilities, so this quickly becomes unmanagable.  For example, imagine that only a and b in the case above are not nullable, but all the other fields are.  You'd need logic like this: -

    If c = Nothing then
         If d = Nothing then
                If e = Nothing then
                      If MyGuid = Nothing then
                          tacolumns.insert (a, b, nothing, nothing, nothing, nothing)
                      Else
                          tacolumns.insert(a, B, nothing, nothing, nothing, MyGuid)
                      End If
                Else
                      If MyGuid = Nothing then
                          tacolumns.insert (a, b, nothing, nothing, e, nothing)
                      Else
                          tacolumns.insert(a, b, nothing, nothing, e, MyGuid)
                      End If
                 End If
    etc etc.

    Besides, it introduces pathological coupling between logic in different parts of your program that is just shrieking "Potential bugs lurk here".   One would hope Microsoft have thought of a more elegant solution to this problem.

    In another situation where I had this kind of combinatorial complexity, I solved the problem by adding an "Insert2" method to the table adaptor that only required the not-null fields.  Thus my logic was the equivalent of: -
              tacolumns.insert2(a, b)
              tbcolumns = tacolumns.GedbyAB(a, b)
              trcolumns = tbcolumns(0)
              If c <> nothing then
                  Trcolumns.c = c
              End if
              If d <> nothing then
                  Trcolumns.d = d
              End If
              If e <> nothing then
                   trcolumns.e = e
              End If
              tacolumns.update(trcolumns)

    But it annoys me having to read back the row when I should have been able to insert it correctly in the first place.

    Cheers, Robert

    Friday, May 8, 2009 5:37 PM
  • User854688209 posted

     Try this;

      Dim Guidvalue as String
        ....
        If condition Then
             Guidvalue = DBNull.Value
        Else
             Guidvalue = a real guid value
        End If
        ....
        taColumns.Insert(........, Guidvalue, ....)

    Same post in this link also, http://forums.asp.net/t/1420668.aspx

    Friday, May 8, 2009 11:49 PM
  • User-1809947162 posted

     I experimented by changing my program to the following, which should always create a row with a NULL value: -

            Dim GUIDValue As String = DBNull.Value
            taColumns.Insert(Arguments, GUIDValue, More arguments)

    That doesn't build, although curiously there are no error messages or green wavy lines in the source other than "There were build errors ....  ". 

    It built when I changed this to 
            Dim GUIDValue As String = DBNull.Value.tostring
            taColumns.Insert(Arguments, Nothing, More arguments)

    However it doesn't build if you write
            Dim GUIDValue As String = DBNull.Value
            taColumns.Insert(Arguments, GUIDValue, More arguments)

    Both these build errors are to be expected because you are not allowed to assign a Guid to a string (although I can't think why not), and you can't give a string argument where an object (the tableadaptor in this case) is expecting a Guid.   The lack of specific error messages is puzzling.

    I tried
            Dim GUIDValue As Guid = DBNull.Value
    but this didn't build even when the taColumns.Insert(...) statement was commented out.   Again, no specific error messages.

    Cheers, Robert.

    Saturday, May 9, 2009 12:22 AM
  • User-1809947162 posted

    <meta name="Generator" content="Microsoft Word 9"><meta name="Originator" content="Microsoft Word 9"><link href="file:///C:/DOCUME%7E1/ADMINI%7E1/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml" rel="File-List"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:"Arial Unicode MS"; panose-1:2 11 6 4 2 2 2 2 2 4; mso-font-charset:128; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1 -369098753 63 0 4129279 0;} @font-face {font-family:"\@Arial Unicode MS"; panose-1:2 11 6 4 2 2 2 2 2 4; mso-font-charset:128; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1 -369098753 63 0 4129279 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} pre {margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Arial Unicode MS";} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style>

    What was the point of giving me these references?   None answer the question, directly or indirectly.<o:p></o:p>

    <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>

    I asked how to define GuidValue and give it a value, so that code more or less like this would create a NULL value. <o:p></o:p>

        Dim Guidvalue as ????
        ....
        If condition Then
             Guidvalue = Nothing
        Else
             Guidvalue = a real guid value
        End If
        ....
        taColumns.Insert(........, Guidvalue, ....)

    <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>

    The first reference simply shows how to pass Null as a SQL parameter.  It gives C# code of<o:p></o:p>

    SqlParameter ParamValue= new SqlParameter("@Address", SqlDbType.NVarChar, 20);
    ParamValue.Value = DBNull.Value;<o:p></o:p>

    So what?  I’m not using a SQLParameter.  If I write: -
               
    taColumns.Insert(arguments, dbnull.value, more argument)<o:p></o:p>

    then all I get is an error message, “dbnull.value can not be converted to a guid”.   <o:p></o:p>

    <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>

    I can’t assign dbnull.value to Guidvalue if Guidvalue is defined as Guid, or as string. While the following code will build: -<o:p></o:p>

    Dim S as object<o:p></o:p>

    S = dbnull.value<o:p></o:p>

    Tacolumns.insert(argument, S, more arguments)<o:p></o:p>

    this fails at run time, with message “Specified Cast is not valid”.<o:p></o:p>

    <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>

    The second reference showed me (or might have showed me if the c# example and VB.Net example had actually be present) the best way of creating SQL parameters.  Also irrelevant.<o:p></o:p>

    <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>

    The third reference is about “Working with DataAdaptor Events”.  But surely writing my Insert code within Try/end try is not a best-practice way of handing this very simply problem, writing a Null value?  Besides, I didn’t see how any of the events described would allow me to solve my problem.<o:p></o:p>

     

    Regards, Robert.

    Monday, May 11, 2009 2:46 AM
  • User1716267170 posted

    Hi Robert Barnes,

    Please don't post a duplicated question so that we can help you better. It's the same with this one.

    Thanks.

    Monday, May 11, 2009 2:54 AM
  • User-1809947162 posted

    David, thanks for posting a reply in the duplicate thread, http://forums.asp.net/p/1420668/3150171.aspx#3150171.   This duplication was an accident, I've closed that thread and are replying here.

    You said: -

    ****

    Hi Robert Barnes,

    Did you set the AllowDBNull as True and NullValue as NULL for the GUID field in typed dataset designer? If not, please set them like that and try again. Also, please notice that if you've set GUID field as nullable, the VS intelligence should have a question markup(?).

    Thanks.
    David Qian
    Microsoft Online Community Support

    ****

    AllowDBNull is set to True, NullValue is set to (Throw Exception).   NullValue gives options of (Throw Exception), (empty), and (nothing), but if you attempt to choose any value other than (throw exception) you get an error message, "property value is not valid". 

    I have only ever been able to set this property for String fields, but even with setting it to either (empty) or (nothing)  I get incorrect behaviour.  If I assign Nothing to the string variable I end up with a blank value in the database not NULL.   

    Of course the behaviour that I would like is that: -

    1.    This property is available for any field that allows NULL values
    2     Its default value is (empty) or (nothing)   (By the way, what is the difference?)
    3     With the default value, you can assign
                       field = nothing
            and have the value set to NULL in the database. whatever the data type.  Not Guid.empty, nor 0 nor blank

    Unfortunately this is NOT the behaviour that has been programmed. 

    So I'm afraid that your suggestion doesn't help David.  AllowDBNulls is already set to True, and I can't change NullValue.

    Regards, Robert.

     

    Saturday, May 16, 2009 7:11 PM
  • User1716267170 posted

    Hi Robert Barnes,

    Sorry for the delay since it was a weekend. I tested your problem, and you could try this code:

    Dim gu As New Guid("21932442-3422-1214-4232-123154432432") 
    
    Dim a As System.Nullable(Of Guid) 
    If condition1 = condition2 Then 
        a = Nothing 
    Else 
        a = gu 
    End If 
    
    Dim guid As Code.dsTableAdapters.GUIDtestTableAdapter = New Job.Code.dsTableAdapters.GUIDtestTableAdapter() 
    guid.Insert(a, "Hallo11") 
    

    Thanks.

    Monday, May 18, 2009 1:38 AM
  • User9541437 posted

    Hi Robert,

    Were you able to get this working?  I'm having the same problem and can't get this working in C#.

     

    I'm trying:

    TableRow.SetGuidColumnToNull(); //function provided by having the adapter AllowNull set to true

    Table.Rows.Add(TableRow);

    TableAdapter.FillTable(Table);

     

    I'm getting the same exception you mentioned.

    Other Notes:

    This column on the DB is set to allow nulls

    The default value on the column if opened in SQL is set to NULL

    The column on the TableAdapter is set to Allow Null

    the default value on the column in the TableAdapter is <DBNull> (because it won't let me set it to anything else as you mentioned above)

    The Null Value value on the column in the TableAdapter is (Throw Exception) (which is why I'm getting an exception but I can't have it as anything else besides manually entering an Empty guid (00000000-0000-0000-000000000000).

     

     

    Thursday, November 25, 2010 6:09 PM
  • User-1809947162 posted

    Sort of. I found that the following approach works, even it isn't terribly elegant.  

    For this discussion, imagine that

    1.    I have defined a table, called "MyTable", that has fields A, B, C, D, E, and F.   A is a key field (let's assume it's a GUID) and must always be present, the remaining fields have a variety of formats, and any or all of them can be NULL.    When Null, I want a real NULL on the database, not a blank string, zero numeric value, empty guid, etc. 

    2.    I have defined taMyTable, tbMyTable, and trMyTable as tableadaptor, table, and tablerow, based on this definition.   Within tbMyTable I have defined a method GetDataById(A) that returns the record with key value A.

    As far as I can tell, the only way to create a NULL value with an Insert statement is to leave the corresponding element out.

    It would be easy if, instead of writing taMytable.insert (A, B, C, D, E, F) you could write something like this: -
        trMyTable.A = guid.newguid
        If BValue is wanted THEN            <=   "BValue is wanted" is not real code,
             trMyTable.B = BValue
        Else
             trMyTable.SetBNull
        End If
        etc
        taMyTable.Insert(trMytable)

    Unfortunately this won't work as you can't refer to trMyTable here because it doesn't exist, and the first reference to it, trMyTable.A = guid.newguid, will throw an error.   There may be a way of saying "Give me a dummy record in memory", but if there is I haven't found it.

    HOWEVER, you can do this.   The process is to create an initial record with minimal data, then read it back and update it: -

         Dim NewKey as guid = guid.newguid
         taMyTable.Insert (NewKey,,,,,,)
         trMyTable = tbMytable.GetDataById(Newkey)

        If BValue is wanted THEN           
             trMyTable.B = BValue
        End If
        '   etc for C, D, E, F
        taMyTable.Update(TrMyTable)

    Given that you are immediately reading back the record you've just created, I hope that the system is smart enough to optimize the database read, but in any event it seems very fast and it is certainly easy.

    I hope this helps,

    Regards, Robert

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 25, 2010 6:54 PM
  • User-1809947162 posted

    Oops sorry, I've edited the code above.  In the last block of code, the statement

         trMyTable = tbMytable.GetDataById(Newkey)

    was originally written incorrectly as

         tbMytable.GetDataById(Newkey)


    Thursday, November 25, 2010 6:59 PM