Pass parameter to SQL query from excel data connector

Technology specific object identification, supported applications, web technologies, and 3rd party controls.
vivek.guvva
Posts: 31
Joined: Mon Feb 22, 2016 7:21 pm

Pass parameter to SQL query from excel data connector

Post by vivek.guvva » Wed Apr 06, 2016 10:37 pm

Hi, I have a scenario where I need to run the same SQL query with different values. I made the dynamic value in the query as parameter and pass value to that parameter using excel data connector. Below is the same query that i have used. This query is not working when I enter "weekID" values in the excel sheet and run.

Dim weekID as Int32
select * from table1 where col1 = " & weekID & "

Parameter value is not passing to the query if I use excel data connector. But if I pass value to the variable "weekID" in the code it self, then is is working. Below query is working

Dim weekID as Int32 = 201613
select * from table1 where col1 = " & weekID & "

Please let me know how can i pass weekID parameter to the query using excel data connector. I am using odbc data connector for executing the query.

Thanks
Vivke

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 7470
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Pass parameter to SQL query from excel data connector

Post by odklizec » Thu Apr 07, 2016 7:16 am

Hi,

The thing is, that the data connectors always pass the values as string! So if there is expected int in your query, you need to convert the variable obtained from data connector to int, for example like this:
int weekIDInt = 0;
Int32.TryParse(weekID, out weekIDInt);
Where weekID should be defined as recording/module variable and filled via data connector.
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

vivek.guvva
Posts: 31
Joined: Mon Feb 22, 2016 7:21 pm

Re: Pass parameter to SQL query from excel data connector

Post by vivek.guvva » Thu Apr 07, 2016 12:52 pm

Hi,

Thank you for your reply. I have tried parsing but that didn't workout. I have attached my code here. Could you please take a look at this and let me know what is the issue. Database connection is establishing but parameter value is not passing to the query in all the iterations. I am really struggling with this. Thanks for your help again.

My input sheet will have all the parameters as columns and it's values as rows.

Thanks
Vivek
Last edited by vivek.guvva on Thu Apr 07, 2016 7:11 pm, edited 1 time in total.

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 7470
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Pass parameter to SQL query from excel data connector

Post by odklizec » Thu Apr 07, 2016 1:20 pm

Hi,

I'm sorry, but I can't help you with the VB code.

BTW, have you tried to debug your code and examine the content of weekID before and after parsing it to int32? I mean, are you sure the weekID is correctly binded to a data connector and that it contains expected value? Also, don't forget that if there are some data obtained from data connector, you need to run entire test suite, not just a single recording/code module!
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration