ADO,Access, & Dates


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: ADO,Access, & Dates

  1. #1
    Randy Guest

    ADO,Access, & Dates


    I am using a DSN Less connection to an Access database. Getting information
    from users through combo and text boxes. When my form opens a text box is
    populated with the system time. When I try to send the informaiton to my
    database I get a runtime error of -2147217900 (80040e14).
    There is a problem with the date field that I cannot figure out. I have
    changed the date format in Access and in my VB environment and still cannot
    get the date in my database. My code to send the data to the database is
    below. Does anybody have any suggestions?
    To get the date I set my variable = to Date as follows

    datDate = Date


    Thanks in advance

    Private Sub cmdSendData_Click()

    Dim statement As String


    statement = "INSERT INTO MeterChangeOut " & _
    "(SerialNum, UserID, ComputerName, Reading, Location, MapNumber, Date)
    " & _
    " Values (" & _
    "'" & lblSerialNumText.Caption & "', " & _
    "'" & lblUserIDText.Caption & "', " & _
    "'" & lblComputerNameText.Caption & "', " & _
    "'" & txtReading.Text & "', " & _
    "'" & cboLocation.Text & "', " & _
    "'" & txtMapNumber.Text & "', " & _
    "'" & txtDate.Text & "'" & _
    ")"


    cnnMeterChangeOut.Execute statement, , adCmdText

  2. #2
    Kevin MacCallum Guest

    Re: ADO,Access, & Dates


    Couple of things:
    Don't use quotes around a date field in a SQL statement. Should use "#"
    It requires the date to be in mm/dd/yyyy format when used in this manner.

    Also - Date is a reserved word in VBA. To indicate it is a field name wrap
    it in [] or better yet change the field name something like MyDate.
    Here is a revised SQL statement:

    statement = "INSERT INTO MeterChangeOut " & _
    "(SerialNum, UserID, ComputerName, Reading, Location, MapNumber, [Date])
    " & _
    " Values (" & _
    "'" & lblSerialNumText.Caption & "', " & _
    "'" & lblUserIDText.Caption & "', " & _
    "'" & lblComputerNameText.Caption & "', " & _
    "'" & txtReading.Text & "', " & _
    "'" & cboLocation.Text & "', " & _
    "'" & txtMapNumber.Text & "', " & _
    -> "#" & format$(txtDate.Text,"mm/dd/yyyy") & "#" & _
    ")"

    For more info do a search for "date literals, specifying in SQL statements"
    in the Access help file.
    It is talking about using dates in a criteria but concept still applies.

    Here is an excerpt:

    When you specify the criteria argument, date literals must be in U.S. format,
    even if you're not using the U.S. version of the Microsoft Jet database engine.
    For example, May 10, 1996, is written 10/5/96 in the United Kingdom and 5/10/96
    in the United States. Be sure to enclose your date literals with the number
    sign (#) as shown in the following examples.

    To find records dated May 10, 1996 in a United Kingdom database, you must
    use the following SQL statement:

    SELECT *
    FROM Orders
    WHERE ShippedDate = #5/10/96#;

    Kevin

    "Randy" <reverett@naeci.com> wrote:
    >
    >I am using a DSN Less connection to an Access database. Getting information
    >from users through combo and text boxes. When my form opens a text box

    is
    >populated with the system time. When I try to send the informaiton to my
    >database I get a runtime error of -2147217900 (80040e14).
    >There is a problem with the date field that I cannot figure out. I have
    >changed the date format in Access and in my VB environment and still cannot
    >get the date in my database. My code to send the data to the database is
    >below. Does anybody have any suggestions?
    >To get the date I set my variable = to Date as follows
    >
    >datDate = Date
    >
    >
    >Thanks in advance
    >
    >Private Sub cmdSendData_Click()
    >
    > Dim statement As String
    >
    >
    > statement = "INSERT INTO MeterChangeOut " & _
    > "(SerialNum, UserID, ComputerName, Reading, Location, MapNumber, Date)
    >" & _
    > " Values (" & _
    > "'" & lblSerialNumText.Caption & "', " & _
    > "'" & lblUserIDText.Caption & "', " & _
    > "'" & lblComputerNameText.Caption & "', " & _
    > "'" & txtReading.Text & "', " & _
    > "'" & cboLocation.Text & "', " & _
    > "'" & txtMapNumber.Text & "', " & _
    > "'" & txtDate.Text & "'" & _
    > ")"
    >
    >
    > cnnMeterChangeOut.Execute statement, , adCmdText



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center