Rs.Update when multiple table select

Results 1 to 5 of 5

Thread: Rs.Update when multiple table select

  1. #1
    Susanne Guest

    Default Rs.Update when multiple table select

    I need to update two tables, which I have selected as below.<BR><BR>sSQL = "SELECT * FROM booking,schedule WHERE booking.sh_ref = schedule.sh_ref AND booking.bk_ref = " & bk_ref<BR>rs1.Open sSQL,,1,1,1<BR>rs1("booking.bk_approved") = 1<BR>rs1("booking.bk_po") = bk_po<BR>rs1("schedule.sh_places") = rs1("schedule.sh_places") - 1<BR>rs1.Update<BR><BR>This fails with the error:<BR><BR>ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application. <BR><BR>How do I use the rs.update function when there are two tables in my rs?<BR><BR>Any help most appreciated.<BR><BR>Thanks<BR>Susanne

  2. #2
    Vidya Guest

    Default RE: Rs.Update when multiple table select

    Hi,<BR>The error which u got implies that the column which u specified is not in the recordset.<BR>Instead of rs1("booking.bk_po"), try giving just rs1("bk_po").<BR>It will work. <BR>

  3. #3
    Susanne Guest

    Default RE: Suggestion didn't work!!!!!

    &nbsp;<BR>When I change the code as suggested to be:<BR><BR>sSQL = "SELECT * FROM booking,schedule WHERE booking.sh_ref = schedule.sh_ref AND booking.bk_ref = " & bk_ref<BR>rs1.Open sSQL,,0,2<BR> <BR>rs1("bk_approved") = 1<BR>rs1("bk_po") = bk_po<BR>rs1("sh_places") = rs1("sh_places") - 1<BR> <BR>rs1.Update<BR><BR>I get now get the error:<BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039 8000ffff&#039 <BR><BR>The query is not updateable because the from clause is not a single simple table name. <BR><BR>- which makes me think that this is not the way to do an update on a recordset made up of two tables. I&#039m looking around for some examples but failing to come up with anything concrete. If anyone out there knows if this is possible then please let me know!<BR><BR>Thanks<BR>Susanne

  4. #4
    Hema S Guest

    Default RE: Suggestion didn't work!!!!!

    I&#039m not sure if this is a better solution, since I&#039ve not worked with the Recordset Update feature. Why don&#039t you use an explicit update statement & execute it? I always write my own.<BR>Hema


  5. #5
    Join Date
    Dec 1969
    Posts
    274

    Default RE: Suggestion didn't work!!!!!

    Try using an update query, e.g.<BR><BR>sSQL = "Update booking,schedule " & _<BR> "Set bk_approved=1,bk_po=&#039" & bk_po & "&#039, sh_places=-1" & _ "WHERE booking.sh_ref = schedule.sh_ref AND booking.bk_ref = " & bk_ref<BR><BR>and use a command object to execute it.<BR><BR>OR<BR><BR>maybe try some crazy data shaping, or retrieve the rows from one table, set them, update, retrieve rows from second, update.

Posting Permissions

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