I have a table that contains two nullable foreign keys: -
CREATE TABLE [dbo].[GDBFAM](
[FAMid] [uniqueidentifier] NOT NULL,
[FAMUserName] [varchar](50) NOT NULL,
[Snip],
[FAMFather] [uniqueidentifier] NULL,
[FAMMother] [uniqueidentifier] NULL,
[Snip]
I can guarantee a unique FAMid value from (FAMUserName, FAMFather, and FAMMother) provided that I can treat NULL as a value and not as "Undefined", so I've written a procedure dbo. GETFamID2 (@Father, @Mother, @Username, @FamID) with logic: -
CREATE PROCEDURE [dbo].[GetFamID2]
@Father uniqueidentifier,
@Mother Uniqueidentifier,
@UserName varchar(50),
@FamID uniqueidentifier Output
AS
BEGIN
SET NOCOUNT ON;
Set @Famid = NULL -- Initialize
If @Father is null and @Mother is null
Return
IF @Mother is NULL
Select @FamID = Famid from GDBFAM where FAMFather = @Father and FAMMother Is NULL
and FAMUserName = @Username
ELSE
If @Father is NULL
Select @FamID = Famid from GDBFAM where FAMFather is NULL and FAMMother = @Mother
and FAMUserName = @Username
Else
Select @FamID = Famid from GDBFAM where FAMFather = @Father and FAMMother = @Mother
and FAMUserName = @Username
Return
END
Testing this with SSMS, everything worked perfectly
with values for FAMFather and FAMMother,
with one of them NULL,
with both NULL.
Next step: use this from ASP.NET. I added a Query to the appropriate tableadaptor, and wrote the following ASP.NET code: -
Dim tqfam As New FamTableAdapters.QueriesTableAdapter
Dim FamID As Nullable(Of Guid)
[Snip]
tqfam.GetFamID2(trINDI.INDIFathersINDIid, trINDI.INDIMothersINDIid, User.Identity.Name, FamID)
If FamID.HasValue then ...
trINDI is a tablerow from a record with the values of Father's and Mother's ID. Either or both can be NULL.
The above code worked perfectly provided that there were non-NULL values for both, but failed if one or both was NULL. I couldn't find any way of getting the NULL value back to my program, and so I ended up writing the following code using the tableadaptor
property "IsFieldnameNull" : -
A: I added this to my ASP.NET code: -
Dim Fatherid, Motherid As Guid
If trINDI.IsINDIFathersINDIidNull Then
Fatherid = Guid.Empty
Else
Fatherid = trINDI.INDIFathersINDIid
End If
If trINDI.IsINDIMothersINDIidNull Then
Motherid = Guid.Empty
Else
Motherid = trINDI.INDIMothersINDIid
End If
tqfam.GetFamID2(Fatherid, Motherid, User.Identity.Name, FamID)
B. I added this code to my SQL procedure, just after SET NOCOUNT ON;
If @Father = '00000000-0000-0000-0000-000000000000'
Set @Father = NULL
If @Mother = '00000000-0000-0000-0000-000000000000'
Set @Mother = NULL
Everything now works perfectly again, but it seems insane to have to do this. In my ASP.NET code I am explicitly testing for a DBNull value, converting it into something else just to get past ASP.NET's exception handling, then passing it back to SQL to convert
back into DBNull.
I thought that I'd already made it perfectly clear. I have no problem handling the NULL value in SQL. The problem arises in ASP.NET.
Using a tableadaptor I read a record into trINDI. This has a field, INDIMothersINDIid that is the key of another record, and may be NULL.
As far as I can tell, there is no way that I can refer to trINDI.INDIMothersINDIId when it has a NULL value without causing an exception. In the tableadaptor there is a property "Null Value" which defaults to "(Throw Exception)", but this only seems to be
able to be changed for string variables: when I attempted to reset it to "(Empty)" or "(Nothing)" Visual Studio gave me an error. I therefore have to test for NULL with
If trINDI.IsINDIMothersINDIIdNull THEN ...
With the code that I gave in my first post, I am already passing "some value instead of NULL". In the SQL Procedure I then check for that value. It all works.
My question is: is there another, better, way that I've missed? My code works correctly, but it seems insane to have to write all this code. If the field had been a String field (Varchar in SQL) then I would have simply chosen the property "(Nothing)"
and there would have been no problem.
I have a table that contains two nullable foreign keys: -
CREATE TABLE [dbo].[GDBFAM](
[FAMid] [uniqueidentifier] NOT NULL,
[FAMUserName] [varchar](50) NOT NULL,
[Snip],
[FAMFather] [uniqueidentifier] NULL,
[FAMMother] [uniqueidentifier] NULL,
[Snip]
You have a bad design. Instead of trying to cope with it using coding cartwheels, why not simply fix it.
What would probably work would be adding a record to this table where FAMUserName is "not applicable" and the FAMid, FAMFather, and FAMMother fields have the same value. Then update all other records so that FAMFather and FAMMother have this value if they
are null. Finally modify your design so those fields can't be null and have a default value from the "not applicable" record.
Wow, how would this help? My current design might be bad, but this would make it SOOOO much worse, and wouldn't solve the underlying issue that I'm complaining about.
Firstly, the use of a "Magic value" like "not applicable" is a VERY bad idea. FamUserName is the user's logon id, and is assigned by a user when they register. It would only take one idiot to create a userid of "not applicable" to break my code. One should
NEVER have a special value, however unlikely, in a value that a user can create. Of course I could put some code into the registration logic to ensure that this became a forbidden value, but then I'd have extra code there, together with the necessity of documenting
the reason for it, and I'd end up with some unwanted code bloat.
Secondly, how does this help? The issue is that ASP.NET doesn't handle NULL GUID values at all well. You are correct to point out that I've had to code cartwheels to get around the problem, but changing the database design to avoid nullable foreign key
fields isn't necessarily simpler, it's just coding cartwheels in the database instead of in ASP, and will weaken database integrity by moving checks out of the database and into the ASP.NET code.
Isn't this exactly the situation that NULL values are indented for? Codd and Date introduced NULL into the relational model to allow us to express "Value not known" and avoid special values like 0, blank, (and "not applicable") that just might be a real
value. Thus if the INDI record has a NULL Mother Foreign Key this is saying that we don't know the mother of that individual. Which is exactly correct. If however the INDI record had a value of GUID.Empty then the database is now implying that this individual's
mother is the record with key GUID.Empty, i.e. '00000000-0000-0000-0000-000000000000' and somewhere else (code? documentation?) we find the rule telling us that this is not a real person.
By the way this record would have to exist. Following best practice I have put as many of the relationships as I can into the database and so the database enforces the rule that if there is a Mother FK then it points to another INDI record and this is a
record for a female. And what do I do about a NULL Father record? It can't point to the same record, as we've just set this up to be a female! So now I've got two special values. Now the rule that the INDI record with key GUID.Empty is not a real person is
not carried in the databsase, but is enforced by the ASP.NET code and/or mentioned in the documentation, and database integrity as been weakened by moving the checks out of the database and into the code? I'd better remember too that COUNT(*) no longer returns
the number of individuals represented in the database as there are at least two dummy records.
And all of this complexity is being suggested to avoid a problem in ASP.NET where I don't appear to be able to handle NULL GUID values through a table adaptor, and so have to explicitly use the test IsFieldNameNULL. All I need to be able to do is to set
the NullValue property of the FK field to (Nothing) in the tableadaptor, as I could if the FK were a string rather than a GUID, and all the complexity would go away. I'm hoping that somebody will show me an option to allow me to do this.
Robert Barne...
Member
451 Points
708 Posts
Handling NULL values through a tableadaptor and SQL Proc - is there a smarter way?
Dec 23, 2012 02:46 AM|LINK
I have a table that contains two nullable foreign keys: -
CREATE TABLE [dbo].[GDBFAM](
[FAMid] [uniqueidentifier] NOT NULL,
[FAMUserName] [varchar](50) NOT NULL,
[Snip],
[FAMFather] [uniqueidentifier] NULL,
[FAMMother] [uniqueidentifier] NULL,
[Snip]
I can guarantee a unique FAMid value from (FAMUserName, FAMFather, and FAMMother) provided that I can treat NULL as a value and not as "Undefined", so I've written a procedure dbo. GETFamID2 (@Father, @Mother, @Username, @FamID) with logic: -
CREATE PROCEDURE [dbo].[GetFamID2]
@Father uniqueidentifier,
@Mother Uniqueidentifier,
@UserName varchar(50),
@FamID uniqueidentifier Output
AS
BEGIN
SET NOCOUNT ON;
Set @Famid = NULL -- Initialize
If @Father is null and @Mother is null
Return
IF @Mother is NULL
Select @FamID = Famid from GDBFAM where FAMFather = @Father and FAMMother Is NULL
and FAMUserName = @Username
ELSE
If @Father is NULL
Select @FamID = Famid from GDBFAM where FAMFather is NULL and FAMMother = @Mother
and FAMUserName = @Username
Else
Select @FamID = Famid from GDBFAM where FAMFather = @Father and FAMMother = @Mother
and FAMUserName = @Username
Return
END
Testing this with SSMS, everything worked perfectly
Next step: use this from ASP.NET. I added a Query to the appropriate tableadaptor, and wrote the following ASP.NET code: -
Dim tqfam As New FamTableAdapters.QueriesTableAdapter
Dim FamID As Nullable(Of Guid)
[Snip]
tqfam.GetFamID2(trINDI.INDIFathersINDIid, trINDI.INDIMothersINDIid, User.Identity.Name, FamID)
If FamID.HasValue then ...
trINDI is a tablerow from a record with the values of Father's and Mother's ID. Either or both can be NULL.
The above code worked perfectly provided that there were non-NULL values for both, but failed if one or both was NULL. I couldn't find any way of getting the NULL value back to my program, and so I ended up writing the following code using the tableadaptor property "IsFieldnameNull" : -
A: I added this to my ASP.NET code: -
Dim Fatherid, Motherid As Guid
If trINDI.IsINDIFathersINDIidNull Then
Fatherid = Guid.Empty
Else
Fatherid = trINDI.INDIFathersINDIid
End If
If trINDI.IsINDIMothersINDIidNull Then
Motherid = Guid.Empty
Else
Motherid = trINDI.INDIMothersINDIid
End If
tqfam.GetFamID2(Fatherid, Motherid, User.Identity.Name, FamID)
B. I added this code to my SQL procedure, just after SET NOCOUNT ON;
If @Father = '00000000-0000-0000-0000-000000000000'
Set @Father = NULL
If @Mother = '00000000-0000-0000-0000-000000000000'
Set @Mother = NULL
Everything now works perfectly again, but it seems insane to have to do this. In my ASP.NET code I am explicitly testing for a DBNull value, converting it into something else just to get past ASP.NET's exception handling, then passing it back to SQL to convert back into DBNull.
Can anybody suggest a more elegant approach?
Thank you, Robert.
nikunjnandan...
Participant
882 Points
223 Posts
Re: Handling NULL values through a tableadaptor and SQL Proc - is there a smarter way?
Dec 23, 2012 06:17 AM|LINK
I am not clearly uncerstanding your problem.
You have a problem in handing in null value in form level or in sql level.
Please specify your problem only.whether it's db level or form level.
i suuggest you pass some value instead of NULL.
Then check for condition for that value.
You can make also enum for that values also.
Nikunj Nandaniya
My Blog
Robert Barne...
Member
451 Points
708 Posts
Re: Handling NULL values through a tableadaptor and SQL Proc - is there a smarter way?
Dec 23, 2012 09:34 PM|LINK
I thought that I'd already made it perfectly clear. I have no problem handling the NULL value in SQL. The problem arises in ASP.NET.
Using a tableadaptor I read a record into trINDI. This has a field, INDIMothersINDIid that is the key of another record, and may be NULL.
As far as I can tell, there is no way that I can refer to trINDI.INDIMothersINDIId when it has a NULL value without causing an exception. In the tableadaptor there is a property "Null Value" which defaults to "(Throw Exception)", but this only seems to be able to be changed for string variables: when I attempted to reset it to "(Empty)" or "(Nothing)" Visual Studio gave me an error. I therefore have to test for NULL with
If trINDI.IsINDIMothersINDIIdNull THEN ...
With the code that I gave in my first post, I am already passing "some value instead of NULL". In the SQL Procedure I then check for that value. It all works.
My question is: is there another, better, way that I've missed? My code works correctly, but it seems insane to have to write all this code. If the field had been a String field (Varchar in SQL) then I would have simply chosen the property "(Nothing)" and there would have been no problem.
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Handling NULL values through a tableadaptor and SQL Proc - is there a smarter way?
Dec 23, 2012 10:27 PM|LINK
You have a bad design. Instead of trying to cope with it using coding cartwheels, why not simply fix it.
What would probably work would be adding a record to this table where FAMUserName is "not applicable" and the FAMid, FAMFather, and FAMMother fields have the same value. Then update all other records so that FAMFather and FAMMother have this value if they are null. Finally modify your design so those fields can't be null and have a default value from the "not applicable" record.
Robert Barne...
Member
451 Points
708 Posts
Re: Handling NULL values through a tableadaptor and SQL Proc - is there a smarter way?
Dec 24, 2012 03:02 AM|LINK
Wow, how would this help? My current design might be bad, but this would make it SOOOO much worse, and wouldn't solve the underlying issue that I'm complaining about.
Firstly, the use of a "Magic value" like "not applicable" is a VERY bad idea. FamUserName is the user's logon id, and is assigned by a user when they register. It would only take one idiot to create a userid of "not applicable" to break my code. One should NEVER have a special value, however unlikely, in a value that a user can create. Of course I could put some code into the registration logic to ensure that this became a forbidden value, but then I'd have extra code there, together with the necessity of documenting the reason for it, and I'd end up with some unwanted code bloat.
Secondly, how does this help? The issue is that ASP.NET doesn't handle NULL GUID values at all well. You are correct to point out that I've had to code cartwheels to get around the problem, but changing the database design to avoid nullable foreign key fields isn't necessarily simpler, it's just coding cartwheels in the database instead of in ASP, and will weaken database integrity by moving checks out of the database and into the ASP.NET code.
Isn't this exactly the situation that NULL values are indented for? Codd and Date introduced NULL into the relational model to allow us to express "Value not known" and avoid special values like 0, blank, (and "not applicable") that just might be a real value. Thus if the INDI record has a NULL Mother Foreign Key this is saying that we don't know the mother of that individual. Which is exactly correct. If however the INDI record had a value of GUID.Empty then the database is now implying that this individual's mother is the record with key GUID.Empty, i.e. '00000000-0000-0000-0000-000000000000' and somewhere else (code? documentation?) we find the rule telling us that this is not a real person.
By the way this record would have to exist. Following best practice I have put as many of the relationships as I can into the database and so the database enforces the rule that if there is a Mother FK then it points to another INDI record and this is a record for a female. And what do I do about a NULL Father record? It can't point to the same record, as we've just set this up to be a female! So now I've got two special values. Now the rule that the INDI record with key GUID.Empty is not a real person is not carried in the databsase, but is enforced by the ASP.NET code and/or mentioned in the documentation, and database integrity as been weakened by moving the checks out of the database and into the code? I'd better remember too that COUNT(*) no longer returns the number of individuals represented in the database as there are at least two dummy records.
And all of this complexity is being suggested to avoid a problem in ASP.NET where I don't appear to be able to handle NULL GUID values through a table adaptor, and so have to explicitly use the test IsFieldNameNULL. All I need to be able to do is to set the NullValue property of the FK field to (Nothing) in the tableadaptor, as I could if the FK were a string rather than a GUID, and all the complexity would go away. I'm hoping that somebody will show me an option to allow me to do this.
TabAlleman
All-Star
15575 Points
2702 Posts
Re: Handling NULL values through a tableadaptor and SQL Proc - is there a smarter way?
Dec 24, 2012 01:37 PM|LINK
My approach would be to supply NULL as default values for the two nullable parameters, like so:
CREATE PROCEDURE [dbo].[GetFamID2]
@Father uniqueidentifier = NULL,
@Mother Uniqueidentifier = NULL,
@UserName varchar(50),
@FamID uniqueidentifier Output
AS
and then in the .net code, if the value you would be passing for either of those parameters is null or empty, simply don't pass that parameter at all.