SQL Server Enterprise Manager-Error adding a record

[Vi08]

Hello,

In the past, in our tblProjects, we used the field name "CBUID" and Data Type is smallint. Now, we also have RegionID. CBUID is basically no longer needed.

However, if a person tries to add a new project into our SQL Server Database using Forms in MS Access and if I do not include the field name, "CBUID" on the form so it can be selected, they will receive an error message , "CBUID can not be null...."

I checked tblProjects Design View and CBUID field name is ok to be NULL; however, I do not know why when adding a new record, if CBUID is NULL, we'll receive error message that it cannot be NULL.

I even deleted this field name CBUID from tblProjects. Then tried to add a new record again; however, I still receive the same error message.

I was thinking there might be some integrity contraints like PK and FK. Therefore, I checked sysforeignkeys. It is FK if the Data Type is smallint.

All that being said, CBUID is FK because the Data Type is smallint. I changed it's Data Type from smallint to varchar so it is no longer a FK ; however, I still can not add a record when CBUID is NULL.

Basically, I tried:
1. Deleted CBUID from the table
2. Change the Data Type to somthing else.

However, I still receive the same error message that CBUID can not be NULL when I tried to add a record to this table without selecting CBUID.

Any help is greatly appreciated.

 

[bborissov]

Put a default value to that column.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

Have you updated your linked tables? Access may still be looking at the old structure.

"NOTHING is more important in a database than integrity." ESquared

 

[Vi08]

Well, I don't want to put a default value because I plan to permanently remove that column name soon. However, as I mentioned, I deleted it and tried to add a new record, I still receive the same error message. Even after I deleted the linked table and re add.

I also received the same error message when I try to add it from SQL Server.

 

[bborissov]

Until you have that field into the table you'll get that error. Put default value till you removed that field, and I ment put the default value not in your FrontEnd application but in SQL Server side.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

what is your insert query?

"NOTHING is more important in a database than integrity." ESquared

 

[Vi08]

I did not do it in a query. I add it directly by doing as follows:
In SQL Server Enterprise Manager, I right mouse click tblProjects, then Click on Open Table, then Return all rows. I then add it by type in a project# and so on, if I do not assign CBUID with a value. I will receive an error message. The one thing I don't understand is that the desgin view indicates that CBUID is ok to be NULL.

Also, after I deleted CBUID, then try to add a new project, I will no longer see the field name CBUID. However, why I receive the same error message that it can not be NULL when I tried to add a new record.

 

[SQLSister]

Never, ever, under any circumstances, enter data through Enterprise manager. This is an extremely poor practice that has serious performance implications for your system. Always use a query or stored proc to enter data.

Check and see if you have a trigger which is causing your problem. Also since this was the identifier, do you have foreign keys set up that must be met to do data entry?

"NOTHING is more important in a database than integrity." ESquared