Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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
Edited 3-Jun-12 10:16am
v2
Comments
db7uk at 3-Jun-12 16:58pm
   
Whats the error? and what line is this occurring?
db7uk at 3-Jun-12 17:06pm
   
Added to that have you tried passing DBNULL.Value instead of SQLDataTime.Null?
Suresh Shewale at 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 at 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 at 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 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  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

hi,can you help me please where i have insert null value in stored procedure
below is iam using stored procedure
 
Create procedure ProPackStatus(@employeeid int,@expirationTime datetime,@avizoTime datetime )
                as
                begin
                IF EXISTS(select * from  tbl_PackStatus where employeeid=@employeeid)
                update tbl_PackStatus set expirationTime=@expirationTime,avizoTime=@avizoTime where  employeeid=@employeeid
                ELSE insert into  tbl_PackStatus (@employeeid,@expirationTime ,@avizoTime)
end
  Permalink  
Comments
losmac at 17-Jun-12 4:59am
   
This is not an answer! Please, remove it. If you have any question or comments, use "Have a question or comment" button.
developerit at 17-Jun-12 6:48am
   
hi,can you help me please where i can insert null value in stored procedure
 
below is iam using stored procedure
 
Create procedure ProPackStatus(@employeeid int,@expirationTime datetime,@avizoTime datetime )
 
as begin
 
IF EXISTS(select * from tbl_PackStatus where employeeid=@employeeid)
 
update tbl_PackStatus set expirationTime=@expirationTime,avizoTime=@avizoTime where employeeid=@employeeid
 
ELSE insert into tbl_PackStatus (@employeeid,@expirationTime ,@avizoTime)
 
end
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 at 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 at 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  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 CPallini 465
1 Sergey Alexandrovich Kryukov 310
2 George Jonsson 269
3 Gihan Liyanage 255
4 OriginalGriff 245
0 OriginalGriff 4,903
1 CPallini 3,960
2 Sergey Alexandrovich Kryukov 3,474
3 George Jonsson 2,786
4 Gihan Liyanage 2,386


Advertise | Privacy | Mobile
Web03 | 2.8.140905.1 | Last Updated 17 Jun 2012
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