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