locked
how to store null value or Constant value in datetime column in sql server 2008 using asp.net with c# RRS feed

  • Question

  • User1467013749 posted

    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  ie either i have to store some constant value or null value.

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

    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
    ====
     
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;

     Collapse | Copy Code
    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() .

     

    Tuesday, February 5, 2013 6:09 AM

All replies

  • User-1923420989 posted

    If you don't have the actual datetime value , send datetime.minvalue() .

    Tuesday, February 5, 2013 7:25 AM
  • User1467013749 posted

      I Tried for this option

    MyCommand.Parameters.AddWithValue("@startdate", DateTime .MinValue);

    MyCommand.Parameters.AddWithValue("@enddate", DateTime .MinValue);

    still same error.

    Wednesday, February 6, 2013 3:17 AM
  • User-1441883313 posted

    check this link

    http://blogs.msdn.com/b/jaskis/archive/2008/07/09/insert-null-value-into-datetime-column-in-sql-server-from-aspx-application.aspx

    Wednesday, February 6, 2013 6:13 AM
  • User1467013749 posted

    I  tried  this   method also but it gives me error

    please correct me where iam gone wrong

     

    [

    WebMethod

    ]

    public string packStatus1(string packCode, string phoneNo, DateTime startdate, DateTime

    enddate)

    {

    string strResult1 = " Status Failed .."

    ;

    SqlConnection MyCon1 = new SqlConnection

    (StrConnection);

    SqlCommand cmd1 = new SqlCommand("insert into Test(packcode,phone,startdate,enddate)values(@packcode,@phone,@startdate,@enddate)"

    , MyCon1);

    cmd1.Parameters.Add(

    "@packcode",SqlDbType

    .VarChar);

    cmd1.Parameters.Add(

    "@phone", SqlDbType

    .VarChar);

    cmd1.Parameters.Add(

    "@startdate", SqlDbType

    .DateTime);

    cmd1.Parameters.Add(

    "@enddate", SqlDbType

    .DateTime);

    cmd1.Parameters[

    "@packcode"

    ].Value = packCode;

    cmd1.Parameters[

    "@phone"

    ].Value = phoneNo;

    // System.Data.SqlTypes.SqlDateTime getDate;

    //set DateTime null

    //getDate = SqlDateTime.Null;

    System.Data.SqlTypes.

    SqlDateTime

    getDate;

    getDate =

    SqlDateTime

    .Null;

    if (startdate != null

    )

    {

    cmd1.Parameters[

    "@startdate"

    ].Value = (startdate).ToUniversalTime();

    }

    else

    {

     

    cmd1.Parameters[

    "@startdate"

    ].Value = getDate;

    }

    if (enddate != null

    )

    {

    cmd1.Parameters[

    "@enddate"

    ].Value = (enddate).ToUniversalTime();

    }

    else

    {

     

    cmd1.Parameters[

    "@enddate"

    ].Value = getDate;

    }

    try

    {

    MyCon1.Open();

     

    if

    (cmd1.ExecuteNonQuery() != 0)

    {

    strResult1 =

    "Status Changed"

    ;

     

    }

    strResult1 =

    "Status Changed"

    ;

     

     

     

    }

    catch (Exception

    ex)

    {

    strResult1 = ex.ToString();

    }

    MyCon1.Close();

    return

    strResult1;

    }

    Sunday, February 10, 2013 5:54 AM