column does not allow nulls. INSERT fails.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

column does not allow nulls. INSERT fails.

Author

Topic

Kimbrly
Starting Member

9 Posts

Posted - 2003-02-16 : 10:12:31

I got another error, when i tried to submit a form that worked when i was using ACCESS, but since i imported my files into SQL i am getting this error...

[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert the value NULL into column 'RatingsID', table 'sidewalk.dbo.StoreReviews'; column does not allow nulls. INSERT fails.

Insert Into StoreReviews ( StreetID, StoreID, RatingLink, Atmosphere, Price, Service, Quality, AVGrating, PostedBy, PostedDate, Review ) values ( 1, 174, ' ', 4.4, 4.1, 4.7, 4.9, 5.0, 'Michele', {d '2003-02-11'}, 'I love Aldo shoes, they always have the best styles.' )
DATASOURCE SidewalkSavvy

Do you think this error is the same problem, the data type was changed??? It shouldn't be null.. it is supposed to be calculated... and inserted...

Here is the code i have for that...

<CFSET thisAverage=Round((FORM.TXTSLIDERWEIGHT_0 + FORM.TXTSLIDERWEIGHT_1+ FORM.TXTSLIDERWEIGHT_2 + FORM.TXTSLIDERWEIGHT_3)/4)>

<CFQUERY name="insertrequest" DATASOURCE="#request.dsn#">
Insert Into StoreReviews
(
StreetID,
StoreID,
RatingLink,
Atmosphere,
Price,
Service,
Quality,
AVGrating,
PostedBy,
PostedDate,
Review

)
values
(
#getStreetID.StreetID#,
#getStreetID.StoreID#,
'<img src="Images/#Round(getAVGrating.AVGrating)#Stars.bmp">',
#FORM.TXTSLIDERWEIGHT_0#,
#FORM.TXTSLIDERWEIGHT_1#,
#FORM.TXTSLIDERWEIGHT_2#,
#FORM.TXTSLIDERWEIGHT_3#,
#getAVGrating.AVGrating#,
'#form.PostedBy#',
#CreateODBCDate(DateFormat(now(),"mm/dd/yy"))#,
'#form.Review#'
)
</CFQUERY>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-16 : 10:15:30

The RatingsID column is not listed in the INSERT column list, and if that columns does not have a default or automatically generated value associated with it, it needs to be explicitly passed in the INSERT statement.

Kimbrly
Starting Member

9 Posts

Posted - 2003-02-16 : 10:28:40

The ratingsID is an AutoNumber in the table.. at least it was in Access... maybe the data type was switched...

The RatingsID column is not listed in the INSERT column list, and if that columns does not have a default or automatically generated value associated with it, it needs to be explicitly passed in the INSERT statement.

[/quote]

Kimbrly
Starting Member

9 Posts

Posted - 2003-02-16 : 10:55:49

Ok.. so i'm trying to add the RatingsID into the Insert query, but isn't it supposed to be automatically generated?? I am trying to create a query...

<CFQUERY name="getStreetID" DATASOURCE="#request.dsn#">
Select StreetID, StoreName, StoreID
From StoreDirectory
Where StoreName='#form.Name#'
</cfquery>

<CFQUERY name="getRatingsID" DATASOURCE="#request.dsn#">
SELECT RatingsID
FROM StoreReviews
WHERE StoreID=#getStreetID.StreetID#

<CFQUERY name="insertrequest" DATASOURCE="#request.dsn#">
Insert Into StoreReviews
(
RatingsID,
StreetID,
StoreID,
RatingLink,
Atmosphere,
Price,
Service,
Quality,
AVGrating,
PostedBy,
PostedDate,
Review

)
values
(
#getRatingsID.RatingsID#,
#getStreetID.StreetID#,
#getStreetID.StoreID#,
'<img src="Images/#Round(getAVGrating.AVGrating)#Stars.bmp">',
#FORM.TXTSLIDERWEIGHT_0#,
#FORM.TXTSLIDERWEIGHT_1#,
#FORM.TXTSLIDERWEIGHT_2#,
#FORM.TXTSLIDERWEIGHT_3#,
#getAVGrating.AVGrating#,
'#form.PostedBy#',
#CreateODBCDate(DateFormat(now(),"mm/dd/yy"))#,
'#form.Review#'
)
</CFQUERY>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-16 : 10:58:34

quote:
Ok.. so i'm trying to add the RatingsID into the Insert query, but isn't it supposed to be automatically generated??

Did you check that it is indeed an Identity column? Can you post the DDL (CREATE TABLE statement) of the table in question?

Kimbrly
Starting Member

9 Posts

Posted - 2003-02-16 : 11:18:57

I'm sorry... i don't have access to my database right now, i'm not at my house, but I can check later... I am just learning how to use SQL, it is much different that what I am used to!! I appreciate the help here...

All i know is that the ratingsID was an autonumber, but i'm not sure what the data type is now...

Kimbrly
Starting Member

9 Posts

Posted - 2003-02-16 : 19:06:02

Ok.... i checked the data type for RatingsID and it says INT length 4 and it doesn't allow Nulls. Does this help?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-16 : 20:03:10

If it does not include the keyword Identity then it's not an identity column. You can't add it either, you'd have to re-create the table and import the data into it.

Kimbrly
Starting Member

9 Posts

Posted - 2003-02-16 : 22:05:34

Is there another solution... i rather avoid recreating the table if possible... can i insert the RatingID in my action page.... as an autonumber...????

quote:

If it does not include the keyword Identity then it's not an identity column. You can't add it either, you'd have to re-create the table and import the data into it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-16 : 22:16:33

Without the exact structure I can't say. But it's not likely; it will involve more work than setting the column with identity.

jfuex
Starting Member

29 Posts

Posted - 2003-03-01 : 10:41:43

You won't be able to set the autonumber access property because the table is not an Access table. I think what the poster is missing is the fact that in SQL Server "Identity" = AutoNumber in Microsoft Access. Of course she still has the problem that the table is already populated. She should be able to do the following to add it (I think).

Rename the current ID column to something else.
Create a new ID column and set the identity property.
Delete the original ID column.

I can't remember if SQL lets you add a new identity column to a table with existing data, but if not it is really no big deal to re-create the table since SQL will automatically script the creation of that table and a simple Append Query (Access Lingo) will repopulate it.

kedst1
Starting Member

2 Posts

Posted - 2003-03-03 : 17:04:33

You should be able to run the following commands to add a new identity column, remove the current RatingsID and then rename the new identity column to RatingsID:

ALTER TABLE StoreReviews ADD
RatingsID_new int NOT NULL IDENTITY (1, 1)

EXEC sp_rename StoreReviews.RatingsID, RatingsID_old

EXEC StoreReviews.RatingsID_new, RatingsID

ALTER TABLE StoreReviews drop
RatingsID_old

I have used the solution above many times. In case it does not work you should be able to follow the procedure below to use a temporary table:

CREATE TABLE TMP_StoreReviews (RatingsID int identity (1, 1) not null, <The rest of the columns from StoreReviews ....>

INSERT Into TMP_StoreReviews
(StreetID, StoreID, RatingLink, Atmosphere, Price, Service, Quality, AVGrating, PostedBy, PostedDate, Review)
SELECT (StreetID, StoreID, RatingLink, Atmosphere, Price, Service, Quality, AVGrating, PostedBy, PostedDate, Review) from StoreReviews

sp_rename 'StoreReviews', 'Old_StoreReviews'
sp_rename 'TMP_StoreReviews, 'StoreReviews'

As long as there are no referenecs(foreign keys) to the StoreReviews table either one of these solutions should work. If there are references, I am not sure SQL Server will automatically pick them up when you rename the temp table to "StoreReviews".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources