locked
Adding System Date in Database RRS feed

  • Question

  • User803118023 posted

    actually i have 2 Similar questions:

    1) i have a databse file on microsoft access. and i want to add system date in my databse using c#, what is the method to do this? 

    2) i m new in asp.net , i m making a demo application and storing a record of a person. there is a column of Date of birth. i used "datetime" data type for this column, because it have check on dates like i can't enter 29-02-2011, since its an invalid date. but problem with that it contain time with it, which should not be mentioned in date of birth column. plz guide me which  data type shoul i use?

    or if i simply use text data type and then add "regular expression" in textbox, then what regular expression should be used to have a check on invalid dates. remember i m not asking about the format of date, i m asking about invalid and valid dates.

    Saturday, January 29, 2011 6:49 AM

Answers

  • User2019981500 posted

    Hi,

    just Insert

    DateTime.Now.Date if you want only date portion


    Regards


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 29, 2011 2:07 PM
  • User2019981500 posted

    Hi,


    Fist of all you know that Users sucks every way and you let them suck so easily.why? Just give him dropdownlist with all correct dates.Try to replace your textbox(through which user inputs date) with dropdownlist.

    Paste Blew code there

    [Code snippet from Asp.net Tutorial]


      <form id="form1" runat="server">
    <div>
    <asp:DropDownList ID="DropDownList1" runat="server" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="True">
    </asp:DropDownList>
    <asp:DropDownList ID="DropDownList2" runat="server" OnSelectedIndexChanged="DropDownList2_SelectedIndexChanged" AutoPostBack="True">
    </asp:DropDownList>
    <asp:DropDownList ID="DropDownList3" runat="server" AutoPostBack="True">
    </asp:DropDownList><br />
    <asp:Label ID="Label1" runat="server"></asp:Label><br />
    
    </div>
    </form> 


    Code behind


    using System;
    using System.Data;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    
    public partial class Default3 : System.Web.UI.Page
    {
        int year, month;
        protected void Page_Load(object sender, EventArgs e)
        {
            DateTime tnow = DateTime.Now;
            ArrayList AlYear = new ArrayList();
            int i;
            for (i = 2002; i <= 2010; i++)
                AlYear.Add(i);
            ArrayList AlMonth = new ArrayList();
            for (i = 1; i <= 12; i++)
                AlMonth.Add(i);
            if (!this.IsPostBack)
            {
                DropDownList1.DataSource = AlYear;
                DropDownList1.DataBind();
                DropDownList1.SelectedValue = tnow.Year.ToString();
                DropDownList2.DataSource = AlMonth;
                DropDownList2.DataBind();
                DropDownList2.SelectedValue = tnow.Month.ToString();
                year = Int32.Parse(DropDownList1.SelectedValue);
                month = Int32.Parse(DropDownList2.SelectedValue);
                BindDays(year, month);
                DropDownList3.SelectedValue = tnow.Day.ToString();
            }
            Label1.Text = "You select date:" + DropDownList1.SelectedValue + "year" + DropDownList2.SelectedValue + "month" + DropDownList3.SelectedValue;
        }
    
        //judge leap year
        private bool CheckLeap(int year)
        {
            if ((year % 4 == 0) && (year % 100 != 0) || (year % 400 == 0))
                return true;
            else return false;
        }
    
        //binding every month day
        private void BindDays(int year, int month)
        {
            int i;
            ArrayList AlDay = new ArrayList();
    
            switch (month)
            {
                case 1:
                case 3:
                case 5:
                case 7:
                case 8:
                case 10:
                case 12:
                    for (i = 1; i <= 31; i++)
                        AlDay.Add(i);
                    break;
                case 2:
                    if (CheckLeap(year))
                    {
                        for (i = 1; i <= 29; i++)
                            AlDay.Add(i);
                    }
                    else
                    {
                        for (i = 1; i <= 28; i++)
                            AlDay.Add(i);
                    }
                    break;
                case 4:
                case 6:
                case 9:
                case 11:
                    for (i = 1; i <= 30; i++)
                        AlDay.Add(i);
                    break;
            }
            DropDownList3.DataSource = AlDay;
            DropDownList3.DataBind();
        }
        //select year
        public void DropDownList1_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            year = Int32.Parse(DropDownList1.SelectedValue);
            month = Int32.Parse(DropDownList2.SelectedValue);
            BindDays(year, month);
        }
    
        //select month
        public void DropDownList2_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            year = Int32.Parse(DropDownList1.SelectedValue);
            month = Int32.Parse(DropDownList2.SelectedValue);
            BindDays(year, month);
        }
    }



    Regards

    shabir

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 7, 2011 6:41 AM
  • User2019981500 posted

    Hi Hans ,there are Lots of issues what he has mentioned to private mail, and providing simple format  using dropdown list is acceptal and best for the User friendly interface.Right? free from injection as well as user doesn't need to type anything.Rest Coding part is just functions which he can reuse anywhere.

    Hans, i don't understand how can we create an object of type ValidationCompareOperator from its string representation 'LessThenEqual' for the 'Operator' property.


    Regards

    shabir

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 7, 2011 7:34 AM
  • User-1199946673 posted

    regarding validation controls, i try to get some regular expression which can check that 29th Feb is a valid date or not, but i could't find any regular expression like this.
     

    When using validation controls, you don't need a regular expression to validate a date. Like I said, you can use a comparevalidator to check whether or not a control (like a textbox) holds a valid date (using the DataTypeCheck) and even if a date is smaller/greater than a given date. If you want a date to be in a certain range, use a rangevalidator instead.

    When using 3 comboboxes, you can't use these validators. but you need to use a customvalidator and write you're own logic in code behind, you can use the code already provided and optionally you can write something similar in javscript so that the customvalidator will also work on client side.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 12, 2011 6:07 AM
  • User1335898253 posted

    If you want to export data from MS access, I suggest to use a C# data export component Spire.DataExport, I use it for a long time, quit good. Now it provides a free version. You may try it. It supports importing/exporting to XLS, PDF, Word and so on. I think it may help you.
    More information:
    http://www.e-iceblue.com/Introduce/free-dataexport-component.html
    If you have questions you may send an email to me. My email address is: tom.lutao@e-iceblue.com

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 14, 2011 9:29 PM

All replies

  • User2019981500 posted

    But you have asked only one question.Anyways attach your access db to you App_Code Folder then write below function to insert data,if you want more fields you can add fields to same function.

    create table with date field

    Just

    public bool InsertDateInMsAccessDatabase(string userdob)
            {
                OdbcConnection objConn = new OdbcConnection();
                OdbcCommand Objcmd;
                objConn.ConnectionString = ConfigurationManager.ConnectionStrings["DBMyFaithConStr"].ConnectionString;
                string StrQry = "INSERT INTO YourTable(yourdatefield)VALUES(?)";
                string lblmessage;
                using (objConn)
                {
                    try
                    {
                        Objcmd = new OdbcCommand(StrQry, objConn);                    
                        Objcmd.Parameters.AddWithValue("", Yourdatefield);
                        objConn.Open();
                        Objcmd.ExecuteNonQuery();
                        lblmessage = "Account successfully Created";
                    }
                    catch (OdbcException ex)
                    {
                        lblmessage = ex.Message;
                    }
                    finally
                    {
                        objConn.Close();
                    }
                }
                return true;
            }

    Add Connection to WebConfig like

    <connectionStrings>
    		<add name="DBMyFaithConStr" connectionString="Driver={Microsoft Access Driver (*.mdb)};Dbq=E:\FaithForum\App_Data\DBMyFaith.mdb"/>		
    	</connectionStrings>


    Regards

    shabir

    Saturday, January 29, 2011 10:32 AM
  • User-1199946673 posted

    Anyways attach your access db to you App_Code Folder
     

    You mean the App_Data folder! In your example code you use the right folder. But you're using ODBC, however you better use OleDb to connect to an Access Database:

    http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET

    Example codes can be found here:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    And when working with dates and Access, read this:

    http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET

    Saturday, January 29, 2011 11:10 AM
  • User803118023 posted

    But you have asked only one question


    Lolz.... i think moderator has edited my email so that at a time only one question could be answered. i have asked two. ok. the code you sent to me  is working fine. but i m looking for built in method. there are many, one is i think date.now(); something like that, but it returns date and time both at same time.i need only date. if you know how to do this with .now() function kinldy guide me in that.

    thnx for your answer, 1st this question get cleared then i will ask the 2nd question.

    Saturday, January 29, 2011 1:55 PM
  • User2019981500 posted

    Hi,

    just Insert

    DateTime.Now.Date if you want only date portion


    Regards


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 29, 2011 2:07 PM
  • User803118023 posted

    thnx alot shabbir! for your help. this method works fine for me.

    now other thing which i want to ask is that:

    i m making a database to save emplyee records. it has a date of birth column. if i chose data type for this column as datetime, then it shows date with it which is not fit for this column. and if i chose text datatype, then anyone can enter 30th of feb. which is wrong entry. how to make a check on this? i used some regular expressions also but could't find any regular expression which can be helpfull to check this thing.

    Monday, January 31, 2011 3:13 PM
  • User2019981500 posted

    Just write me what exactly you want regarding Date.Make me understand your Requirement Again..

    You also Visit this,MAY BE IT WILL HELP YOU

    http://regexlib.com/Search.aspx?k=date&c=5&m=-1&ps=20

    Regards

    Friday, February 4, 2011 3:20 PM
  • User803118023 posted

    i have a database table. which have employee record. there is a field in that table which is made to store date of birth.

    i had used "text" data type for this field. and use a regular expression to enter date in 12/23/1984 format.

    but problem with this  approach is if i enter 29/2/1983, the date get inserted, altough its not a valid date since in 1983 feb have 28 days not 29.

    to overcome this problem i changed the data type of this field from "text" to "datetime" , now if i want to store this invalid date it wont inserted and an exception occures. but now problem is that it shows time with date of birth which is logically wrong. because if we mention date of birth of a person, then there is no need to mention the time. but "datetime" data type show time with date. and i dont want this result. kindly tell me how can i get my desired result? that no one can enter invalid dates like i gave you example of 29th of feb in 1983. 

    Monday, February 7, 2011 5:54 AM
  • User2019981500 posted

    Hi,


    Fist of all you know that Users sucks every way and you let them suck so easily.why? Just give him dropdownlist with all correct dates.Try to replace your textbox(through which user inputs date) with dropdownlist.

    Paste Blew code there

    [Code snippet from Asp.net Tutorial]


      <form id="form1" runat="server">
    <div>
    <asp:DropDownList ID="DropDownList1" runat="server" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="True">
    </asp:DropDownList>
    <asp:DropDownList ID="DropDownList2" runat="server" OnSelectedIndexChanged="DropDownList2_SelectedIndexChanged" AutoPostBack="True">
    </asp:DropDownList>
    <asp:DropDownList ID="DropDownList3" runat="server" AutoPostBack="True">
    </asp:DropDownList><br />
    <asp:Label ID="Label1" runat="server"></asp:Label><br />
    
    </div>
    </form> 


    Code behind


    using System;
    using System.Data;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    
    public partial class Default3 : System.Web.UI.Page
    {
        int year, month;
        protected void Page_Load(object sender, EventArgs e)
        {
            DateTime tnow = DateTime.Now;
            ArrayList AlYear = new ArrayList();
            int i;
            for (i = 2002; i <= 2010; i++)
                AlYear.Add(i);
            ArrayList AlMonth = new ArrayList();
            for (i = 1; i <= 12; i++)
                AlMonth.Add(i);
            if (!this.IsPostBack)
            {
                DropDownList1.DataSource = AlYear;
                DropDownList1.DataBind();
                DropDownList1.SelectedValue = tnow.Year.ToString();
                DropDownList2.DataSource = AlMonth;
                DropDownList2.DataBind();
                DropDownList2.SelectedValue = tnow.Month.ToString();
                year = Int32.Parse(DropDownList1.SelectedValue);
                month = Int32.Parse(DropDownList2.SelectedValue);
                BindDays(year, month);
                DropDownList3.SelectedValue = tnow.Day.ToString();
            }
            Label1.Text = "You select date:" + DropDownList1.SelectedValue + "year" + DropDownList2.SelectedValue + "month" + DropDownList3.SelectedValue;
        }
    
        //judge leap year
        private bool CheckLeap(int year)
        {
            if ((year % 4 == 0) && (year % 100 != 0) || (year % 400 == 0))
                return true;
            else return false;
        }
    
        //binding every month day
        private void BindDays(int year, int month)
        {
            int i;
            ArrayList AlDay = new ArrayList();
    
            switch (month)
            {
                case 1:
                case 3:
                case 5:
                case 7:
                case 8:
                case 10:
                case 12:
                    for (i = 1; i <= 31; i++)
                        AlDay.Add(i);
                    break;
                case 2:
                    if (CheckLeap(year))
                    {
                        for (i = 1; i <= 29; i++)
                            AlDay.Add(i);
                    }
                    else
                    {
                        for (i = 1; i <= 28; i++)
                            AlDay.Add(i);
                    }
                    break;
                case 4:
                case 6:
                case 9:
                case 11:
                    for (i = 1; i <= 30; i++)
                        AlDay.Add(i);
                    break;
            }
            DropDownList3.DataSource = AlDay;
            DropDownList3.DataBind();
        }
        //select year
        public void DropDownList1_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            year = Int32.Parse(DropDownList1.SelectedValue);
            month = Int32.Parse(DropDownList2.SelectedValue);
            BindDays(year, month);
        }
    
        //select month
        public void DropDownList2_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            year = Int32.Parse(DropDownList1.SelectedValue);
            month = Int32.Parse(DropDownList2.SelectedValue);
            BindDays(year, month);
        }
    }



    Regards

    shabir

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 7, 2011 6:41 AM
  • User803118023 posted

    thnx shabbir. its a very simple logic. but since i m new in asp.net so i have no idea how to implement.

    thnx for the help.  i will back, when have some more questions.

    Monday, February 7, 2011 6:51 AM
  • User2019981500 posted

    Still you are great.. becuase good learner is always great..

    AnyTime......

    All the best..

    Monday, February 7, 2011 6:52 AM
  • User-1199946673 posted

     

    Just give him dropdownlist with all correct dates

     

    You really think this is a good approach? There's no need to use any code behing, by simply use a comparevalidator:

    <asp:TextBox ID="DobTextBox" runat="server" />
    <asp:CompareValidator ID="DobCompareValidator" runat="server" ControlToValidate="DobTextBox" ErrorMessage="Invalid Date" Text="*" Operator="DataTypeCheck" Type="Date" />


     

    This will validate the Textbox that it will contain a valid date. But sinc a Dob of birth should be in the past, so dates in the futere are not valid also, you can this this to:

    <asp:TextBox ID="DobTextBox" runat="server" />
    <asp:CompareValidator ID="DobCompareValidator" runat="server" ControlToValidate="DobTextBox" ErrorMessage="Invalid Date" Text="*" Operator="LessThenEqual" ValueToCompare="1/1/2011" Type="Date" />
    
    

    Change the ValueToCompare property in the maximum dat. This can be done in code behind or in the HTML markup....

    Monday, February 7, 2011 7:05 AM
  • User2019981500 posted

    Hi Hans ,there are Lots of issues what he has mentioned to private mail, and providing simple format  using dropdown list is acceptal and best for the User friendly interface.Right? free from injection as well as user doesn't need to type anything.Rest Coding part is just functions which he can reuse anywhere.

    Hans, i don't understand how can we create an object of type ValidationCompareOperator from its string representation 'LessThenEqual' for the 'Operator' property.


    Regards

    shabir

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 7, 2011 7:34 AM
  • User803118023 posted

    Hans! thnx for your help too. may be your approach is good, but since i m a new commer in asp.net so my concepts and my approach towords programing is not very good. the method explained by shabbir is easy for me and i completely understnad what he explained and it makes my job done. 

    Monday, February 7, 2011 9:39 AM
  • User-1199946673 posted

    ,there are Lots of issues what he has mentioned to private mail
     

    Then you better answer him in the private emails. How can we answer if we don't get the whole picture?

    and providing simple format  using dropdown list is acceptal and best for the User friendly interface

    He specifically asked for a textbox!

    Right?

    I don't see it that way. A user friendly interface is to provide a calender, like:

    http://www.asp.net/ajax/ajaxcontroltoolkit/Samples/Calendar/Calendar.aspx

    free from injection as well

    I wouldn't rely on that

    as user doesn't need to type anything

    With my example the user can type whatever they want, but only valid dates are allowed

     

    i don't understand how can we create an object of type ValidationCompareOperator from its string representation 'LessThenEqual' for the 'Operator' property.

    Read MSDN:

    http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.comparevalidator.aspx

    Monday, February 7, 2011 1:43 PM
  • User-1199946673 posted

    may be your approach is good
     

    If you would start learning ASP.NET from the beginning, you would understand what I'm saying

    but since i m a new commer in asp.net so my concepts and my approach towords programing is not very good.

    That's why you should start learning ASP.NET, not just ask for code snippets without understandng the whole picture

    the method explained by shabbir is easy for me

    So you only want to learn easy things? And I really don't see that the code is easier that sme setting in HTML markup Just try to inderstand wjat validation controls are all about:

    http://www.asp.net/general/videos/how-do-i-use-validation-controls-in-aspnet

    and it makes my job done. 

    And most likely, next time you've another validation problem (like how to validate a number between 100 - 200), you need to ask another question

    Monday, February 7, 2011 1:50 PM
  • User2019981500 posted

    Then you better answer him in the private emails. How can we answer if we don't get the whole picture?

    You can see when this thread has started.It is almost 7 days back and he has send private mail like he was not able to post because of some issue.

    I don't see it that way. A user friendly interface is to provide a calender, like:

    Calendar is best choice but traditional and well know,Light weight Date display interface is Dropdownlist format only.

    With my example the user can type whatever they want, but only valid dates are allowed

    When database column datatype Format is choosen from backend,Isn't it good to provide selection to user rather than giving him choice of entering anything.

    Regards

    shabir


    Monday, February 7, 2011 2:37 PM
  • User2019981500 posted

    Hi Hans,

    Then you better answer him in the private emails. How can we answer if we don't get the whole picture?


    You can see when this thread has started.It is almost 7 days back and he has send private mail like he was not able to post because of some issue.

    I don't see it that way. A user friendly interface is to provide a calender, like:


    Calendar is best choice but traditional and well know,Light weight Date display interface is Dropdownlist format only.

    With my example the user can type whatever they want, but only valid dates are allowed


    When database column datatype Format is choosen from backend,Isn't it good to provide selection to user rather than giving him choice of entering anything.

    Regards

    shabir



    Monday, February 7, 2011 2:47 PM
  • User803118023 posted

    Hello Hans_V

    i have used the code given by Shabbir, and its perfectly working for me, i also have made some changes in it according to my requirements. but to learn more i would be happy if you explain the startegy which you told here. regarding validation controls, i try to get some regular expression which can check that 29th Feb is a valid date or not, but i could't find any regular expression like this.

    i visited this site to search for regular expression. http://regexlib.com/

    Saturday, February 12, 2011 12:20 AM
  • User-1199946673 posted

    regarding validation controls, i try to get some regular expression which can check that 29th Feb is a valid date or not, but i could't find any regular expression like this.
     

    When using validation controls, you don't need a regular expression to validate a date. Like I said, you can use a comparevalidator to check whether or not a control (like a textbox) holds a valid date (using the DataTypeCheck) and even if a date is smaller/greater than a given date. If you want a date to be in a certain range, use a rangevalidator instead.

    When using 3 comboboxes, you can't use these validators. but you need to use a customvalidator and write you're own logic in code behind, you can use the code already provided and optionally you can write something similar in javscript so that the customvalidator will also work on client side.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 12, 2011 6:07 AM
  • User1335898253 posted

    If you want to export data from MS access, I suggest to use a C# data export component Spire.DataExport, I use it for a long time, quit good. Now it provides a free version. You may try it. It supports importing/exporting to XLS, PDF, Word and so on. I think it may help you.
    More information:
    http://www.e-iceblue.com/Introduce/free-dataexport-component.html
    If you have questions you may send an email to me. My email address is: tom.lutao@e-iceblue.com

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 14, 2011 9:29 PM