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?
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)
<div mce_keep="true"> You get there by opening the dataset designer</div>
<div mce_keep="true">Click on the Table Adapter where it says "OutgoingTransferTableAdapter" to select the TableAdapter</div>
<div mce_keep="true">Open the appropriate Command node in the Properties window</div>
<div mce_keep="true">Open the Parameters Dialog by clicking on Parameters section</div>
<div mce_keep="true">Change the AllowDbNull property on the ApprovingUser parameter.</div>
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
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. =(
dataset nullable column AllowDBNull exception
If this help you anyway, remember to mark it as a "answer". ;-)
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.
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:
Adam Maras
Member
65 Points
21 Posts
Strongly-Typed DataSet/Nullable column issue
Mar 02, 2008 11:09 PM|LINK
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?
ADO.NET Strongly-Typed DataSet
jogi
Member
445 Points
80 Posts
Re: Strongly-Typed DataSet/Nullable column issue
Mar 03, 2008 08:18 PM|LINK
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)
hope that helps
-jogi
Adam Maras
Member
65 Points
21 Posts
Re: Strongly-Typed DataSet/Nullable column issue
Mar 04, 2008 06:21 PM|LINK
No, the column's AllowDbNull property is and has been set correctly in all places.
jogi
Member
445 Points
80 Posts
Re: Strongly-Typed DataSet/Nullable column issue
Mar 04, 2008 09:29 PM|LINK
I was referring to the Parameter. Not the column. -jo
Adam Maras
Member
65 Points
21 Posts
Re: Strongly-Typed DataSet/Nullable column issue
Mar 04, 2008 09:52 PM|LINK
I was referring to both [:P]
Wencui Qian ...
All-Star
56784 Points
5796 Posts
Microsoft
Re: Strongly-Typed DataSet/Nullable column issue
Mar 05, 2008 07:46 AM|LINK
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!
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework
indiekiduk
Member
4 Points
2 Posts
Re: Strongly-Typed DataSet/Nullable column issue
Mar 28, 2008 10:26 AM|LINK
arochax
Member
14 Points
17 Posts
Re: Strongly-Typed DataSet/Nullable column issue
Jun 19, 2008 05:44 PM|LINK
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. =(
dataset nullable column AllowDBNull exception
tmrhymer
Member
143 Points
79 Posts
Re: Strongly-Typed DataSet/Nullable column issue
Apr 23, 2009 04:22 AM|LINK
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.
danieldsmith
Member
18 Points
4 Posts
Re: Strongly-Typed DataSet/Nullable column issue
Apr 23, 2009 01:45 PM|LINK
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