Microsoft SQL Server forum - Seeking correct syntax to pass a NULL value for a datetime field from an asp page to SQL

Author

Seeking correct syntax to pass a NULL value for a datetime field from an asp page to SQL

bjose21

2006-01-13, 8:23 pm

Hello,

I've been searching the web for quite some time to resolve the problem
of "1/1/1900" returning in a datetime field in SQL that resulted from a
blank (not NULL) value being passed to it through an ASP page.

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")&"'"
_______________

______________
cDateClosed = ""
If(Request.Form("dateClosed") <> "") Then
cDateClosed = (NULL)
end if

sql="UPDATE rfa SET "&_
"dateClosed='"& cDateClosed &"', "&_
"where rfaId='"& Request.Form("RFAID")&"'"
_______________

______________
cDateClosed = ""
If(Request.Form("dateClosed")= "") Then
cDateClosed = NULL
end if

sql="UPDATE rfa SET "&_
"dateClosed='"& cDateClosed &"', "&_
"where rfaId='"& Request.Form("RFAID")&"'"
_______________
Thanks in advance!!!!

KenJ

2006-01-13, 8:23 pm

Before getting to your question, let me point out that the unfiltered
input here is a huge SQL injection attack just waiting to happen...

sql="UPDATE rfa SET "&_
"dateClosed='"& cDateClosed &"', "&_
"where rfaId='"& Request.Form("RFAID")&"'"

For the NULL, try quotes instead of parentheses around the word NULL
like this...

cDateClosed = ""
If(Request.Form("dateClosed")= "") Then
cDateClosed = "NULL" ''' <---- building a
string, we need quotes...
end if

bjose21 wrote:
> Hello,
>
> I've been searching the web for quite some time to resolve the problem
> of "1/1/1900" returning in a datetime field in SQL that resulted from a
> blank (not NULL) value being passed to it through an ASP page.
>
> 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")&"'"
> _______________
>
> ______________
> cDateClosed = ""
> If(Request.Form("dateClosed") <> "") Then
> cDateClosed = (NULL)
> end if
>
> sql="UPDATE rfa SET "&_
> "dateClosed='"& cDateClosed &"', "&_
> "where rfaId='"& Request.Form("RFAID")&"'"
> _______________
>
> ______________
> cDateClosed = ""
> If(Request.Form("dateClosed")= "") Then
> cDateClosed = NULL
> end if
>
> sql="UPDATE rfa SET "&_
> "dateClosed='"& cDateClosed &"', "&_
> "where rfaId='"& Request.Form("RFAID")&"'"
> _______________
> Thanks in advance!!!!

Erland Sommarskog

2006-01-13, 8:23 pm

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

bjose21

2006-01-18, 9:23 am

Hello,

Thanks for your responses. I was able to get around this within the asp
code by basically stating..if there is a date in the field then write
to SQL. However if there is no date then do not write anything to SQL
for that particular field. Seems to be working great. Below is a sample
of the asp code

If IsDate(cDateClosed) Then
sql = sql & ", dateClosed='"& cDateClosed & "'"
End If

Erland Sommarskog

2006-01-18, 11:23 am

bjose21 (robert.jose@accenture.com) writes:
> Thanks for your responses. I was able to get around this within the asp
> code by basically stating..if there is a date in the field then write
> to SQL. However if there is no date then do not write anything to SQL
> for that particular field. Seems to be working great. Below is a sample
> of the asp code
>
>
> If IsDate(cDateClosed) Then
> sql = sql & ", dateClosed='"& cDateClosed & "'"
> End If

How do you format the date? If you format it as YYYYMMDD, this will
work. If you format it according to regional settings, it could fail,
or even worse result in an incorrect update like Jan 3rd being stored
as March 1st.

If you use parameterised statements, this can not occur.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Alexander Kuznetsov

2006-01-18, 11:24 am

In such cases it helps to be specific. For instance, instead of
'2005-01-03'
use convert(datetime,'20
05-01-03',120)

Erland Sommarskog

2006-01-19, 3:24 am

Alexander Kuznetsov (AK_TIREDOFSPAM@hotm
ail.COM) writes:
> In such cases it helps to be specific. For instance, instead of
> '2005-01-03'
> use convert(datetime,'20
05-01-03',120)

No, that was not was I was thinking of. I meant:

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

Which was in a previous article of mine in the thread.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Alexander Kuznetsov

2006-01-19, 3:24 am

Hi Erland,

I think I was replying to the original poster, sorry for the confusion.
I agree that what you are suggesting (a parameterized query) is the
best for this situation. My suggestion would be more relevant for the
cases when dynamic SQL is the best choice.

Sponsored Links