Forum Discussion

baba_k's avatar
baba_k
Occasional Contributor
13 years ago

Getting error while trying to execute SQL statement through script

Hi,

I am Getting an exception while trying retrieve data from database through script. Below is the error message and Query we are using in the script :

Error Message- “Conversion failed when converting the varchar value 'B03001' to data type int”


Query-Select count(*) as count from aq1orderitem where awo_id="&ProjectSuite.Variables.NewAWO&" and item_type = 'POSL'


Where the AWO in DB is of Varchar type. We are capturing the AWO from another script and while capturing that AWO into project suite variables we tried to create&save the variable as  String/Integer . I tried with convert functions but there is no function to convert from String/Integer to varchar.

Below is the additional script:


aqConvert.IntToStr(ProjectSuite.Variables.NewAWO)




Set Qry = ADO.CreateADOQuery


 Qry = .CreateADOQuery

' Specify the connection string


 



Qry.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Agresso_QA;Data Source=GLNWD021\QA"



' Specify the SQL expression


 



Qry.SQL = "Select count(*) as count from aq1orderitem where awo_id="&ProjectSuite.Variables.NewAWO&""



' and item_type = 'POSL'"


' Execute the query


 



Qry.Open



' Process results and insert data into the test log


 



Qry.First


Qry.Open



' Process results and insert data into the test log


 



Qry.First



While Not Qry.EOF


 Qry.EOF

Log.Message Qry.FieldByName("count").Value


.Message Qry.FieldByName("count").Value

Qry.Next

Wend



' Closes the query


 




Qry.Close



Please help me in resolving this issue.



Qry = .CreateADOQuery Qry.EOF.Message Qry.FieldByName("count").Value

2 Replies

Sort By
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    13 years ago
    Note that the error you're getting is coming back from SQL server, not from the TestComplete software.



    What I would do first of all is put a breakpoint on the following line



    Qry.SQL = "Select count(*) as count from aq1orderitem where awo_id="&ProjectSuite.Variables.NewAWO&""




    And inspect the value of the query in your watch list to see how it actually appears.  I would then take that value to your query tool for your actual database and see what happens when you run it there.



    I'm guessing, though, that awo_id column is the varchar column in question.  The problem is that your particular query is just passing the value of your variable to the string without wrapping it in the necessary quote characters.  Something you might try is this.



    Qry.SQL = "Select count(*) as count from aq1orderitem where awo_id='"&ProjectSuite.Variables.NewAWO&"'"




    Note that I added single quotes before and after your variable to make sure that the query is passed in as an actual string parameter.



    See if that works for you.
  • baba_k's avatar
    baba_k
    Occasional Contributor
    13 years ago
    Hi Martin,

    Thanks for the suggestion and it worked...