Clear a Field in MS Access with ASP

[Ken Schaefer]

Couple of points:

a) Even if you set "Not Required", that just means you can enter a NULL
value. It doesn't mean you can enter a Zero Length String. NULL <> "" (ie
Null does not equal Zero Length String). To allow Zero Length Strings,
there's another setting (for text fields) which says "Allow Zero Length
Strings"

b) When you want to update something as a NULL, you don't put single quotes
around it - that means a literal string containing the text "NULL". What you
want is something like:

<%
strSQL = _
"UPDATE myTable " & _
"SET myField = NULL " & _
"WHERE UserID = 1"

objConn.Execute strSQL,,adCmdText+adExecuteNoRecords
%>

Cheers
Ken

"James" <james.horne@roke.co.uk> wrote in message
news:SCk0b.2$313.277@psinet-eu-nl...
: Hopefully the title says it all. I want to delete the entry in a field in
a
: record in a MS Access database using ASP. Note I don't want to remove the
: entire record - just the vlaue of one field within in. E.g. My database
hold
: records of people, and having entered a phone number (say), I like the
: option to delete the value in this field.
:
: Within Access itself you can do "UPDATE people SET phone='Null'". However,
: trying to run the same code via ASP in a web page fails. It also won't
: accept "UPDATE people SET phone=''" - even though the field is set as "Not
: Required".
:
: I just want to make the entry blank! How difficult can this be!
:
: Any ideas please,
:
: Thanks,
:
: James
:
:

[William Tasso]

James wrote:

> Hopefully the title says it all. I want to delete the entry in a
> field in a record in a MS Access database using ASP. Note I don't
> want to remove the entire record - just the vlaue of one field within
> in. E.g. My database hold records of people, and having entered a
> phone number (say), I like the option to delete the value in this
> field.
>
> Within Access itself you can do "UPDATE people SET phone='Null'".
> However, trying to run the same code via ASP in a web page fails. It
> also won't accept "UPDATE people SET phone=''" - even though the
> field is set as "Not Required".
>

Aside from missing a 'WHERE' clause, you have described the correct way to
achieve the desired result. The problem lies elsewhere. What is the error
message?

--
William Tasso - [url]http://WilliamTasso.com[/url]

[James]

I found the problem thanks to Ken's message. I had the SQL setup to do this:
"... SET field='"&value&"' WHERE ...". Consequently, although I had
previously set the field value to be NULL, the SQL generated became ".. SET
field='NULL' WHERE ..." - Note the extra quotes around NULL :-(

I now have a function to check for NULL and all is fine:

Function checkForNull(field)

If Len(field) = 0 Then
field = "NULL"
Else
field = "'"&field&"'"
End If

checkForNull = field

End Function

Thanks again,

James


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:u5Ty6liZDHA.1872@TK2MSFTNGP12.phx.gbl...

> Couple of points:
>
> a) Even if you set "Not Required", that just means you can enter a NULL
> value. It doesn't mean you can enter a Zero Length String. NULL <> "" (ie
> Null does not equal Zero Length String). To allow Zero Length Strings,
> there's another setting (for text fields) which says "Allow Zero Length
> Strings"
>
> b) When you want to update something as a NULL, you don't put single

quotes

> around it - that means a literal string containing the text "NULL". What

you

> want is something like:
>
> <%
> strSQL = _
> "UPDATE myTable " & _
> "SET myField = NULL " & _
> "WHERE UserID = 1"
>
> objConn.Execute strSQL,,adCmdText+adExecuteNoRecords
> %>
>
> Cheers
> Ken
>
> "James" <james.horne@roke.co.uk> wrote in message
> news:SCk0b.2$313.277@psinet-eu-nl...
> : Hopefully the title says it all. I want to delete the entry in a field

in

> a
> : record in a MS Access database using ASP. Note I don't want to remove

the

> : entire record - just the vlaue of one field within in. E.g. My database
> hold
> : records of people, and having entered a phone number (say), I like the
> : option to delete the value in this field.
> :
> : Within Access itself you can do "UPDATE people SET phone='Null'".

However,

> : trying to run the same code via ASP in a web page fails. It also won't
> : accept "UPDATE people SET phone=''" - even though the field is set as

"Not

> : Required".
> :
> : I just want to make the entry blank! How difficult can this be!
> :
> : Any ideas please,
> :
> : Thanks,
> :
> : James
> :
> :
>
>