locked
Strongly-Typed DataSet/Nullable column issue RRS feed

  • Question

  • User-554376942 posted

    I have a strongly-typed DataSet "Surplus" that has a table "OutgoingTransfer" and its TableAdapter "OutgoingTransferTableAdapter". This table has a column "ApprovingUser" which is, on the SQL Server, a nullable "int" column. I have the column configured in the DataSet so that AllowDBNull is set to True. However, when I instantiate a DataTable of type "OutgoingTransferDataTable" and use its "AddOutgoingTransferRow" method, the "ApprovingUser" parameter is of type "int", not "int?". Because of this, "null" is an invalid parameter for the method. I have tried everything I know to make this work; I must be able to add a row to this DataTable with "ApprovingUser" as "null". Can someone point me in the right direction as to why this is happening?

    Sunday, March 2, 2008 7:09 PM

All replies

  • User1192284279 posted

    Hi, 

     This is a common problem I've come up with while working with typed datasets.  Here is a fix.  You need to check the parameter in your insert/update method and make sure that AllowDbNull is set to true. (it probably isn't)

    •  You get there by opening the dataset designer
    • Click on the Table Adapter where it says "OutgoingTransferTableAdapter" to select the TableAdapter
    • Open the appropriate Command node in the Properties window
    • Open the Parameters Dialog by clicking on Parameters section
    • Change the AllowDbNull property on the ApprovingUser parameter.

    hope that helps

    -jogi

    Monday, March 3, 2008 4:18 PM
  • User-554376942 posted

    No, the column's AllowDbNull property is and has been set correctly in all places.

    Tuesday, March 4, 2008 2:21 PM
  • User1192284279 posted

    I was referring to the Parameter. Not the column. -jo

    Tuesday, March 4, 2008 5:29 PM
  • User-554376942 posted

    I was referring to both [:P] 

    Tuesday, March 4, 2008 5:52 PM
  • User1716267170 posted

    Hi Adam Maras,

    Your issue is so strange. Everything runs well on my machine.

    So please check the whether you've set the nullable type in database. If there's still any errors, please feel free to let me know. Thanks.

     

    Hope this helps you!

    Wednesday, March 5, 2008 3:46 AM
  • User-1857125468 posted

    I have a strongly-typed DataSet "Surplus" that has a table "OutgoingTransfer" and its TableAdapter "OutgoingTransferTableAdapter". This table has a column "ApprovingUser" which is, on the SQL Server, a nullable "int" column. I have the column configured in the DataSet so that AllowDBNull is set to True. However, when I instantiate a DataTable of type "OutgoingTransferDataTable" and use its "AddOutgoingTransferRow" method, the "ApprovingUser" parameter is of type "int", not "int?". Because of this, "null" is an invalid parameter for the method. I have tried everything I know to make this work; I must be able to add a row to this DataTable with "ApprovingUser" as "null". Can someone point me in the right direction as to why this is happening?

    I have the exact same problem. Even though I have set AllowDBNull to true the auto generated AddRow function expects a double instead of a double? meaning I can't supply null as the parameter. Fortunately I have found a work around: DataSet.LocationRow row = dataSet.LocationTable.AddLocationRow("Home",0,0); row["Latitude"] = DBNull.value; row["Longitude"] = DBNull.value; I am using SQLite though so maybe its DataSet generation is to blame? http://sqlite.phxsoftware.com
    Friday, March 28, 2008 6:26 AM
  • User659618793 posted

    Unfortunatelly this is a problem with Dataset generation since VS 2005. When VS create a typed Dataset with a nullable column, it should use a "Nullable(of)" type, but it doesn't.

    The dataset collumn property "NullValue" has a default value of "(Throw exception)" even when the "AllowDBNull" is true. We can only change the value to "(Nothing)" when "DataType" property is "System.String".

    So, when I need to check if the column has a value, I need to test "datarow("FieldName") Is DBNull.Value", because if I use the column typed property, it raises an exception. The same problem happens with typed "AddRow" methods (your case).

    I think this was a BIG mistake from Microsoft, because there is no sense in throwing an exception when a NULLABLE collumn value is DBNull.

    I was expecting they had changed and fixed this issue in VS 2008, but they didn't.   =(

    Thursday, June 19, 2008 1:44 PM
  • User1857242553 posted

    I am having this same problem (my post) except some parameters are showing up as nullable, even when it seems like they shouldnt. I'm not understanding what VS uses to make the decision when auto generating the methods for your query whether to make the parameters nullable or not. To me it would seem like they should all be nullable by default and then you can go into the parameters collection and change the AllowDBNull property for each parameter yourself. Since your parameters aren't always mapping directly to a field value, they may just be used purely for the logic of the stored procedure. And from what I am seeing in the properties for each parameter, the AllowDBNull property is working only part of the time. The only trend I can see in my experience so far (and I have done very little testing outside of a couple sproc's) is the parameters I am passing as strings are not cooperating with the AllowDBNull setting and the parameters that are type int are behaving as expected.

    Thursday, April 23, 2009 12:22 AM
  • User-1121501850 posted

    Adam,

    This is a limitation of typed datasets.  The dataset generator will never generate nullable properties.  So from your example, the "ApprovingUser" property will always be of type int, even if you've specifically specified that the column is nullable by setting the AllowDBNull property in the designer.

    The only way to set such properties to null is to use the helper methods that the dataset generator also creates.  The methods are named after your column name, so in your case, you should have methods on the data row object called IsApprovingUserNull() and SetApprovingUserNull().

    Obviously, it's a pain in the ass to have to use these methods, and it confuses a lot of people.  It would be much better if they simply generated nullable properties.  People have been pleading for support for this until they're blue in the face, yet MSFT don't seem to be listening.  See the latter part of the following thread for details:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/2e1c4146-994a-4ebb-b090-fd271aebb880/

    If you're interested in getting this resolved, please also vote for the suggestions on Connect:

    https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=105927

    https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=383175

    https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=284009

    Thursday, April 23, 2009 9:45 AM
  • User905154917 posted

    There is a great Visual Studio extension called 'DataSet Light' which can repalce the build in ADO.NET DataSets and adds support for nullable types!

    Give it a try: https://visualstudiogallery.msdn.microsoft.com/a42dfe9c-a771-4872-b1a7-82cb94560ae1?SRC=VSIDE

    Saturday, November 29, 2014 12:36 PM