Posted by Erland Sommarskog on 01/14/06 00:17

bjose21 (robert.jose@accenture.com) writes:
> The solution is that a NULL value needs to passed to SQL from ASP.
> Thats fine...I understand the why the problem is happening and the
> solution around it. HOWEVER, I can't seem to get the proper syntax to
> work in the ASP page. It seems no matter what I try the "1/1/1900"
> still results. Below are a few variations of the code that I have
> tried, with the key part being the first section. Does anyone have any
> suggestions?!?!?
> ______________
> cDateClosed = ""
> If(Request.Form("dateClosed")= "") Then
> cDateClosed = (NULL)
> end if
>
> sql="UPDATE rfa SET "&_
> "dateClosed='"& cDateClosed &"', "&_
> "where rfaId='"& Request.Form("RFAID")&"'"
> _______________

The complete UPDATE statement should read:

UPDATE rfa SET cDateClosed = NULL WHERE rfaid = 'whatever'

However, you should not build any complete SQL statements in your
ASP code. Your code should read:

sql = "UPDATE rfa SET dateClosed = ? WHERE rfaId = ?"

Then you should define two parameters for your command, and pass the
values of cDateClosed and Request.Form("RFAID"). There are two important
gains with this:

1) You protected against an attack known as SQL injection.
2) You use SQL Server more effciently, as the plan for the
parameterised query is cached.

Sorry, I can't give any detailed examples for ASP, as I don't know it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]