Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL-Server
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 1: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
0 OriginalGriff 406
1 Marcin Kozub 225
2 Sergey Alexandrovich Kryukov 205
3 Raul Iloc 170
4 Maciej Los 164
0 OriginalGriff 8,289
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,624
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100