SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 2 of 2
  1. #1
    Senior Member
    Join Date
    Jun 2005
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Picking SQL Query from Excel Sheet and Executing in QTP

    hi all,
    I want get SQL query in run time from excel sheet
    like this:
    </font><blockquote><font size="1" face="Verdana, Arial, Helvetica">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">Set XL = CreateObject(&quot;Excel.Application&quot
    XL.DisplayAlerts = False
    Set oWb = XL.Workbooks.Open(&quot;D:\ETL\Resources\tagDBMapw ithQueries.xls&quot
    query=Trim(XL.WorkSheets(&quot;Query&quot.Cells((&quot;1&quot,(&quot;C&quot).value)</pre><hr /></blockquote><font size="2" face="Verdana, Arial, Helvetica">and then execute it like this;
    </font><blockquote><font size="1" face="Verdana, Arial, Helvetica">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">strConn=&quot;DSN=&quot;&amp;dsnName&a mp;&quot;;UID=&quot;&amp;userId&amp;&quot;;Passwor d=&quot;&amp;Password&amp;&quot;;&quot;
    Set oConn=CreateObject(&quot;ADODB.Connection&quot
    oConn.Open strConn

    Set RsSqlQuery= oConn.Execute(query)</pre><hr /></blockquote><font size="2" face="Verdana, Arial, Helvetica">There are 2 problems

    Problem # 1

    I get this error "[Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with 'Select Matter.title,Matter.matterTypeDescription,Matterty pe.fullDesc,Mattertype.description,
    Matter.govContractNo,Matter.matterM' is too long. Maximum length is 128." When i execute the Query.

    Please note that when I paste the query in QTP :
    Like this:
    </font><blockquote><font size="1" face="Verdana, Arial, Helvetica">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">query=&quot;Select Matter.title,Matter.matterTypeDescription,Matterty pe.fullDesc,Mattertype.description,&quot;&amp;_
    &quot;Matter.govContractNo,Matter.matterMgmtNo,Mat ter.abstract,Matter.stagedescription,&quot;&amp;_
    &quot;Matter.countryname,Matter.matterid,Matter.cu stomstatusid,&quot;&amp;_
    &quot;Patent.exportControlled,Matter.fileddate,Pat ent.issueDate,&quot;&amp;_
    &quot;Matter.datePurchased,Matter.fmtSerialNo,Matt er.confirmationNo,Matter.class,&quot;&amp;_
    &quot;Patent.allowedDate,Patent.independentClaims, Patent.abandonedDate,Patent.selfClassified,&quot;& amp;_
    &quot;Patent.issuedate,Patent.fmtpatentNo,patent.a rtUnitNo,Matter.subclass,Patent.expirationDate,&qu ot;&amp;_
    &quot;Patent.totalClaims,Patent.RCEOccured,Patent. priorityDate,Patent.pctAppDate,&quot;&amp;_
    &quot;Patent.projectedPubDate,Patent.pubNo,Matter. datePurchased,Matter.otherinfo,&quot;&amp;_
    &quot;Patent.priorityAppNo,Patent.pctAppNo,Patent. pubDate,Matter.Otherinfo &quot;&amp;_
    &quot;FROM Matter INNER JOIN Patent ON Matter.matterId = Patent.matterId &quot;&amp;_
    &quot;INNER JOIN MatterType ON Matter.subMatterTypeID = MatterType.MatterTypeID &quot;&amp;_
    &quot;Where Matter.matterTypeId IN (1,200,202,206,208,209) &quot;&amp;_
    &quot;And (Matter.creatorOrgId = &quot; &amp; org_id &amp; &quot &quot;&amp;_
    &quot;And Matter.Matterid IN (Select matterid from matter where countryname='PCT') &quot;&amp;_
    &quot;And Matter.matterid &gt;=38700 ORDER BY Matter.matterid;&quot;</pre><hr /></blockquote><font size="2" face="Verdana, Arial, Helvetica">It works fine.


    Problem # 2
    I need to get the value of Matter.creatorOrgId
    (Matter.creatorOrgId = " & org_id & ") from a vbs file. I have defined the variable org_id there.


    Hence I have to get the query from excel then get the value of org_id from vbs file place it properly in query and then execute the query.

    Any idea how couuld this be done.

  2. #2
    Senior Member
    Join Date
    Oct 2005
    Location
    Universe
    Posts
    393
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Picking SQL Query from Excel Sheet and Executing in QTP

    Hi,

    Here is solution for the problem #1.

    You will have to create a Recordset Object too like this.

    Set ObjRecordset=CreateObject("ADODB.Recordset")

    Your Modified code will look like this.
    --------------------------------------------------
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adUseClient = 3
    strConn="DSN="&dsnName&";UID="&userId&";Password=" &Password&";"
    Set oConn=CreateObject("ADODB.Connection")
    Set ObjRecordset=CreateObject("ADODB.Recordset")
    oConn.open strConn
    objRecordset.CursorLocation = adUseClient
    objRecordset.CursorType = adopenstatic
    objRecordset.LockType = adlockoptimistic
    objRecordset.Source=query
    Set objRecordset.activeconnection=oConn
    objRecordset.open
    --------------------------------------------------

    I am looking at your second problem.

    Thanks,
    Wasim

 

 

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

Copyright BetaSoft Inc.