Strange NULL value behaviour / RequiredFieldValidator

[Eric Layman]

Hi everyone,

Im puzzled by a NULL behaviour in SQL 2000 server.

There is a column in the table that does not allow NULL.

During data mining, the staff noted that, for that particular column, there
are a few records that are empty.

I do not specifically know whether they are "alt + 0160" character.

What are the reasons that can cause this to happen?


Validation front end, I've used ASP.NET's REQUIREDFIELDVALIDATOR on the
control to ensure that the field is compulsory

On the application scripting, I've the following to cleanse the data before
the data goes into the database:

cmd.Parameters.Add("@STREET_NAME", Data.SqlDbType.VarChar)
cmd.Parameters("@STREET_NAME").Value =
ReplaceSingleQuote(streetname.Text.Trim())

The function is:

Public Shared Function ReplaceSingleQuote(ByVal x As String)

If x = "" Then
Return x
End If

x = x.Replace("'", "''")
Return x

End Function

 

[Guest]

Howdy,

I see you're using Parameters so you don't need to replace any characters as
SqlParameter is responsible for that. It's only required if you build queries
through concatenation. I suspect all single quotes have been turned to doule
quotes (see it in the database). Anyway, it seems there's something wrong
with your validation as empty entries are submited.

Regards

 

[Eric Layman]

Thanks for the reply.

That's very weird.

I have no idea how to account for the blank columns!

She showed me the sql commands and that column is specifically set to NOT
NULL

She asked me "How did you do that?"

Howdy,

I see you're using Parameters so you don't need to replace any characters
as
SqlParameter is responsible for that. It's only required if you build
queries
through concatenation. I suspect all single quotes have been turned to
doule
quotes (see it in the database). Anyway, it seems there's something wrong
with your validation as empty entries are submited.

Regards

 

[Guest]

Hi Eric again,

Correct me if I’m wrong but you (or she) is confusing empty string with
NULL. NULL is a special value that simply indicates nothing is assigned,
whilst empty string is a correct value. Setting NOT NULL constraint to a
varchar column means NULL value is not allowed, but any other values (like
empty string '') are. Also check if there is a default value set for this
column. Anyway, from the code you posted, I suspect, there aren’t data access
layer or business logic layers as you assign values directly from user
controls. This may indicate, operations on the table are performed in several
places differently (different logic?). In addition, do you use stored
procedures or plain text queries? If SPs, please confirm there is no logic
inside that inserts empty string in some cases. And the last tip, have you
checked if there are any triggers created on this table?

Regards

 

[Mike C#]

Run a test to see if there are actually NULLs in that column. Run the
following query:

SELECT *
FROM MyTable
WHERE MyColumn IS NULL

If you return rows then you have some NULLs in there and we'll need to
investigate how you got NULLs into a non-nullable column. If no rows are
returned then the column does not contain NULLs, and you need to determine
how you got zero-length strings into the column if you're not expecting them
to be there. If zero-length strings are being stored but are not
acceptable, you can add a check constraint like the following to the column:

CHECK(LEN(MyColumn) > 0)