Click here to Skip to main content
12,397,773 members (57,695 online)
Rate this:
 
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 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 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 2 Feb 2013
Copyright © CodeProject, 1999-2016
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