Click here to Skip to main content
11,720,545 members (73,871 online)
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 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  
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

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 2,129
1 OriginalGriff 884
2 F-ES Sitecore 640
3 Maciej Los 609
4 Richard MacCutchan 540


Advertise | Privacy | Mobile
Web03 | 2.8.150901.1 | Last Updated 17 Jun 2012
Copyright © CodeProject, 1999-2015
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