Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL-Server
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 19:57pm
Edited 11-Jan-13 0:09am
v2
Comments
Mika Wendelius at 11-Jan-13 0:59am
   
The question is a bit unclear. Can you edit the question and post the relevant code fragments.
Vishal.Shimpi144 at 11-Jan-13 1:10am
   
can you please post your code
Mayank Topiwala at 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 at 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 at 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 at 11-Jan-13 2:54am
   
what else if I don't want to insert default value i.e., '01/01/1900'
_Subrata_ at 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 at 11-Jan-13 3:49am
   
ok and now what if I want to insert into dd/MM/yyyy format
_Subrata_ at 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 at 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_ at 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)



Advertise | Privacy | Mobile
Web03 | 2.8.150302.1 | Last Updated 11 Jan 2013
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