Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40

    Question Unanswered: URGENT!!! Inserting Time with con.execute in Oracle

    Hi there,
    I wanna to insert Date & Time in an Oracle table from VB, using a Insert Query.
    I had used these:
    ########## CODE #################
    Dim sqlOrcl as String
    Dim conOrcl As New ADODB.Connection

    conOrcl.Open "Provider = OraOLEDB.Oracle.1 ;Persist Security Info=False;User ID=test;password = test;Data Source=oracledb"

    sqlOrcl = "Insert into testDate values(to_date('" & VBA.CStr(VBA.Format(VBA.CStr(VBA.Now), "dd-MMM-yyyy HH:NnS AMPM")) & "','dd-MON-yyyy HH:MIS AM'))"

    conOrcl.Execute sqlOrcl

    ###############################################
    The above Query is working absolutely fine but I have to use the to_date() of Oracle environment.
    Is there any way to insert the Date & Time in Oracle via a query without writing to_date() function.

    When I use this-------------

    sqlOrcl = "Insert into testDate values( '" & VBA.CStr(VBA.Format(VBA.CStr(VBA.Now), "dd-MMM-yyyy HH:NnS AMPM")) & "')"

    It gives error
    ORA - 01858: a non-Numeric Character found where a numeric was expected.

    The above error was with OraOLEDB.Oracle.1

    While when I use MSDAOra.1 it gives the following error.
    ORA - 01830: date format picture ends before converting entire input string.

    I am unable to understand whenever I do not specify the Time with in the Query there is no requirement for the to_date() and the query works absolutely fine.Please help!!!
    Thanking you in anticipation.
    RGDS
    AMIT

  2. #2
    Join Date
    Nov 2004
    Posts
    4
    If you needed the system date, I will prefer use the sysdate because you are using the now function.
    so your SQL will change as follows
    "Insert into testDate values(sysdate)"

    if you want to insert the date entered by the used in a dtpicker
    "Insert into testDate values('" & format(dtpuserdate.value,"DD-MMM-YYYY") & "')"

    try this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •