how to store a date in database through string?

Question

I want to store a date in my database. I have added two keys values in web.config. I have used keyFinancialYr and keyFinancialQtr in my code behind to store those values in my database in two column as financial year and financial quarter. I can store keyFinancialQtr value in my database. But when i try to store keyFinancialYr value it gives error. like this "An exception of type 'System.Data.EvaluateException' occurred in System.Data.dll but was not handled in user code

Additional information: Cannot convert value '-2018' to Type: System.DateTime."

web.config-

<appSettings>
  <add key="keyFinancialYr" value="2018-01-01" />

  <add key="keyFinancialQtr" value="1" />
</appSettings>

code behind-

    DateTime x = Convert.ToDateTime(WebConfigurationManager.AppSettings["keyFinancialYr"]);
    int y = Convert.ToInt32(WebConfigurationManager.AppSettings["keyFinancialQtr"]);
    using (OleDbConnection excel_con = new OleDbConnection(conString))
    {
        excel_con.Open();
        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
        DataTable dtExcelData = new DataTable();

        //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
        dtExcelData.Columns.AddRange(new DataColumn[9] { new DataColumn("Id", typeof(int)),
              new DataColumn("Banks", typeof(string)),
               new DataColumn("Crop Loan", typeof(int)),
                new DataColumn("Water Resources", typeof(decimal)),
                 new DataColumn("Farm Mechanisation", typeof(int)),
                  new DataColumn("Plantation & Horticulture", typeof(decimal)),
            new DataColumn("Forestry & Wasteland Dev.", typeof(int)),
            new DataColumn("Financial_Quarter", typeof(int),y.ToString()),
            new DataColumn("Financial_yr",typeof(DateTime),x.ToShortDateString())
             });

You need to include the error otherwise nobody will have a clue. It's like phoning a garage and saying "there is something wrong with my car" — Peter Morris
– Peter Morris, Commented May 25, 2017 at 9:22
An exception of type 'System.Data.EvaluateException' occurred in System.Data.dll but was not handled in user code Additional information: Cannot convert value '-2018' to Type: System.DateTime. — HAPPYsukh
– HAPPYsukh, Commented May 25, 2017 at 9:26
can you please edit the question again and post the exception message that you are getting while storing the keyFinancialYr — Charan Ghate
– Charan Ghate, Commented May 25, 2017 at 9:26
The third parameter of the DataColumn constructor is the expression to be used to calculate the value of the column. It is not the value of the column (if this concept has any sense) — Steve
– Steve, Commented May 25, 2017 at 9:28

GarethD · Accepted Answer · 2017-05-25 09:58:14Z

1

Since the 3rd argument of the DataColumn Constructor is an expression, rather than interpreting your string as a date, it taking it as a formula, i.e.

1 - 1 - 2018 = -2018

And -2018 is not a valid date. You need to add single quotes either side of your date to make sure it is interpreted a literal:

new DataColumn("Financial_yr", typeof(DateTime), "'" + x.ToShortDateString() + "'")

An alternative would be to use the DefaultValue Property.

new DataColumn("Financial_yr", typeof(DateTime)) { DefaultValue = x}

edited May 25, 2017 at 9:58

answered May 25, 2017 at 9:51

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Panagiotis Kanavos Over a year ago

I'd guess the OP wants to hard-code the date colum, not set a default. Simply quoting the original string should work in this case

Chetan · Accepted Answer · 2017-05-25 10:04:43Z

@HAPPYsukh,

The constructor of DataColumn you are using expects a expression as third parameter. When you pass x.ToShortDateString() to it, it will result in 01-01-2018 which results in "-2018" as the it becomes expression and evaluates as math formula. And "-2018" is surely not a valid dateTime value.

Same applies to Financial_Quarter column too.

If you want to set x as a default value for the column Financial_yr you should set it once the column is created.

You can set DefaultValue properties of both the columns as following while adding them to the columns collection of the table.

var financialQtrColumn = new DataColumn("Financial_Quarter", typeof(int)) { DefaultValue = y };
var financialYearColumn = new DataColumn("Financial_yr", typeof(DateTime)) { DefaultValue = x };
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(
    new DataColumn[9] { new DataColumn("Id", typeof(int)),
    new DataColumn("Banks", typeof(string)),
    new DataColumn("Crop Loan", typeof(int)),
    new DataColumn("Water Resources", typeof(decimal)),
    new DataColumn("Farm Mechanisation", typeof(int)),
    new DataColumn("Plantation & Horticulture", typeof(decimal)),
    new DataColumn("Forestry & Wasteland Dev.", typeof(int)),
    financialQtrColumn,
    financialYearColumn
});

I have just posted the lines of code which you need to change.

This should resolve your issue.

Collectives™ on Stack Overflow

how to store a date in database through string?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related