Click here to Skip to main content
12,398,225 members (57,075 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# ASP.NET C#4.0
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

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 3-Jun-12 10:13am
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
   
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?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Chage ur code like this..It will help you

if(avizoTime==null)
{
MyCommand.Parameters.AddWithValue("@avizoTime",DBNull.Value);
}
else
{
MyCommand.Parameters.AddWithValue("@avizoTime",(avizoTime).ToUniversalTime());
}
  Permalink  
v2
Comments
developerit 4-Jun-12 4:44am
   
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

when
avizoTime is null
then not needed to add parameter in SQLCommand
in Your Stored Procedure
set
@avizoTime by default null

like

@avizoTime datetime = null

when you run your query
null value automatically update
  Permalink  
Comments
developerit 4-Jun-12 2:57am
   
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
   
i have already told that
not needed to add parameter in SQLCommand
then why you add this parameter ???
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

MyCommand.Parameters.AddWithValue("@expirationTime",SQLDateTime.Null);
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 6

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 :

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

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


hope this can work as work around.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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.
  Permalink  

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 10 Jun 2016
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