Strange NULL value behaviour / RequiredFieldValidator

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("@dotnet.itags.org.STREET_NAME", Data.SqlDbType.VarChar)
cmd.Parameters("@dotnet.itags.org.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
Posted Via mcse.ms Premium Usenet Newsgroup Services
----
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----
http://www.mcse.msHowdy,
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 querie
s
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
--
Milosz
"Eric Layman" wrote:

> 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, ther
e
> 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 befor
e
> 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
>
>
> Posted Via mcse.ms Premium Usenet Newsgroup Services
> ----
> ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
> ----
> http://www.mcse.ms
>
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?"
"Milosz Skalecki [MCAD]" <mily242@.DONTLIKESPAMwp.pl> wrote in message
news:F8096D27-F0D9-430B-9166-75518B2F4F98@.microsoft.com...
> 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
> --
> Milosz
>
> "Eric Layman" wrote:
>
>
Posted Via mcse.ms Premium Usenet Newsgroup Services
----
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----
http://www.mcse.ms
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 acc
ess
layer or business logic layers as you assign values directly from user
controls. This may indicate, operations on the table are performed in severa
l
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
Milosz
"Eric Layman" wrote:

> 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?"
>
> "Milosz Skalecki [MCAD]" <mily242@.DONTLIKESPAMwp.pl> wrote in message
> news:F8096D27-F0D9-430B-9166-75518B2F4F98@.microsoft.com...
>
> Posted Via mcse.ms Premium Usenet Newsgroup Services
> ----
> ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
> ----
> http://www.mcse.ms
>
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)
"Eric Layman" <namyalcire[at no spam]gmail.com> wrote in message
news:1175766804_1545@.sp6iad.superfeed.net...
> 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?"
>
> "Milosz Skalecki [MCAD]" <mily242@.DONTLIKESPAMwp.pl> wrote in message
> news:F8096D27-F0D9-430B-9166-75518B2F4F98@.microsoft.com...
>
> Posted Via mcse.ms Premium Usenet Newsgroup Services
> ----
> ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
> ----
> http://www.mcse.ms