hi all,

can anybody tell me that how will i prevent to store the blank fields in any
table.
if i uncheck the allow null property of a field in a table then it only
bloks null values. bit through application ehwn i store values from a
webforms using text boxes then it will store '' in the fields and these field
accepts those values since these are not null.
also i dont want to chek this on interface level. i want that database
should through an exception to the application.

Cheers

Himanshu

Sir, 
   What exactly, would you have the Server store in the column then? If you
know what else to put there, make that value the default value, and then it
will put that into the column henyou do not specify something else...


Show quoteHide quote

Hi

Check on the UI will remove the need for a round trip to the database and
provide the user with a friendlier message.

You could do something like:

CREATE TABLE Mandatory( charcol varchar(10) NOT NULL CHECK (
NULLIF(RTRIM(charcol),'') IS NOT NULL ) )

INSERT INTO Mandatory ( charcol ) VALUES ( NULL )

/*
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'charcol', table
'Test.dbo.Mandatory'; column does not allow nulls. INSERT fails.
The statement has been terminated.
*/
INSERT INTO Mandatory ( charcol ) VALUES ( '' )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint
'CK__Mandatory__charc__42E1EEFE'. The conflict occurred in database 'Test',
table 'Mandatory', column 'charcol'.
The statement has been terminated.
*/

INSERT INTO Mandatory ( charcol ) VALUES ( '    ' )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint
'CK__Mandatory__charc__42E1EEFE'. The conflict occurred in database 'Test',
table 'Mandatory', column 'charcol'.
The statement has been terminated.
*/

Alternatively you could do it in a trigger.

John

Show quoteHide quote

add column
ntext and update/insert triggers
Error converting data type nvarchar to int.
move records from table1 to table2 if does not exist in table3
Multiple instances of MSSQL and IP addresses