473,241 Members | 1,585 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

Trying to insert a zero, its telling me i cannot insert a null


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.
Aug 12 '08 #1
6 1799
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
Aug 12 '08 #2
pbd22 (du*****@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, es****@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

Aug 12 '08 #3
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];
}

}

Aug 12 '08 #4
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
Aug 12 '08 #5
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?
Aug 12 '08 #6
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

Aug 12 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
by: pm940 | last post by:
Hello. I've been reading some past discussions on the NULL vs. zero. References are always made to systems or machienes that use values other than zero to represent the NULL pointer. Although...
10
by: Lyle Fairfield | last post by:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acfctNZ_HV05186465.asp "If the value of the variant argument is Null, the Nz function returns the number zero or a...
5
by: Learner | last post by:
Hello, Here is the code snippet I got strucked at. I am unable to convert the below line of code to its equavalent vb.net code. could some one please help me with this? static public...
9
by: anachronic_individual | last post by:
Hi all, Is there a standard library function to insert an array of characters at a particular point in a text stream without overwriting the existing content, such that the following data in...
10
by: teddysnips | last post by:
SQL Server 2000 (DDL below) If I try to run this code in QA: SET IDENTITY_INSERT tblAdminUsers ON INSERT INTO tblAdminUsers (fldUserID, fldUsername, fldPassword, fldFullname,
27
by: jm | last post by:
I am having trouble understanding the purposes of an interface, even though the concept of interfaces is around me all the time (user interface, for example). I'm just not understanding software...
6
by: rn5a | last post by:
During registration, users are supposed to enter the following details: First Name, Last Name, EMail, UserName, Password, Confirm Password, Address, City, State, Country, Zip & Phone Number. I am...
3
by: Dave | last post by:
I have an old web app that ues an Access database and ASP 3.0. I need to build an INSERT statement based on the contents of a form. What is the best way to handle blank text boxes that are...
4
by: Kenneth Brody | last post by:
I looked at my copy of n1124, and I didn't see anything about this particular situation... What happens if you realloc() to a size of zero? Implementations are allowed to return NULL on...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.