SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 4 of 4
  1. #1
    Apprentice
    Join Date
    Oct 2006
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Unable to update oracle database table

    Hi All,

    I am trying to update table in oracle database, my script works fine if I use select command to retrieve data, but I don't know why it is not working for update

    QTP executes the script successfully but when I check the database data is not updated can someone please tell me is there anything I missed in the below code

    Dim curDate, curMonth, sqlQuery, sqlQuery2
    curDate = Day(Date)
    curMonth = Month(Now) -2
    curYear = Year(Date)

    If curDate >=1 Then
    curDate = 1
    End If

    nowdate = curYear & "/" & curMonth & "/" & curDate

    sqlQuery = "update TSE_ELS Set VA_BY_Data = null where TO_CHAR(VA_BY_Data, 'yyyy/mm/dd') > " & "'" & nowdate & "'" & " and TO_CHAR(VA_BY_Data,'yyyy/mm/dd') < " & "'" & Date & "'"

    Set fcsWoods = CreateObject("ADODB.Connection")

    fcsWoods.ConnectionString = "DSN=becwoods;UID=fcs;PWD=xyzabc;DBQ=BECWOODS;DBA= W;APA=T;EXC=F;FEN=T;QTO=F;FRC=10;FDL=10;LOB=T;RST= T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MT S=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F; "

    fcsWoods.Open
    fcsWoods.BeginTrans

    fcsWoods.Execute(sqlQuery)

    fcsWoods.CommitTrans
    fcsWoods.Close
    Set fcsWoods = nothing

    Thanks
    Ccnu

  2. #2
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0

    Re: Unable to update oracle database table

    Print sqlQuery

    Copy the query from print log and execute it in Toad or SQL whatever you have and make sure it works corretcly over there

  3. #3
    Apprentice
    Join Date
    Oct 2006
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Unable to update oracle database table

    Thank you Tarun,

    the problem lies below code line

    nowdate = curYear & "/" & curMonth & "/" & curDate

    above code returns the date as 'yyyy/m/d' whereas in my query I am passing 'yyyy/mm/dd' I fixed my code as below

    nowdate = curYear & "/" & "0" & curMonth & "/" &"0" & curDate

    Now I am able to update the database without any problems

    Once again thank you.

    Regards
    ccnu

  4. #4
    Member
    Join Date
    Aug 2009
    Location
    Bangalore, Karnataka, India
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Unable to update oracle database table

    But wouldn't the quesry create problem when date is like 2010/12/25 bcos appending 0 to it would make it 2010/012/025 which is not a date format.
    --------------------------------------------------
    K. Raghu Ram
    Automation & Performance Test Engineer
    --------------------------------------------------

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 11.54%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 09:47 PM.

Copyright BetaSoft Inc.