SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 8 of 8
  1. #1
    Member
    Join Date
    Jan 2008
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Updation of result set record


    Hi,
    My requirEment is I want to update all the records of the database when record set is open. My approach is through one recordset object Iam fetching the values by another recordset Iam trying to update. When Iam trying to update the record syntax error in update query message coming.
    Iam using QTP 9.0 on MS XP,sp2 operating system with and database is local(in my system only).
    Any body can explain better approach and solution for this.

    Here with Iam attaching the code.
    For testing purpose i create three columns
    1. RECID
    2. ABC
    3. DEF
    '--- CODE
    Dim oConn
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Provider = "Microsoft.Jet.OLEDB.4.0"
    Set oRs = CreateObject("ADODB.Recordset")
    Set oRs1 = CreateObject("ADODB.Recordset")
    sql= " select * FROM testdb"

    oConn.Open "C:\testdb.mdb"
    oRs.CursorLocation = 3
    oRs.CursorType = 3
    oRs.Open sql,oConn
    If oConn.State=1 And oRs.State =1 Then' MsgBox "Data base connected"
    MsgBox oRs.RecordCount
    oRs.MoveFirst
    i=1
    While Not oRs.EOF
    ' msgbox oRS.fields("RECID")
    ' msgbox oRS.fields("ABC")
    ' msgbox oRS.fields("DEF")
    sql1="Update testdb set " & "oRs.Fields(ABC)='"& i & "' where "& oRs.Fields("RECID")&" ='"&oRs.Fields("RECID")&"'"
    msgbox sql1
    oRs1.open sql1,oConn
    msgbox err.description
    i=i+1
    oRs.Movenext
    Wend
    End If
    oRs.Close
    oConn.Close
    Set oRs = Nothing
    Set oConn = Nothing
    Thanks & Regards,

    Rama Kishore Kanagala

  2. #2
    Member
    Join Date
    Nov 2006
    Location
    Leeds,UK
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Updation of result set record

    Would recommend making 2 VB script functions.

    The first would open a new connection , retrive all records as per you query and save it to a flat file, close the ADODB connection.

    The second would open the ADODB connection again, read each row from flat file , replace these values in to ur update query, fire the query in to database, repeat this for all the rows using a loop. close the adodb aseeion.

    Doing this way would make your code modular and also reusable.

    Note:
    1) Make sure username and password for accesing DB has the update permissions.

    2) Also make sure u have chosen the cursor as per your database type.

    Hope this helps

  3. #3
    Member
    Join Date
    Jan 2008
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Updation of result set record

    no roope in my database 156 columns and 7000 records are there. it wont work out. apart from this any other method. using dsn or any other way.
    Thanks & Regards,

    Rama Kishore Kanagala

  4. #4
    Member
    Join Date
    Jan 2008
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Updation of result set record

    My exact requirment is i should fetch the values from db and key in the parameters in application. Database should update with result.
    Thanks & Regards,

    Rama Kishore Kanagala

  5. #5
    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: Updation of result set record

    Tanya, you have mixed SQL And code both

    While Not oRs.EOF
    ' msgbox oRS.fields("RECID")
    ' msgbox oRS.fields("ABC")
    ' msgbox oRS.fields("DEF")
    sql1="Update testdb set " & "oRs.Fields(ABC)='"& i & "' where "& oRs.Fields("RECID")&" ='"&oRs.Fields("RECID")&"'"
    msgbox sql1
    oRs1.open sql1,oConn

    should be something like

    While Not oRs.EOF
    ' msgbox oRS.fields("RECID")
    ' msgbox oRS.fields("ABC")
    ' msgbox oRS.fields("DEF")
    sql1="Update testdb set ABC='"& i & "' where RECID='"&oRs.Fields("RECID")&"'"
    msgbox sql1
    oRs1.open sql1,oConn

  6. #6
    Member
    Join Date
    Jan 2008
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Updation of result set record

    I tried tarun. Now I got new error message - "Data type mismatch in criteria expression". I am used datatype as text for all the columns.
    Thanks & Regards,

    Rama Kishore Kanagala

  7. #7
    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: Updation of result set record

    You need to remove "'" from front of columns with number type.

  8. #8
    Member
    Join Date
    Jan 2008
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Updation of result set record

    Thanks, Tarun. Its working. I indexed in database. RECID is numeric. Because of that it is giving error
    Thanks & Regards,

    Rama Kishore Kanagala

 

 

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 05:04 PM.

Copyright BetaSoft Inc.