Click here to Skip to main content
15,396,239 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

1 solution

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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900