How to Store Null Values in sql server 2008 in datetime field using asp.net with c#

Question

0.00/5 (No votes)

See more:

hi,
Iam Using asp.net with c#, Ms Sql Server 2008

when iam passing the date value then its working fine if iam not passing the value its giving me error can you help me where i have to make changes

below is my table, stored procedure, code as follows
Table Test
==========
packcode varchar(50)
phone varchar(10)
startdate datetime
endtime datetime

Procedure testing
==================

SQL

CREATE PROCEDURE testing(@packcode varchar(50),@phone varchar(10),@startdate datetime,@enddate datetime)

as
                begin
                IF EXISTS(select * from  Test where packcode=@packcode)

                update Test set phone=@phone,startdate=@startdate,enddate=@enddate where packcode=@packcode

                else insert into Test(packcode,phone,startdate,enddate)values(@packcode ,@phone ,@startdate ,@enddate )
                end

code
====

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

C#

public class status : System.Web.Services.WebService {
    string StrConnection = ConfigurationManager.ConnectionStrings["Application"].ConnectionString;

[WebMethod]
public string HelloWorld() {
return "Hello World";
}

[WebMethod]
public string packStatus(string packCode, string phoneNo, DateTime startdate, DateTime enddate)
{

//sqldatenul = DBNull.Value;
string strResult = " Status Failed ..";
SqlConnection MyCon = new SqlConnection(StrConnection);
SqlCommand MyCommand = new SqlCommand("testing", MyCon);
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.AddWithValue("@packcode", Convert.ToString(packCode));
MyCommand.Parameters.AddWithValue("@phone", Convert.ToString(phoneNo));
if (startdate == null)
{

MyCommand.Parameters.Add("@startdate", SqlDbType.DateTime).Value = SqlDateTime.Null;

}
else {

MyCommand.Parameters.AddWithValue("@startdate", (startdate).ToUniversalTime());

}

if (enddate == null)
{

MyCommand.Parameters.Add("@enddate", SqlDbType.DateTime).Value = SqlDateTime.Null;

}
else
{

MyCommand.Parameters.AddWithValue("@enddate", (enddate).ToUniversalTime());

}

try
{

MyCon.Open();
if (MyCommand.ExecuteNonQuery() != 0)
{

strResult = "Status Changed";

}
strResult = "Status Changed";

}
catch (Exception ex)
{

strResult = ex.ToString();

}
MyCon.Close();
return strResult;

}

}

when iam not passing value to this webservice it is giving me error

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

Posted 2-Feb-13 0:04am

developerit

Add a Solution

1 solution

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)

Naveen.Sanagasetti · Answer 1 · 2013-02-02T00:12:00

Solution 1

first of all in your SP itself you assign the parameters is accepted to null
ex:

SQL

@startdate datetime=null,

and then in your web service it self whenever you add that parameter at that time you can check the condition.

C#

if(startDate!="")
    MyCommand.Parameters.AddWithValue("@startdate", startdate);

and then in page wise you check the condition

C#

if(startDate!=null)
    --- your insert query with the startdate value..
else
    --- your inserting query with startdate is null...

Posted 2-Feb-13 0:12am

Naveen.Sanagasetti

Comments

developerit 2-Feb-13 7:29am

ihave changd the procedure to this but still same problem

ALTER PROCEDURE testing(@packcode varchar(50),@phone varchar(10),@startdate datetime=null,@enddate datetime=null)

as
begin
IF EXISTS(select * from Test where packcode=@packcode)

update Test set phone=@phone,startdate=@startdate,enddate=@enddate where packcode=@packcode

else insert into Test(packcode,phone,startdate,enddate)values(@packcode ,@phone ,@startdate ,@enddate )
end
and i changed the code also
and iam passing this date format
2013-02-02T08:27:24.994+03:00
when iam not passing it is giving me error

developerit 4-Feb-13 6:28am

Please any body help me for this issue.....