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 .