November 18, 2013 at 3:05 pm
I've written a Windows Communication Foundation (WCF) component which is used to put data into a SQL Server 2012 database. In testing I keep on getting the following error message:
"The value for column 'DaysUseLast30' in table 'ASISubstanceUse' is DBNull."
The DaysUseLast30 column is nullable, and I've just added a default value of 0 to that column (which is a valid value and would be what users would intend, when they don't enter a value, because the application we're writing displays a 0 for that column). So, I'm not sure what's wrong. Here's the table's definition:
CREATE TABLE [dbo].[ASISubstanceUse](
[ClientNumber] [int] NOT NULL,
[CaseNumber] [tinyint] NOT NULL,
[Followup] [tinyint] NOT NULL,
[SubstanceType] [tinyint] NOT NULL,
[DaysUseLast30] [tinyint] NULL,
[LifeUseYears] [tinyint] NULL,
[Route] [tinyint] NULL,
[AgeOfFirstUse] [tinyint] NULL,
CONSTRAINT [PK_ASISubstanceUse] PRIMARY KEY NONCLUSTERED
(
[ClientNumber] ASC,
[CaseNumber] ASC,
[Followup] ASC,
[SubstanceType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ASISubstanceUse] ADD CONSTRAINT [DF_ASISubstanceUse_DaysUseLast30] DEFAULT ((0)) FOR [DaysUseLast30]
GO
Now, at first I thought that the problem was that my code is likely passing in a null (DBNull) to that parameter, that's why I decided to give it a default value of 0. But then I realized, looking at the table's definition, that it should work fine with a null being passed for the column during an insertion operation. So, why am I getting that error? (There are no triggers associated with that table.)
Kindest Regards, Rod Connect with me on LinkedIn.
November 18, 2013 at 3:16 pm
There is nothing wrong with your ddl. I am guessing this is on the .NET side. What does the code look like?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 18, 2013 at 4:02 pm
Sean Lange (11/18/2013)
There is nothing wrong with your ddl. I am guessing this is on the .NET side. What does the code look like?
I concur. Run Profiler with RPC completed and TSQL Batch completed and capture the actual call and see what it looks like.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 18, 2013 at 5:08 pm
The use of the word "DBNull" suggests that this is an error on the .NET side, not in SQL Server.
Debug the code in visual studio with break on "Common Language Runtime Exception" "Thrown" checked in the Exceptions dialog (Ctl+Alt+E).
This will help you find the actual line of code that is failing and it will most likely be attempting to access a value that is DBNull.
Or post a sample of the .NET code and see if we can spot the problem...but debug it first.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 19, 2013 at 1:45 pm
Hello Kevin,
I've finally had a chance to give your suggestion a try. I got SQL Profiler running, but here's what it gave me:
exec sp_executesql N'UPDATE [ASISubstanceUse] SET [ClientNumber] = @ClientNumber,
[CaseNumber] = @CaseNumber, [Followup] = @Followup, [SubstanceType] = @SubstanceType,
[DaysUseLast30] = @DaysUseLast30, [LifeUseYears] = @LifeUseYears, [Route] = @Route,
[AgeOfFirstUse] = @AgeOfFirstUse
WHERE (([ClientNumber] = @Original_ClientNumber) AND ([CaseNumber] = @Original_CaseNumber) AND
([Followup] = @Original_Followup) AND ([SubstanceType] = @Original_SubstanceType) AND
((@IsNull_DaysUseLast30 = 1 AND [DaysUseLast30] IS NULL) OR ([DaysUseLast30] = @Original_DaysUseLast30))
AND ((@IsNull_LifeUseYears = 1 AND [LifeUseYears] IS NULL) OR ([LifeUseYears] = @Original_LifeUseYears))
AND ((@IsNull_Route = 1 AND [Route] IS NULL) OR ([Route] = @Original_Route)) AND
((@IsNull_AgeOfFirstUse = 1 AND [AgeOfFirstUse] IS NULL) OR ([AgeOfFirstUse] = @Original_AgeOfFirstUse)));
SELECT ClientNumber, CaseNumber, Followup, SubstanceType, DaysUseLast30, LifeUseYears, Route,
AgeOfFirstUse FROM ASISubstanceUse WHERE (CaseNumber = @CaseNumber) AND
(ClientNumber = @ClientNumber) AND (Followup = @Followup) AND
(SubstanceType = @SubstanceType)',
N'@ClientNumber int,
@CaseNumber tinyint,
@Followup tinyint,
@SubstanceType tinyint,
@DaysUseLast30 tinyint,
@LifeUseYears tinyint,
@Route tinyint,
@AgeOfFirstUse tinyint,
@Original_ClientNumber int,
@Original_CaseNumber tinyint,
@Original_Followup tinyint,
@Original_SubstanceType tinyint,
@IsNull_DaysUseLast30 int,
@Original_DaysUseLast30 tinyint,
@IsNull_LifeUseYears int,
@Original_LifeUseYears tinyint,
@IsNull_Route int,
@Original_Route tinyint,
@IsNull_AgeOfFirstUse int,
@Original_AgeOfFirstUse tinyint',
@ClientNumber=149092,
@CaseNumber=1,
@Followup=0,
@SubstanceType=8,
@DaysUseLast30=0,
@LifeUseYears=8,
@Route=2,
@AgeOfFirstUse=19,
@Original_ClientNumber=149092,
@Original_CaseNumber=1,
@Original_Followup=0,
@Original_SubstanceType=8,
@IsNull_DaysUseLast30=0,
@Original_DaysUseLast30=0,
@IsNull_LifeUseYears=0,
@Original_LifeUseYears=8,
@IsNull_Route=0,
@Original_Route=2,
@IsNull_AgeOfFirstUse=0,
@Original_AgeOfFirstUse=19
(Note: SQL Profiler gave this to me as two long lines, which I've broken up for clarify, I hope. if it isn't clear, that's probably my fault.)
Now, in looking at this I can't even see the data that I'm trying to insert into the table. It would have a value, for @SubstanceType, of 20. It isn't there. I've checked every occurance of the table ASISubstanceUse, and whenever @SubstanceUse is given, it is never equal to 20. So I'm not sure why that isn't getting through, but it looks to me as though it isn't even making it to the database for the attempt.
Kindest Regards, Rod Connect with me on LinkedIn.
November 19, 2013 at 2:56 pm
OK, I found the problem, and it wasn't with the database. Actually, running SQL Profiler, indirectly, pointed to what was wrong. As I said previously I couldn't find an INSERT statement, which I figured I should. So I thought, what if that's because it didn't try to insert the record? And that's the issue. I used ADO.NET back when I wrote this, and here's what I had:
serverCopy.ASISubstanceUse.Rows.Add(clientNumber,
caseNumber,
0,
DrugAlcoholDataSet.ASISubstanceUse.SubstanceType,
DrugAlcoholDataSet.ASISubstanceUse.DaysUseLast30,
DrugAlcoholDataSet.ASISubstanceUse.LifeUseYears,
DrugAlcoholDataSet.ASISubstanceUse.Route,
DrugAlcoholDataSet.ASISubstanceUse.AgeOfFirstUse);
(I'm using C#.) I found that the class ASISubstanceUse's fields like Route, DaysUseLast30, etc., were all not-nullable. Since this is what's known as a strongly typed dataset, it has methods that were generated for it, for each of the fields, with names like IsDaysUseLast30Null(), which returns a Boolean of True if the value is null, and False otherwise. So I changed the code to this:
serverCopy.ASISubstanceUse.Rows.Add(clientNumber,
caseNumber,
0,
DrugAlcoholDataSet.ASISubstanceUse.SubstanceType,
(DrugAlcoholDataSet.ASISubstanceUse.IsDaysUseLast30Null() ? 0 : DrugAlcoholDataSet.ASISubstanceUse.DaysUseLast30),
(DrugAlcoholDataSet.ASISubstanceUse.IsLifeUseYearsNull() ? 0 : DrugAlcoholDataSet.ASISubstanceUse.LifeUseYears),
(DrugAlcoholDataSet.ASISubstanceUse.IsRouteNull() ? 0 : DrugAlcoholDataSet.ASISubstanceUse.Route),
(DrugAlcoholDataSet.ASISubstanceUse.IsAgeOfFirstUseNull() ? 0 : DrugAlcoholDataSet.ASISubstanceUse.AgeOfFirstUse));
I hope that this might help someone.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply