how to pass null datetime into table in sql server.

Question

0.00/5 (No votes)

See more:

This is code behind logic

Quote:
iEmployee.PASSPORT_ISSUEDATE = HR.Dal.Helper.FormatDate(txtPasportIsuDate.Text);
iEmployee.PASSPORT_EXPIREDATE = HR.Dal.Helper.FormatDate(txtPasportExpDate.Text);

below is my data acces method.while In debugging I am getting exception that is, conversion of varchar datatype to a datetime datatype resulted in an out of range value...

Quote:
public QueryResult Add(Model.Employees iObject, Model.EmployeeDependents[] iDependants, Model.EmployeePics iPic)
{
QueryResult iResult = new QueryResult();
DBManager dbManager = null;
string sql = string.Empty;
try
{

sql = @"INSERT INTO dbo.EMPLOYEES(EMP_NO,EMP_INTLS,FIRST_NAME,MIDDLE_NAME,LAST_NAME,FULL_NAME,MGR_NO,CONTRACT_START_DATE,CONTRACT_END_DATE,BRANCH_NO,DEPT_NO,
TITLE_NO,GRADE_NO,EMP_STATUS,DEGREE_ID,NAT_ID,GDR_ID,BIRTH_DATE,LANG_ID,CITY_ID,REGION_ID,RES_ADDRESS,RES_POBOX,RES_STREET,RES_PHONE,MOBILE_NO,EMAIL,
LOGIN_NAME,ACCESS_KEY,TRAN_DATE,IS_DELETED,USER_ID,IP,CHECK_SUM,TIME_STAMP,SYSTEM_ID,SITE_ID,TERMINATION_STATUS_ID,PAN_CARD_NO,EMPLOYEE_TYPE,
INSTITUTE_NAME,BLOOD_GROUP,MARITAL_STATUS,FATHER_NAME,SPOUSE_NAME,PASSPORT_NO,PASSPORT_ISSUEDATE,PASSPORT_EXPIREDATE,VISA_NO,VISA_ISSUEDATE,
VISA_EXPIREDATE,EMIRATES_ID,EMIRATES_ISSUEDATE,EMIRATES_EXPIREDATE,HEALTHCARD_NO,HEALTHISSUEDATE,HEALTHEXPIREDATE,DRIVING_LICENCE_NO,DRIVING_LICENCE_ISSUEDATE,
DRIVING_LICENCE_EXPIRE,EMERGENCY_CONTACTNAME,EMERGENCY_CONTACTNO,EMERGENCY_ADDRESS,EMERGENCY_RELATION,PHYSICALCHALLENGED)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},{12},{13},{14},{15},{16},'{17}',{18},{19},{20},'{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}',0,'{30}','{31}',{32},GETDATE(),'{33}',{34},{35},'{36}','{37}','{38}','{39}','{40}','{41}','{42}','{43}','{44}','{45}','{46}','{47}','{48}','{49}','{50}','{51}','{52}','{53}','{54}','{55}','{56}','{57}','{58}','{59}',{60},'{61}','{62}','{63}')";
sql = string.Format(sql,
Helper.IsNull(iObject.EMP_NO)
, Helper.IsNull(iObject.EMP_INTLS)
, Helper.IsNull(iObject.FIRST_NAME)
, Helper.IsNull(iObject.MIDDLE_NAME)
, Helper.IsNull(iObject.LAST_NAME)
, Helper.IsNull(iObject.FULL_NAME)
, Helper.IsNull(iObject.MGR_NO)
, Helper.NullIf<datetime>(iObject.CONTRACT_START_DATE)
, Helper.NullIf<datetime>(iObject.CONTRACT_END_DATE)
, Helper.IsNull(iObject.BRANCH_NO)
, Helper.IsNull(iObject.DEPT_NO)
, Helper.NullIf<int>(iObject.TITLE_NO)
, Helper.NullIf<int>(iObject.GRADE_NO)
, Helper.NullIf<int>(iObject.EMP_STATUS)
, Helper.NullIf<int>(iObject.DEGREE_ID)
, Helper.NullIf<int>(iObject.NAT_ID)
, Helper.NullIf<int>(iObject.GDR_ID)
, Helper.NullIf<datetime>(iObject.BIRTH_DATE)
, Helper.NullIf<int>(iObject.LANG_ID)
, Helper.NullIf<int>(iObject.CITY_ID)
, Helper.NullIf<int>(iObject.REGION_ID)
, Helper.IsNull(iObject.RES_ADDRESS)
, Helper.IsNull(iObject.RES_POBOX)
, Helper.IsNull(iObject.RES_STREET)
, Helper.IsNull(iObject.RES_PHONE)
, Helper.IsNull(iObject.MOBILE_NO)
, Helper.IsNull(iObject.EMAIL)
, Helper.IsNull(iObject.LOGIN_NAME)
, Helper.IsNull(iObject.ACCESS_KEY)
, Helper.NullIf<datetime>(iObject.TRAN_DATE)
, Helper.IsNull(iObject.USER_ID)
, Helper.IsNull(iObject.IP)
, Helper.IsNull(iObject.CHECK_SUM)
, Helper.NullIf<int>(iObject.SITE_ID)
, Helper.IsNull(iObject.SYSTEM_ID)
, Helper.IsNull<int>(iObject.TERMINATION_STATUS_ID)
, Helper.IsNull(iObject.PAN_CARD_NO)
, Helper.IsNull(iObject.EMPLOYEE_TYPE)
, Helper.IsNull(iObject.INSTITUTE_NAME)
, Helper.IsNull(iObject.BLOOD_GROUP)
, Helper.IsNull(iObject.MARITAL_STATUS)
, Helper.IsNull(iObject.FATHER_NAME)
, Helper.IsNull(iObject.SPOUSE_NAME)
, Helper.IsNull(iObject.PASSPORT_NO)
, Helper.NullIf<datetime>(iObject.PASSPORT_ISSUEDATE)
, Helper.NullIf<datetime>(iObject.PASSPORT_EXPIREDATE)
, Helper.IsNull(iObject.VISA_NO)
, Helper.NullIf<datetime>(iObject.VISA_ISSUEDATE)
, Helper.NullIf<datetime>(iObject.VISA_EXPIREDATE)
, Helper.IsNull(iObject.EMIRATES_ID)
, Helper.NullIf<datetime>(iObject.EMIRATES_ISSUEDATE)
, Helper.NullIf<datetime>(iObject.EMIRATES_EXPIREDATE)
, Helper.IsNull(iObject.HEALTHCARD_NO)
, Helper.NullIf<datetime>(iObject.HEALTHISSUEDATE)
, Helper.NullIf<datetime>(iObject.HEALTHEXPIREDATE)
, Helper.IsNull(iObject.DRIVING_LICENCE_NO)
, Helper.NullIf<datetime>(iObject.DRIVING_LICENCE_ISSUEDATE)
, Helper.NullIf<datetime>(iObject.DRIVING_LICENCE_EXPIRE)
, Helper.IsNull(iObject.EMERGENCY_CONTACTNAME)
, Helper.NullIf<int>(iObject.EMERGENCY_CONTACTNO)
, Helper.IsNull(iObject.EMERGENCY_ADDRESS)
, Helper.IsNull(iObject.EMERGENCY_RELATION)
,Helper.IsNull(iObject.PHYSICALCHALLENGED));

//
// sql = sql.Replace("''", "NULL").Replace("'NULL'", "NULL").Replace("NNULL", "NULL");
//
switch (Constants.DBEngine)
{
case (int)DataProvider.SqlServer:
dbManager = new DBManager();
dbManager.ProviderType = DataProvider.SqlServer;
dbManager.ConnectionString = Constants.ConnectionString;
break;
}
dbManager.Open();
dbManager.BeginTransaction();

Posted 4-Aug-15 22:40pm

Meer Wajeed Ali

Add a Solution

Comments

DamithSL 5-Aug-15 5:01am

can you update the question with method codes of HR.Dal.Helper.FormatDate and Helper.NullIf?

Meer Wajeed Ali 5-Aug-15 5:43am

Hi I updated my thread kindly check out and let me some possibilities...thanks
public static DateTime FormatDate(string date)
{
try
{
IFormatProvider provider = new System.Globalization.CultureInfo("en-GB");
return Convert.ToDateTime(date, provider);
}
catch
{
string format = "ddd dd MMM h:mm tt yyyy";

return DateTime.ParseExact(date, format, System.Globalization.CultureInfo.InvariantCulture);
}
}

Sreekanth Mothukuru 5-Aug-15 5:28am

Update your method "FormatDate" in such a way that it returns null value when there is an empty string (""). Also make sure your model (iEmployee.PASSPORT_ISSUEDATE) can hold null values for date time fields.

Also, add dynamic values to your query through parameter objects for each item. Query is having SQL injection threat.

2 solutions

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)

OriginalGriff · Answer 1 · 2015-08-04T23:23:00

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

And when you have a null value to pass through, replace it with DBNull.Value as the parameter.

Suket shah · Answer 2 · 2015-08-05T02:09:00

Solution 2

hey You can pass your model filed that is PASSPORT_ISSUEDATE and PASSPORT_EXPIREDATE as a nullble or you can also pass value that DBNull.Value

Posted 5-Aug-15 2:09am

Suket shah