Click here to Skip to main content
12,831,626 members (29,363 online)
Rate this:
Please Sign up or sign in to vote.
See more: ASP
Hi everybody...

I have a column in my table with datatype as datetime. If Im passing empty string from my asp application, the column has been taking the default value as 1/1/1900. But if I pass empty value means it should store null value. I know how to pass null value to a column in In we can pass it as DBNull.value. But Im not knowing how to pass null value through my classic asp page. Can anybody please help....

Posted 21-May-12 4:48am
Updated 21-May-12 4:49am
Technoses 23-May-12 1:40am
what code you have write for this..
you use StoredProcedure to save values
or you directr using Inline Query fot this...
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

This is a pretty late response, but I had this very problem and neither of these answers were right. DBNull doesn't exist in Classic ASP, and there has to be a way to set SQL's DateTime fields to null (because if you don't set them from the beginning then they're already null).

I spent about half an hour searching for this going through pages of Google results, and finally found it! I wanted to post it here for future reference because I think it's pretty relevant.

First, you can use an SQL query to change a field to null, and you just use the string "null" in that case.

However I prefer using ASP's built-in ADODB.Recordset object to make an SQL query to find the records, and its AddNew/Update/Delete method to modify the database. I'm a die hard JS fan, so of course I believe eval is evil, and doing the same thing with SQL queries feels wrong to me. So I needed a different way to set records to null.

And I found the solution! It's as simple as using the variable "Null" in VBScript. Notepad++ didn't highlight it so I thought it wouldn't work, plus I thought "Nothing" was VBScript's version of null, which (Nothing) didn't work. You don't want to cast Null either, just pass in Null.

Here's what my code looks like (note I'm writing this to learn, so I didn't create a permission system or anything):

<!--#include virtual="/"-->
Dim id
id = Request("ID")
if id <> "" and Request.Form("eventName") <> "" then
  Dim fields
  Dim values
  fields = Array("eventName", "eventDate")
  if Request.Form("eventDate") <> "" then
    values = Array(Request.Form("eventName"), CDate(Request.Form("eventDate"))
    values = Array(Request.Form("eventName"), Null)
end if
Dim item
Set item = Server.CreateObject("ADODB.RecordSet") "select top 1 * from calendar where id = " & id, "DSN=Calendar.dsn", , adLockOptimistic, adCmdText
if not item.EOF then
  item.Update fields, values
end if

Here's where it's from (even though the asker decided to go with the SQL query update option >.>)...[^]
SoMad 31-Jan-13 16:39pm
I often yell at those that wake up an old question like this, but your reason for answering is well explained. This deserves to be rewarded.

Soren Madsen
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Dear Friend,

I think so we can not pass null value to the datatype field datetime.
Better from application side or sql end,
1st check
Application End
if year(column_value)='1900' then
str_Val="No date found"
end if

Select case when year(column_value)='1900' then 'No date found' else column_value end

Hope this works.

Venkat_C6 21-May-12 10:05am
Thank you so much...
Venkat_C6 21-May-12 10:30am
But If we pass string value also it is showing error..
Arunprasath Natarajan 2-Feb-13 1:48am
Provide me your code
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

The DBNull value is used when you store the value to the database. So you can use it as long as you decide when to use that value n your code based on the input it the asp page. For example if you have a check box before a date field indicating if there is a value or not, that check box could be used as a criteria in your code whether to use DBNull.Value or the value in the field.

Also I'd remove the default from the column since if you are going to use null values in the database a default of 1/1/1900 would make things more complicated if it also means that there is no date value in the database.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170326.1 | Last Updated 31 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100