QTP Database conenction and parameterization of the values

[siri]

Hi the above one is the script where i specified the connection string but it is not working properly .An error is populating like

"[Microsoft][ODBC driver for Oracle][Oracle]ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor"

[somisays]

Hi,
Create a DSN and use that is the script.

Create a Data Source name by following these steps.
Control Panel...>Administrative Tools...>Data Sources(ODBC)
Click on Add...>Select Driver For oracle

[siri]

I have created a DSN as you said .

Code:

Conn.Open("DSN=devmitg,username=xxx,password=yyy") It is showing me the error like the dsn name is too large The above is the connection string i am using Conn.Open("DSN=devmitg")

the same error i posted TNS could not resolve the service _name

[somisays]

Dear Siri,
I corrected your code.Try this.

Code:

Conn.Open("DSN=devmitg;username=xxx;password=yyy;")

[siri]

Hi Sridhar ,

Can i get ur contact nubmer so that i can briefly explain my problem

See this is the code
modified one :

Code:

Set Conn = CreateObject("ADODB.Connection") Set Rs = CreateObject("ADODB.recordset") Conn.Open("DRIVER={Oracle in OraHome92};SERVER=DEVMITG;UID=ITL_ASSET;PWD=ITL_ASSET;DBQ=DEVMITG") ' Conn.Open("DSN=devmitg;UID=ITL_ASSET;PWD=ITL_ASSET"); ' Specify the query' sql = "Select Code, Meaning From TB_AMS052_Codes Where Code_Nam = Pole" 'Set the recordset cursor type’ Rs.CursorType = 1 'Execute the Query’ 'Rs.Open sql,Conn 'Get the no records returned by query’ Recordcount = Rs.RecordCount 'Adding a column to datatable’ DataTable.GlobalSheet.AddParameter "Meaning" 'Get the values from the Meaning column’ While(NOT Rs.EOF) Msgbox Rs.Fields("Meaning") For i=1 to Recordcount DataTable.SetCurrentRow(i) 'Writing the data to the datatable’ DataTable("Meaning",DtGlobalSheet) = Rs.Fields("Meaning") Rs.MoveNext Next wend 'close the database connection’ DataTable.Export "C:\DatabaseExample.xls" Conn.close Set Conn = Nothing Set Rs = Nothing

'

[somisays]

Hi,
That code looks perfectly fine for me.
If you srill having a problem that means check whether oracle client installed on your machine or not.

I live in London,UK.

or try the following connection string...

Code:

strCon = "Driver={Microsoft ODBC for Oracle}; " & _ "CONNECTSTRING=(DESCRIPTION=" & _ "(ADDRESS=(PROTOCOL=TCP)" & _ "(HOST=hostname)(PORT=1526))" & _ "(CONNECT_DATA=(SID=sidname))); uid=report;pwd=report;

[siri]

Hi Sridhar,

I am not getting problem with the Oracle conenction getting problem with the execution of the query where you have specified like
'Rs.Open sql,Conn
I am not able to catch the values fro mthe query i will send you the ppt to ur mail id so that u can understand much better

[siri]

Hi sridhar ,
I have sent you mail regarding the connection and my requirement . The word doc con tains all the neccessary things .
Waiting for your reply

[somisays]

Hi Siri ,

Modified Code...

Code:

Set Conn = CreateObject("ADODB.Connection") Set Rs = CreateObject("ADODB.recordset") Conn.Open("DRIVER={Oracle in OraHome92};SERVER=DEVMITG; UID=ITL_ASSET;PWD=ITL_ASSET;DBQ=DEVMITG") ' Specify the query' sql = "Select Code, Meaning From TB_AMS052_Codes Where Code_Nam = Pole" 'Set the recordset cursor type’ Rs.CursorType = 1 'Execute the Query’ Rs.Open sql,Conn 'Get the no records returned by query’ Recordcount = Rs.RecordCount 'Adding a column to datatable’ DataTable.GlobalSheet.AddParameter "Meaning","" DataTable.GlobalSheet.AddParameter "Code","" 'Get the values from the Meaning column’ While(NOT Rs.EOF) 'Msgbox Rs.Fields("Meaning") For i=1 to Recordcount DataTable.SetCurrentRow(i) 'Writing the data to the datatable’ DataTable("Meaning",DtGlobalSheet) = Rs.Fields("Meaning") DataTable("Code",DtGlobalSheet) = Rs.Fields("Code") Rs.MoveNext Next wend 'close the database connection’ DataTable.Export "C:\DatabaseExample.xls" Conn.close Set Conn = Nothing Set Rs = Nothing

Please use the above code and i tried this on my computer it is working so please let me know any problem.

[siri]

Hi Sridhar ,

When i am executing the script i am getting error at Rs.Open sql,Conn
please check this .