Hi Jim,
make two/three actions it will create two/three local sheets.
First action for Query data and fill the data values in thats action runtime
sheets.
second action your application script for entering the value from the sheet
of first action sheet.
For getting the data from first action sheet use data table sheet name
("first sheet name") parameter name .
On Sat, Jan 29, 2011 at 12:11 AM, Jimmie Parson <[email protected]> wrote:
> To all,
>
> A few days ago I posted about some issues I had with returning the
> values from the runtime datatable. With some assistance I was able to
> resolve one of my issues I am hoping with your help I can resolve the
> last issue I have right now. I will setup what I have tried so far:
>
> 1. I am connecting to an Oracle DB with an ADOB connection and running
> a SQL query to return the results back to the runtime datatable. This
> was the first issue I had trouble with that is now working.
>
> 2. As long as I only need one query ran and a single output sheet
> added to the datatable everything is fine. The problem arises when
> running 2 or more queries that add more sheets to the datatable. When
> I try to return the value of a cell in any of those sheets with this
> code:
>
> OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
> Lines").OracleTable("LIST_LINES").EnterField 1, "Product Value",
> DataTable.GetSheet("output1").GetParameter("Segment1").Value
>
>
> I get the following error:
>
> The Sheet.GetParameter operation failed. The <Segment1> column does
> not exist.
>
> I have ran some scripts and figured out that the sheets have been
> created and they have the correct number of parameters. I added my
> variables to the debug "add to watch" and I get the same error.
>
> If I run the following code:
>
> Sheetname = DataTable.LocalSheet.Name
> '
> 'Reporter.ReportEvent 1, "The Active Sheet is", Sheetname
>
> I get a return of the active sheet is "Action1"
>
> My question is:
>
> 1. Is there a way to change the active runtime datatable sheet to the
> sheet I need to return the values from?
>
> 2. If not does anyone have any advice as to how to proceed.
>
>
> I will include the code for the script below:
>
>
> 'Connect to Oracle DB and execute a SQL Query, then write the data to
> 'the QTP runtime datatable.
>
> 'Variables for the database connection
> Dim adocon,adorecordset
> 'Create the Connection object
> Set adocon=CreateObject("ADODB.Connection")
> constr="Provider=OraOLEDB.Oracle.1;Password=xxxx;Persist Security
> Info=True;User ID=xxxx;Data Source=Dev"
> adocon.Open constr
> 'Execute the SQL query
> Set adorecordset=CreateObject("ADODB.Recordset")
> Set adorecordset.ActiveConnection=adocon
> Strsql="Select 'TEST PRICE LIST:' || to_char(sysdate,' MM-DD-YY
> HH:MM:SS') as PRICE_LST_NAME from Dual"
> adorecordset.Source=Strsql
> adorecordset.Open
> 'Create the datatable to export the data
> 'Create an output sheet
> Set outsheet=Datatable.Addsheet("output")
> 'Loop through each field to get the name to
> 'create the datable parameters
> For each fld in adoRecordset.Fields
> outsheet.AddParameter fld.name,""
> Next
> i=1
> Do while Not adorecordset.EOF
> DataTable.GetSheet(outsheet.name).SetCurrentRow(i)
> For each fld in adorecordset.Fields
> outsheet.GetParameter(fld.name).value=fld.value
> Next
> adorecordset.MoveNext
> i=i+1
> Loop
>
>
> 'close the recordset object
> adorecordset.close
> 'Destroy the recordset object
> Set adorecordset = nothing
>
>
> 'close the database connection
> adoCon.close
> 'Destroy the connection object
> Set adoCon = nothing
>
> 'Connect to Oracle DB and execute a SQL Query, then write the data to
> 'the QTP runtime datatable.
>
> 'Create the Connection object
> Set adocon=CreateObject("ADODB.Connection")
> constr="Provider=OraOLEDB.Oracle.1;Password=xxxx;Persist Security
> Info=True;User ID=xxxx;Data Source=Dev"
> adocon.Open constr
> 'Execute the SQL query
> Set adorecordset=CreateObject("ADODB.Recordset")
> Set adorecordset.ActiveConnection=adocon
> Strsql="SELECT Segment1, Quantity FROM (Select Description,
> Enabled_Flag, A.Inventory_Item_Id, B.Inventory_Item_Id,
> Inventory_Item_Status_Code, Organization_Id, Segment1,
> Primary_Uom_Code, Primary_Unit_Of_Measure,(Case When B.Conversion_Rate
> Is Null Then 1 Else B.Conversion_Rate End) Quantity, B.Uom_Code,
> B.Unit_Of_Measure From Mtl_System_Items_B A Left Outer Join
> Mtl_Uom_Conversions B On (A.Inventory_Item_Id = B.Inventory_Item_Id)
> Where -1= -1 And (Organization_Id = 356 Or Organization_Id = 359
> Or Organization_Id = 591) AND (ITEM_TYPE =('LNV_FGM') OR ITEM_TYPE
> =('LNV_FGB'))And Enabled_Flag = 'Y' And Inventory_Item_Status_Code =
> 'Active' ORDER BY dbms_random.value ) WHERE rownum = 1"
> adorecordset.Source=Strsql
> adorecordset.Open
> 'Create the datatable to export the data
> 'Create an output sheet
> Set outsheet=Datatable.Addsheet("output1")
> 'Loop through each field to get the name to
> 'create the datable parameters
> For each fld in adoRecordset.Fields
> outsheet.AddParameter fld.name,""
> Next
> i=1
> Do while Not adorecordset.EOF
> DataTable.GetSheet(outsheet.name).SetCurrentRow(i)
> For each fld in adorecordset.Fields
> outsheet.GetParameter(fld.name).value=fld.value
> Next
> adorecordset.MoveNext
> i=i+1
> Loop
>
>
> 'close the recordset object
> adorecordset.close
> 'Destroy the recordset object
> Set adorecordset = nothing
>
> 'close the database connection
> adoCon.close
> 'Destroy the connection object
> Set adoCon = nothing
>
> Dim objIE
> Set objIE = CreateObject("InternetExplorer.Application")
> objIE.visible = True
> objIE.navigate "http://devap1.linvatec.com:8030/OA_HTML/
> AppsLocalLogin.jsp?requestUrl=APPSHOMEPAGE&cancelUrl=http%3A%2F
> %2Fdevap1.linvatec.com%3A8030%2Foa_servlets
>
> %2Foracle.apps.fnd.sso.AppsLogin&s2=CCC48C9F7A462B6500680579CCCFEB46A1937E84A75CCEAC13471DA4D4E81AB9"
> objIE.statusbar = 1
> Wait 3
> Browser("Oracle Applications
> Home").Page("Login").WebEdit("username").Set "xxxx"
> Browser("Oracle Applications
> Home").Page("Login").WebEdit("password").SetSecure "xxxxx"
> Browser("Oracle Applications Home").Page("Login").Image("Login").Click
> Browser("Oracle Applications Home").Page("Oracle Applications
> Home_4").Link("LNV Pricing Super User").Click
> Browser("Oracle Applications Home").Page("Oracle Applications
> Home_5").Link("Price List Setup").Click
> Browser("Oracle Applications 11i").Page("Oracle Applications
> 11i_2").Sync
> Browser("Oracle Applications Home").Page("Oracle Applications
> Home_5").Sync
> OracleFormWindow("Advanced
> Pricing").OracleTabbedRegion("Main").OracleTextField("Name").Enter
> "ADV ALL BLADES & BURS"
> OracleNotification("Note").Approve
> OracleFormWindow("Advanced Pricing").PressToolbarButton "Clear Record"
> DataTable.GetSheet("output").SetCurrentRow(1)
> OracleFormWindow("Advanced
> Pricing").OracleTabbedRegion("Main").OracleTextField("Name").Enter
> DataTable.GetSheet("output").GetParameter("PRICE_LST_NAME").Value
> OracleFormWindow("Advanced
> Pricing").OracleTabbedRegion("Main").OracleTextField("Description").Enter
> "QTP Test"
> OracleFormWindow("Advanced
> Pricing").OracleTabbedRegion("Main").OracleTextField("Effective
> Dates").OpenDialog
> OracleCalendar("Calendar").Enter "15-NOV-2010"
> OracleFormWindow("Advanced
> Pricing").OracleTabbedRegion("Main").OracleTextField("-").OpenDialog
> OracleCalendar("Calendar").Enter "15-APR-2011"
> OracleFormWindow("Advanced
> Pricing").OracleTabbedRegion("Main").OracleTextField("Multi-Currency
> Conversion").OpenDialog
> OracleListOfValues("Multi Currency Conversion").Select "Generated
> Currency Conversion For USD 2002"
> OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
> Lines").OracleTable("LIST_LINES").OpenDialog 1,"Product Context"
> OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
> Lines").OracleTable("LIST_LINES").OpenDialog 1,"Product Attribute"
> OracleListOfValues("Product Attributes").Select "Item Number"
> DataTable.GetSheet("output1").SetCurrentRow(1)
> OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
> Lines").OracleTable("LIST_LINES").EnterField 1, "Product Value",
> DataTable.GetSheet("output1").GetParameter("Segment1").Value
> OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
> Lines").OracleTable("LIST_LINES").EnterField 1, "Value", "35.00"
> OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
> Lines").OracleTable("LIST_LINES").OpenDialog 1,"Start Date"
> OracleCalendar("Calendar").Enter "15-NOV-2010"
> OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
> Lines").OracleTable("LIST_LINES").OpenDialog 1,"End Date"
> OracleCalendar("Calendar").Enter "15-APR-2011"
> OracleFormWindow("Advanced Pricing").OracleTabbedRegion("List
> Lines").OracleTable("LIST_LINES").SetFocus 1,"[ ]"
> OracleFlexWindow("Additional Info for List").OracleTextField("DM
> FLOOR").Enter "1"
> OracleFlexWindow("Additional Info for List").Approve
> OracleFormWindow("Advanced Pricing").SelectMenu "File->Save"
> OracleFormWindow("Advanced Pricing").SelectMenu "File->Exit Oracle
> Applications"
> OracleNotification("Caution").Approve
> Browser("Oracle Applications 11i").Page("Oracle Applications
> 11i_2").Sync
> Browser("Oracle Applications 11i").CloseAllTabs
> Browser("Oracle Applications Home").Page("Oracle Applications
> Home_5").Link("Logout").Click
> Browser("Oracle Applications Home").Page("Login_2").Sync
> Browser("Oracle Applications Home").CloseAllTabs
>
>
>
>
> Any help is appreciated
>
> --
> You received this message because you are subscribed to the Google
> "QTP - HP Quick Test Professional - Automated Software Testing"
> group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]<mercuryqtp%[email protected]>
> For more options, visit this group at
> http://groups.google.com/group/MercuryQTP?hl=en
--
RAJIV KUMAR
<http://rajivkumarnandvani.wordpress.com/>
<http://learnqtphelp.blogspot.com/>
--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/MercuryQTP?hl=en
