Click here to Skip to main content
15,845,367 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi

I have the following vbscript that extract data from a table and then itterating through the line items extracted and insert these line items into an excel sheet.

The script works 100 %.

I now need to impliment a change and that is when I itterate through the line items and the item is inserted into the excel spreadsheet I want to update that line items status from i.e not extracted to extracted.

Here is that portion of the code. could you assist me in order to get the update to work.
VB
SQL3 = "SELECT * FROM PO_ITEM_Details WHERE Folder_ID = '" & FolderID & "' and Quantity_Received > 0"

    rstInv.Open SQL3, objcn

    WHILE NOT rstInv.EOF and x < 10
        x = x + 1
        objSht.Cells((47 + x),2).Value = rstINV("Item_Number")
        objSht.Cells((47 + x),3).Value = rstINV("Description")
        objSht.Cells((47 + x),4).Value = rstINV("Quantity_Received")
        objSht.Cells((47 + x),5).Value = rstINV("UOM")
        objSht.Cells((47 + x),6).Value = rstINV("Unit_Price")
        objSht.Cells((47 + x),7).Value = rstINV("Currency1")
        objSht.Cells((47 + x),8).Value = rstINV("EXT_Price")
        objSht.Cells((47 + x),9).Value = rstINV("New_EXT_Price")

        rstInv.MoveNext
    WEND

    rstInv.Close
Posted
Updated 30-Aug-11 2:31am
v2
Comments
Prerak Patel 30-Aug-11 7:31am    
Use code block for code segments.

1 solution

You can use this in loop
VB
rstINV("Item_Status") = "Extracted"
rstINV.Update
rstInv.MoveNext

You might need to set lock and other stuff
VB
rstInv.CursorLocation = 3
rstInv.Open SQL3, objcn, 3, 3


Ref:http://www.cruto.com/resources/vbscript/vbscript-examples/misc/database/Update-a--Record-in-a-Recordset.asp[^]
 
Share this answer
 
Comments
Swartkat007 30-Aug-11 8:36am    
Prerak Patel, Thank you the information you have given me works like a charm.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900