Click here to Skip to main content
15,441,078 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Iam using asp.net4.o with c# with sql server 2008


iam having table employee with column as follow
employeeid int
avizoTime datetime

expirationTime datetime

ihave to store null value for date time column

when iam using the below code iam iam getting error can you correct the code

C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.SqlTypes;
/// <summary>
/// Summary description for StatusChange
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class StatusChange : System.Web.Services.WebService {
public StatusChange () {
//Uncomment the following line if using designed components 
//InitializeComponent(); 
}
string StrConnection = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString;
System.Data.SqlTypes.SqlDateTime sqldatenul;
//SqlDateTime sqldatenul;
[WebMethod]
public string packStatus(int empid, DateTime expirationTime, DateTime avizoTime)
{
sqldatenul = SqlDateTime.Null;
string strResult = " Status Failed ..";
SqlConnection MyCon = new SqlConnection(StrConnection);

SqlCommand MyCommand = new SqlCommand("ProPackStatus", MyCon);
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.AddWithValue("@employeeid", empid));

if(expirationTime==null)
{
MyCommand.Parameters.AddWithValue("@expirationTime",sqldatenul);
}
else
{
MyCommand.Parameters.AddWithValue("@expirationTime",(expirationTime).ToUniversalTime());
}
if(avizoTime==null)
{
MyCommand.Parameters.AddWithValue("@avizoTime",sqldatenul);
}
else
{
MyCommand.Parameters.AddWithValue("@avizoTime",(avizoTime).ToUniversalTime());
}
try 
{
MyCon.Open();
if (MyCommand.ExecuteNonQuery() != 0)
{
 
strResult = "Status Changed";
}
strResult = "Status Changed";
}
catch(Exception ex)
{
strResult = ex.ToString();
}
MyCon.Close();
return strResult;
}
}
Posted
Updated 22-Apr-16 1:47am
v2
Comments
db7uk 3-Jun-12 16:58pm    
Whats the error? and what line is this occurring?
db7uk 3-Jun-12 17:06pm    
Added to that have you tried passing DBNULL.Value instead of SQLDataTime.Null?
Suresh Shewale 4-Jun-12 0:54am     CRLF
Please specify your error.Also check following points 1.check your field allows null values 2. pass null value as an parameter in C#
Varun Sareen 4-Jun-12 1:09am    
what error is coming?

Chage ur code like this..It will help you

C#
if(avizoTime==null)
{
MyCommand.Parameters.AddWithValue("@avizoTime",DBNull.Value);
}
else
{
MyCommand.Parameters.AddWithValue("@avizoTime",(avizoTime).ToUniversalTime());
}
 
Share this answer
 
v2
Comments
developerit 4-Jun-12 4:44am     CRLF
i have tried below code also it gives me same error no change if(avizoTime==null) { MyCommand.Parameters.AddWithValue("@avizoTime",DBNull.Value); } else { MyCommand.Parameters.AddWithValue("@avizoTime",(avizoTime).ToUniversalTime()); } can you suggest me another idea
when
avizoTime is null
then not needed to add parameter in SQLCommand
in Your Stored Procedure
set
@avizoTime by default null

like

SQL
@avizoTime datetime = null


when you run your query
null value automatically update
 
Share this answer
 
Comments
developerit 4-Jun-12 2:57am     CRLF
i have used both the option 1.@avizoTime by default null MyCommand.Parameters["@expirationTime"].Value = null; 2. DBNULL.Value MyCommand.Parameters.AddWithValue("@expirationTime", DBNull.Value); both are giving the same error System.ArgumentException: Cannot convert to System.DateTime. Parameter name: type ---> System.FormatException: String was not recognized as a valid DateTime. at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, 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() can you please help me to solve this problem
Technoses 5-Jun-12 12:56pm     CRLF
i have already told that not needed to add parameter in SQLCommand then why you add this parameter ???
C#
MyCommand.Parameters.AddWithValue("@expirationTime",SQLDateTime.Null);
 
Share this answer
 
Well this could be a work around. Since the application is throwing an error we need to handle this on application and in DB we need to insert null

so at application side :

C#
if(expirationTime==null)
{
sqldatenul = "January 1, 1753 "; // check the format of string - this is the smallest date in sql server and anything's expiration date will be greater than this

MyCommand.Parameters.AddWithValue("@expirationTime",sqldatenul);
}
else
{
MyCommand.Parameters.AddWithValue("@expirationTime",(expirationTime).ToUniversalTime());
}


And in Stroe Proc :
keep this check before insertion

SQL
if @expirationTime = 'January 1, 1753 '
begin
 @expirationTime = null
end



hope this can work as work around.
 
Share this answer
 
in your store procedure set parameter value null and when date data is null than don't pass it from your code than null value is inserted in your table and check your table structure that you define on these column tick on null.
 
Share this answer
 

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