pbd22 (dushkin@gmail.com) writes:
> I am trying to insert a zero into the below table and I am being told
> that:
>
> Cannot insert the value NULL into column 'UsageToday', table
> DB.dbo.Pub_Count'; column does not allow nulls. UPDATE fails. The
> statement has been terminated.
>
> What I am trying to insert is a string that I convert into an int
> before the insert. It complains
> when I try to insert the converted zero.

Juding from the error message, you are trying to insert a NULL.

Since you don't show us any code or data, there is no way we can tell
what you are doing wrong.





--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi.

I am trying to insert a zero into the below table and I am being told
that:

Cannot insert the value NULL into column 'UsageToday', table
DB.dbo.Pub_Count'; column does not allow nulls. UPDATE fails. The
statement has been terminated.

What I am trying to insert is a string that I convert into an int
before the insert. It complains
when I try to insert the converted zero.

The table is here:

CREATE TABLE [dbo].[Pub_Count](
[CPID] [int] NOT NULL,
[UsageToday] [int] NOT NULL CONSTRAINT [DF__Pub_C__Usage__19DFD96B]
DEFAULT ((0)),
CONSTRAINT [PK_Pub_Count] PRIMARY KEY CLUSTERED
(
[CPID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Thanks in advance.

What is the SQL statement that you use to insert? Try this:

INSERT INTO Pub_Count VALUES(1, '0');

INSERT INTO Pub_Count VALUES(2, CAST('0' AS INT));

You can use COALESCE to make sure the value is not NULL.


Plamen Ratchev
http://www.SQLStudio.com

Hi,

Thanks for the fast reply.

It is a stored procedure i am calling from the code-behind.
I have looked (again and again) at the values I am passing
and I never see a null value... the point it complains on I see
that I am trying to pass a zero.

Here is the bit of code where I construct the command:


List<List<string>> pubs =
null;

SqlCommand command =
conn.CreateCommand();
command.CommandText =
"vb_Update";
command.CommandType =
CommandType.StoredProcedure;

//declare 4 different (or
whatever number you need) params
SqlParameter pub_id = new
SqlParameter("@pub_id", SqlDbType.Int);
SqlParameter pub_bw = new
SqlParameter("@total_yesterday_hours", SqlDbType.Int);
SqlParameter pub_date = new
SqlParameter("@param_date", SqlDbType.SmallDateTime);

//add them to command

command.Parameters.Add(pub_id);

command.Parameters.Add(pub_bw);

command.Parameters.Add(pub_date);

pubs = new
Profile().Publisher();

for (int j = 0; j <
pubs.Count; j++)
{
int p = 0;

for (int k = 0; k <
pubs[j].Count; k++)
{

if (k != 2)
{

command.Parameters[p].Value = Convert.ToInt32(pubs[j][k]);

p++;
}
else
{


command.Parameters[p].Value = pubs[j][k];
}

}

here is the procedure:


ALTER PROCEDURE [dbo].[vb_Update]
(
@pub_id INT,
@total_yesterday_hours INT,
@param_date SMALLDATETIME
)
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF NOT EXISTS
(SELECT CountDate
FROM Pub_ViewCount
WHERE CPID = @pub_id
AND CountDate = @param_date )
BEGIN
INSERT INTO Pub_ViewCount (
CPID, CountDate, ViewCount,
EmbedCount, DirectCount,
UsageToday ) VALUES (
@pub_id, @param_date, 0,0,0,0)
END

-- get total for yesterday, add to UsageTodayBW
UPDATE Pub_ViewCount
SET Pub_ViewCount.DailyPSHours= @total_yesterday_hours
WHERE Pub_ViewCount.CountDate = @param_date
AND Pub_ViewCount.CPID = @pub_id;

UPDATE Pub_ViewCount
SET Pub_ViewCount.UsageToday = (@total_yesterday_hours - (SELECT
Pub_ViewCount.DailyPSHours
FROM Pub_ViewCount
WHERE Pub_ViewCount.CountDate = CONVERT(VARCHAR(50), DATEADD(day, -1,
@param_date), 101)
AND Pub_ViewCount.CPID = @pub_id))
WHERE Pub_ViewCount.CountDate = @param_date
AND Pub_ViewCount.CPID = @pub_id;

UPDATE Pub_Usage
SET TotalUsed = (SELECT SUM(p.UsageToday)
FROM Pub_ViewCount AS p
WHERE p.CPID = @pub_id)
WHERE PUPID = @pub_id;

END

I think the problem statement is here:

UPDATE Pub_ViewCount
SET Pub_ViewCount.UsageToday = (@total_yesterday_hours - (SELECT
Pub_ViewCount.DailyPSHours
FROM Pub_ViewCount
WHERE Pub_ViewCount.CountDate = CONVERT(VARCHAR(50), DATEADD(day, -1,
@param_date), 101)
AND Pub_ViewCount.CPID = @pub_id))
WHERE Pub_ViewCount.CountDate = @param_date
AND Pub_ViewCount.CPID = @pub_id;


Particularly:

(@total_yesterday_hours - (SELECT
Pub_ViewCount.DailyPSHours
FROM Pub_ViewCount
WHERE Pub_ViewCount.CountDate = CONVERT(VARCHAR(50), DATEADD(day, -1,
@param_date), 101)
AND Pub_ViewCount.CPID = @pub_id))


I have tried ISNULL around @total_yesterday_count but that didn't
work.

any ideas?

The problem is with the following update:

UPDATE Pub_ViewCount
SET Pub_ViewCount.UsageToday = (@total_yesterday_hours - (SELECT
Pub_ViewCount.DailyPSHours
FROM Pub_ViewCount
WHERE Pub_ViewCount.CountDate = CONVERT(VARCHAR(50), DATEADD(day, -1,
@param_date), 101)
AND Pub_ViewCount.CPID = @pub_id))
WHERE Pub_ViewCount.CountDate = @param_date
AND Pub_ViewCount.CPID = @pub_id;


If the subquery does not return any rows then you are updating to
(@total_yesterday_hours - NULL) which evaluates to NULL. Add COALESCE:

UPDATE Pub_ViewCount
SET Pub_ViewCount.UsageToday = @total_yesterday_hours - COALESCE(<subquery>,
0)
....


Plamen Ratchev
http://www.SQLStudio.com