how to store null value in a date colum in sql server 2008 using asp.net c#.net

Question

0.00/5 (No votes)

See more:

Iam using asp.net4.o with c# with sql server 2008

iam having table employee with column as follow
employeeid int
avizoTime datetime

expirationTime datetime

ihave to store null value for date time column

when iam using the below code iam iam getting error can you correct the code

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.SqlTypes;
/// <summary>
/// Summary description for StatusChange
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class StatusChange : System.Web.Services.WebService {
public StatusChange () {
//Uncomment the following line if using designed components 
//InitializeComponent(); 
}
string StrConnection = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString;
System.Data.SqlTypes.SqlDateTime sqldatenul;
//SqlDateTime sqldatenul;
[WebMethod]
public string packStatus(int empid, DateTime expirationTime, DateTime avizoTime)
{
sqldatenul = SqlDateTime.Null;
string strResult = " Status Failed ..";
SqlConnection MyCon = new SqlConnection(StrConnection);

SqlCommand MyCommand = new SqlCommand("ProPackStatus", MyCon);
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.AddWithValue("@employeeid", empid));

if(expirationTime==null)
{
MyCommand.Parameters.AddWithValue("@expirationTime",sqldatenul);
}
else
{
MyCommand.Parameters.AddWithValue("@expirationTime",(expirationTime).ToUniversalTime());
}
if(avizoTime==null)
{
MyCommand.Parameters.AddWithValue("@avizoTime",sqldatenul);
}
else
{
MyCommand.Parameters.AddWithValue("@avizoTime",(avizoTime).ToUniversalTime());
}
try 
{
MyCon.Open();
if (MyCommand.ExecuteNonQuery() != 0)
{
　
strResult = "Status Changed";
}
strResult = "Status Changed";
}
catch(Exception ex)
{
strResult = ex.ToString();
}
MyCon.Close();
return strResult;
}
}

Posted 3-Jun-12 10:13am

developerit

Updated 22-Apr-16 1:47am

Manas Bhardwaj

v2

Add a Solution

Comments

db7uk 3-Jun-12 16:58pm

Whats the error? and what line is this occurring?

db7uk 3-Jun-12 17:06pm

Added to that have you tried passing DBNULL.Value instead of SQLDataTime.Null?

Suresh Shewale 4-Jun-12 0:54am

Please specify your error.Also check following points
1.check your field allows null values
2. pass null value as an parameter in C#

Varun Sareen 4-Jun-12 1:09am

what error is coming?

5 solutions

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)

Nikhil Dayalpawar · Answer 1 · 2012-06-03T20:53:00

Solution 2

Chage ur code like this..It will help you

C#

if(avizoTime==null)
{
MyCommand.Parameters.AddWithValue("@avizoTime",DBNull.Value);
}
else
{
MyCommand.Parameters.AddWithValue("@avizoTime",(avizoTime).ToUniversalTime());
}

Posted 3-Jun-12 20:53pm

Nikhil Dayalpawar

Updated 3-Jun-12 20:54pm

v2

Comments

developerit 4-Jun-12 4:44am

i have tried below code also it gives me same error no change

if(avizoTime==null)
{
MyCommand.Parameters.AddWithValue("@avizoTime",DBNull.Value);
}
else
{
MyCommand.Parameters.AddWithValue("@avizoTime",(avizoTime).ToUniversalTime());
}

can you suggest me another idea

Technoses · Answer 2 · 2012-06-03T19:23:00

Solution 1

when
avizoTime is null
then not needed to add parameter in SQLCommand
in Your Stored Procedure
set
@avizoTime by default null

like

SQL

@avizoTime datetime = null

when you run your query
null value automatically update

Posted 3-Jun-12 19:23pm

Technoses

Comments

developerit 4-Jun-12 2:57am

i have used both the option
1.@avizoTime by default null
MyCommand.Parameters["@expirationTime"].Value = null;

2. DBNULL.Value
MyCommand.Parameters.AddWithValue("@expirationTime", DBNull.Value);

both are giving the same error

System.ArgumentException: Cannot convert to System.DateTime.
Parameter name: type ---> System.FormatException: String was not recognized as a valid DateTime.
at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
at System.Convert.ToDateTime(String value, IFormatProvider provider)
at System.String.System.IConvertible.ToDateTime(IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Web.Services.Protocols.ScalarFormatter.FromString(String value, Type type)
--- End of inner exception stack trace ---
at System.Web.Services.Protocols.ScalarFormatter.FromString(String value, Type type)
at System.Web.Services.Protocols.ValueCollectionParameterReader.Read(NameValueCollection collection)
at System.Web.Services.Protocols.HtmlFormParameterReader.Read(HttpRequest request)
at System.Web.Services.Protocols.HttpServerProtocol.ReadParameters()
at System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest()

can you please help me to solve this problem

Technoses 5-Jun-12 12:56pm

i have already told that
not needed to add parameter in SQLCommand
then why you add this parameter ???

engr · Answer 3 · 2012-06-17T04:53:00

Solution 5

C#

MyCommand.Parameters.AddWithValue("@expirationTime",SQLDateTime.Null);

Posted 17-Jun-12 4:53am

engr

sinhasourabh · Answer 4 · 2012-06-17T06:24:00

Well this could be a work around. Since the application is throwing an error we need to handle this on application and in DB we need to insert null

so at application side :

C#

if(expirationTime==null)
{
sqldatenul = "January 1, 1753 "; // check the format of string - this is the smallest date in sql server and anything's expiration date will be greater than this

MyCommand.Parameters.AddWithValue("@expirationTime",sqldatenul);
}
else
{
MyCommand.Parameters.AddWithValue("@expirationTime",(expirationTime).ToUniversalTime());
}

And in Stroe Proc :
keep this check before insertion

SQL

if @expirationTime = 'January 1, 1753 '
begin
 @expirationTime = null
end

hope this can work as work around.

Prosan · Answer 5 · 2012-06-04T01:26:00

Solution 3

in your store procedure set parameter value null and when date data is null than don't pass it from your code than null value is inserted in your table and check your table structure that you define on these column tick on null.

Posted 4-Jun-12 1:26am

Prosan

how to store null value in a date colum in sql server 2008 using asp.net c#.net

5 solutions

Solution 2

Solution 1

Solution 5

Solution 6

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us