Adding System Date in Database

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.

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

User2019981500 posted

Hi,

just Insert

DateTime.Now.Date if you want only date portion

Regards

Answer 5

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.

Answer 6

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

Answer 7

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. 

Answer 8

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

Answer 9

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.

Answer 10

User2019981500 posted

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

AnyTime......

All the best..

Answer 11

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

Answer 12

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

Answer 13

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. 

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

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/

Answer 19

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.

Answer 20

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: [e-mail]