Article ID: 101522
Article Last Modified on 10/28/2003
Insert into Customer (Name, City) values ("bob", NULL)
For example, look at the schema definition shown in the More Information
section below. If the table definition is as in A, the Insert fails because
it is an attempt to insert NULL into a non-null column. If the table
definition is as in B, the Insert command inserts "bob" and Null into the
table -- bypassing the default of "Seattle" for City
Insert into Customer (Name) values ("bob")
This would correctly insert "bob" and "Seattle" into the Customer table.
// SQL Server schema definition
// A) City is defined 'non-nullable' for behavior (1) to manifest
Create table Customer
(Name char(30) not null , City char(30) not null)
// B) City is defined 'nullable' for behavior (2) to manifest
Create table Customer
(Name char(30) not null , City char(30) null)
Create Unique Index Customer_ndx on Customer(name)
Create Default city_default as "Seattle"
sp_bindefault city_default, 'table.city'
// VB Code to insert a new row into SQL Server
Dim DS as Dynaset
DS = DB.Createdynaset ("Customer")
DS.AddNew
DS("Name") = "bob"
// No code to set the value for 'City'
DS.Update
DS.Close
If the table definition for Customer is as in A, an attempt to
insert a new row into SQL Server fails with the following message
from SQL Server:
Column 'Name' in table 'Customer' may not be NULL.If the table definition for Customer is as in B, the row is inserted into SQL Server, but the default has been bypassed. The values "bob" and Null are inserted into the table
Additional query words: buglist3.00 3.00 Access JET default update
Keywords: kbbug KB101522