How to pass null value to a column in sql server table through classic asp

Question

0.00/5 (No votes)

See more:

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 asp.net. In asp.net 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....

Thanks...

Posted 21-May-12 3:48am

Venkat_C6

Updated 21-May-12 3:49am

v2

Add a Solution

Comments

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...

3 solutions

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"
else
str_Val=column_value
end if

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

Hope this works.

Regards,
AP

Posted 21-May-12 3:57am

Arunprasath Natarajan

Comments

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

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.

Posted 22-May-12 11:26am

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Pluto is a Planet · Accepted Answer · 2013-01-31T08:46:00

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):

VB

<!--#include virtual="/adovbs.inc"-->
<%
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"))
  else
    values = Array(Request.Form("eventName"), Null)
end if
Dim item
Set item = Server.CreateObject("ADODB.RecordSet")
item.open "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 >.>)...
http://forums.devx.com/showthread.php?45891-Insert-Empty-Date[^]