Click here to Skip to main content
13,089,386 members (58,347 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hello There

I have a C# application in which I have to create a stored procedure for Inserting records.

Currently I am using below function:-
cmd.Parameters.Add(new SqlParameter("PO_Date", Convert.ToDateTime(Convert.ToString(mDT_PoHead.Rows[0]["PO_Date"]) == "" ? null : Convert.ToString(mDT_PoHead.Rows[0]["PO_Date"]))));


So if the datatable field has a blank value then NULL will be passed else actual value pass.

But at the end error arise "SqlDateTime Overflow" (mDT_PoHead.Rows[0]["PO_Date"] has a blank value.)

Need Suggestion
Best Regards
Posted 10-Jan-13 18:57pm
Updated 10-Jan-13 23:09pm
v2
Comments
Mika Wendelius 11-Jan-13 0:59am
   
The question is a bit unclear. Can you edit the question and post the relevant code fragments.
Vishal.Shimpi144 11-Jan-13 1:10am
   
can you please post your code
Mayank Topiwala 11-Jan-13 4:24am
   
cmd.Parameters.Add(new SqlParameter("PO_Date", Convert.ToDateTime(Convert.ToString(mDT_PoHead.Rows[0]["PO_Date"]) == "" ? null : Convert.ToString(mDT_PoHead.Rows[0]["PO_Date"]))));

is it correct ?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

in here you can have two option,
one way is using nullable type, as below (replace the DATA_TABLE_VALUE from you actual value)

DateTime? value = Convert.IsDBNull([DATA_TABLE_VALUE]) ? null : (DateTime?)[DATA_TABLE_VALUE];


2nd option,
Since DateTime is a value type (like int), a Null value cannot be assigned to it. In this instance you can use DateTime.MinValue or
DateTime.MaxValue
instead.
  Permalink  
Comments
Mayank Topiwala 11-Jan-13 4:29am
   
cmd.Parameters.Add(new SqlParameter("PO_Date", Convert.ToDateTime(Convert.ToString(mDT_PoHead.Rows[0]["PO_Date"]) == "" ? null : Convert.ToString(mDT_PoHead.Rows[0]["PO_Date"]))));

is it correct ?
Tharaka MTR 11-Jan-13 5:01am
   
try this

cmd.Parameters.Add(new SqlParameter("PO_Date", Convert.IsDBNull(mDT_PoHead.Rows[0]["PO_Date"]) ? null : (DateTime?)Convert.ToDateTime(mDT_PoHead.Rows[0]["PO_Date"])));
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

If(Field[""].ToString()!="")Value=Conver.ToDateTime(Field[""])
  Permalink  
Comments
Mayank Topiwala 11-Jan-13 2:54am
   
what else if I don't want to insert default value i.e., '01/01/1900'
_Subrata_ 11-Jan-13 3:30am
   
Fine, don't use default value. Just store Field=Null (Don't use quotation mark) id sql. When retrieving value just check for null, that's all.
Mayank Topiwala 11-Jan-13 3:49am
   
ok and now what if I want to insert into dd/MM/yyyy format
_Subrata_ 11-Jan-13 4:03am
   
In a single word you can't insert date in a specific format. It will manage by server and also regional settings of client system. But you can show date as your require format, before show date just format it.
On C#: use Conver.ToDateTime(Field[""]).ToString("dd/MM/yyyy")
On sql: create user define function and/or using DateName function
Mayank Topiwala 11-Jan-13 4:14am
   
cmd.Parameters.Add(new SqlParameter("PO_Date", Convert.ToDateTime(Convert.ToString(mDT_PoHead.Rows[0]["PO_Date"]) == "" ? null : Convert.ToString(mDT_PoHead.Rows[0]["PO_Date"]))));

is it correct ?
_Subrata_ 11-Jan-13 5:01am
   
Just try:
cmd.Parameters.Add(new SqlParameter("PO_Date", mDT_PoHead.Rows[0]["PO_Date"]));

No require to convert to datetime, as well as there is no checking require for null.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Hi,

Set parameter property
boolIsNullable=true
when creating new sqlparameter.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

try this:
DateTime? dt = IIF((your_datetime_field == NULL)? NULL: Conver.ToDateTime(your_datetime_field))
  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 |
Web02 | 2.8.170813.1 | Last Updated 11 Jan 2013
Copyright © CodeProject, 1999-2017
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