Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
==================
 
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;
 
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

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

first of all in your SP itself you assign the parameters is accepted to null
ex:
 
@startdate datetime=null,
 
and then in your web service it self whenever you add that parameter at that time you can check the condition.
 
if(startDate!="")
    MyCommand.Parameters.AddWithValue("@startdate", startdate);
 
 
and then in page wise you check the condition
 
 
if(startDate!=null)
    --- your insert query with the startdate value..
else
    --- your inserting query with startdate is null...
  Permalink  
Comments
developerit at 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 at 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)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Guruprasad.K.Basavaraju 490
1 Sergey Alexandrovich Kryukov 316
2 Shai Vashdi 314
3 OriginalGriff 190
4 praveen_07 115
0 Sergey Alexandrovich Kryukov 9,185
1 OriginalGriff 5,315
2 Peter Leow 4,040
3 Maciej Los 3,535
4 Abhinav S 3,308


Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 2 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid