How to check blank value from datatable and insert in a Stored Procedure

Question

5.00/5 (2 votes)

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:-

C#

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

Mayank Topiwala

Updated 10-Jan-13 23:09pm

v2

Add a Solution

Comments

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 ?

4 solutions

Solution 4

Hi,

Set parameter property

C#

boolIsNullable=true

when creating new sqlparameter.

Posted 11-Jan-13 0:24am

Aditya Magotra

Solution 3

try this:

C#

DateTime? dt = IIF((your_datetime_field == NULL)? NULL: Conver.ToDateTime(your_datetime_field))

Posted 10-Jan-13 19:50pm

prashant patil 4987

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tharaka MTR · Accepted Answer · 2013-01-10T19:45:00

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)

C#

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.

Posted 10-Jan-13 19:45pm

Tharaka MTR

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"])));

Subrata.In · Accepted Answer · 2013-01-10T19:45:00

Solution 2

C#

If(Field[""].ToString()!="")Value=Conver.ToDateTime(Field[""])

Posted 10-Jan-13 19:45pm

Subrata.In

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