Roman, Thanks for the input and I appreciate the guidance. I am new to QTP myself "<6months" and obviously still learning many of the features and quirks of the software. The problem lies in that I am trying to get something to work and then refine the process as I go. As I gather more experience I am sure I will look back at these beginning efforts and cringe! I have a system that works that in addition to the code above creates an excel sheet and exports the datatable data and then reads the data from that sheet. I was hoping to be able to eliminate those steps and just use the runtime datatable data. As such I got some help from a previous post and am so close to making it work that I can taste it.
The first SQL query returns data that I can read with QTP it is just the second query that throws an error. I know the sheets exist, I know the parameters exist I am just to dumb to know what I am doing wrong. Anyway thanks again for your input if you don't mind I will take your rules and use them as the basis of my own list of rules. Ala Gibbs rules on NCIS!! LOL Jim On Jan 28, 9:45 pm, Roman Zilber <[email protected]> wrote: > I don't work right now with QTP, but when I did, I defined to myself > number of rules, here are three of them: > > 1. if you can, don't use build in QTP Excel support, use COM Excel > Automation insteadhttp://www.google.ca/search?q=vbscript+Excel+Automation > 2. if you can, don't use actions, use functions libraries > 3. don't use QTP report > > There are many arguments why to do that way :) I only don't want to > start holy wars. > Now I wouldn't use Excel at all, some simple DB, as SQLite, can serve > the purpose better. > > > > > > > > On Fri, Jan 28, 2011 at 1:41 PM, 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 "/ > > 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 > > ... > > read more » -- 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
