Access Databases and AccessDataSource Control

Re: Problems with date format in Access 2007

Hanwell — Mon, 14 Sep 2009 16:23:12 GMT

got mine working too thanks for the help did the following.

Imports System.Data.OleDb
Imports System.Data

Partial Class Members_Admin_AddEvent
    Inherits System.Web.UI.Page

    Protected Sub DropDownOFirstName_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownOFirstName.DataBound
        DropDownOFirstName.Items.Insert(0, "Please Select Member")
    End Sub



    Protected Sub DropDownDMFirstName_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownDMFirstName.DataBound
        DropDownDMFirstName.Items.Insert(0, "Please Select Member")
    End Sub

    Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
        If Page.IsValid Then
            Dim ConnString As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|membership.mdb"

            Dim SqlString As String = "INSERT INTO TBL_Events(MemberID, DiveManagerID, [Date], Location, Duration, Details) VALUES (?, ?, ?, ?, ?, ?)"

            Using conn As New OleDbConnection(ConnString)

                Using cmd As New OleDbCommand(SqlString, conn)

                    cmd.CommandType = CommandType.Text

                    cmd.Parameters.AddWithValue("MemberID", DropDownOFirstName.SelectedValue)

                    cmd.Parameters.AddWithValue("DiveManagerID", DropDownDMFirstName.SelectedValue)
                    cmd.Parameters.AddWithValue("Date", Convert.ToDateTime(TextBoxStartDate.Text).ToOADate)
                    cmd.Parameters.AddWithValue("Location", TextBoxLocation.Text)
                    cmd.Parameters.AddWithValue("Duration", TextBoxDuration.Text)
                    cmd.Parameters.AddWithValue("Details", TextBoxDetails.Text)

                    conn.Open()

                    cmd.ExecuteNonQuery()

                End Using

            End Using

got some tips from

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

and made the connection with tips from

http://www.asp101.com/articles/john/connstring/default.asp

Re: Problems with date format in Access 2007

hans_v — Fri, 03 Jul 2009 14:35:02 GMT

pavan496:
Using parameterized queries solved my problem

And it will save you from many other problems also, like SQL injections, and it makes your code more readable and structured....

You're Welcome

Re: Problems with date format in Access 2007

pavan496 — Fri, 03 Jul 2009 13:22:36 GMT

Thankyou very much.. Using parameterized queries solved my problem.. Thanks a lot...

Regards

Re: Problems with date format in Access 2007

hans_v — Fri, 03 Jul 2009 09:52:31 GMT

v.vivek:

use cdate() function. i hope this solve ur problem.

The problems begin when you 're using the cdate() function in an SQL statement. The trick is to do all the conversions from String to Date in VB or C#, because then you make use of the globalisation you want to use, and add the date as as parameter to the SQL command.

Re: Problems with date format in Access 2007

v.vivek — Fri, 03 Jul 2009 09:16:24 GMT

use cdate() function. i hope this solve ur problem.

Re: Problems with date format in Access 2007

hans_v — Fri, 03 Jul 2009 07:40:04 GMT

pavan496:
Similarly, i changed the format of the date field in access 2007 table to dd/MM/yyyy wherever needed.

This is nonsense. What you're doing there is specifying how a date is represented when you're using Access as application. But also in the access application, ýou did not, and you can not specify how a date is stored internally. In fact, all dates in Access are stored as numbers! But In an ASP.NET environment, you're not dealing with an access database, but with a Jet Database Engine. When you are entering dates in aa/bb/cccc format, Jet willl thread that as according to the culture.

When you set a culture, then all dates (and numbers) are shown by default according to that culture. If you didn't set a culture, ASP.NET uses the culture of the users browser. When a user is entereing a date in a textbox, the same is happening. So when you have a textbox where a user entered a date. all you need to do is convert it to a datetime:

dim aDate = Convert.ToDateTime(TextBox1.Text)

The Date in the TextBox will be handled as specified in the culture, either the culture of the users browser, or, if you did set a culture in web.config, by that culture

A simple test will show what I just explained

Drag a textbox (TextBox1), a Label (Label1) and a Button (Button1) on a Form. In the code behind enter:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim aDate As DateTime = Convert.ToDateTime(TextBox1.Text)
        TextBox1.Text = aDate.ToShortDateString
        Label11.Text = aDate.Day.ToString
End Sub

When you set the Culture in web.config to en-GB, and enter 2/1/2009 in the textbox and click the button, the label will show that the day is 2

When you enter 2/13/2009, that it will throw an error, because it isn't a valid date, because 13 is threated as the month

When you change the CUltue in web.config to en-US and enter 2/1/2009, the label will show that the day is 1

When you enter 2/13/2009, the label will show that the day is 13

And when you enter 13/1/2009, it will throw an error

One more thing. When entering data in a database, always use parmeterized queries

http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

when entering a date, convert the date into a Ole Automation date:

cmd.Parameters.AddWithValue("DateField", Convert.ToDateTime("DateString").ToOADate)

Re: Problems with date format in Access 2007

asifchouhan — Fri, 03 Jul 2009 04:38:44 GMT

Try this

For example:

</appSettings>

Then you could refer to this value in your code wherever you need it, like this:

<asp:Label ID="lblDate" runat="server" Text='<%# Eval("IssueDate", System.Configuration.ConfigurationManager.AppSettings("MY_DATE_FORMAT")) %>'></asp:Label>

Regards

Asif

Problems with date format in Access 2007

pavan496 — Fri, 03 Jul 2009 02:31:24 GMT

Hello, I have an asp.net application with ms access as the database. I want the date format in the whole application to be in dd/MM/yyyy format, so i changed the culture to "en-GB" in web.config file. Similarly, i changed the format of the date field in access 2007 table to dd/MM/yyyy wherever needed.

Now, my problem is ,

when i enter any date with the date field less than 12, access is considering it as month, for example, 03/05/2009 (dd/mm/yyyy - 3rd may) is ending up as March 5th.

when i enter a date with date field greater than 12, it is working properly.

I would like to know what is going wrong, and what can i do to solve this problem.

Thanks in advance.

Regards