Solved

Passing a parameter to a SQL statement with Oracle Database

Posted on 2008-10-03
3
534 Views
Last Modified: 2013-12-18
Hello,
I am using an Oracle Database.
I am trying to pass a parameter to the SQL statement.

My orginal SQL which gets the records is as follows:
SELECT rug.user_group_id, rug.user_group_desc, rh.code_hotel
FROM rpt_user_groups rug, rpt_user_group_xref rugx, rpt_hotels rh
WHERE rugx.rpt_user_group_parent_id in 1 <-------------------i have to pass a parameter here instead of 1
AND rugx.rpt_user_group_child_id = rug.user_group_id
AND rug.user_group_id = rh.user_group_id(+) ORDER BY rug.user_group_desc,rug.user_group_id

i am trying like below:

Protected Sub RadTreeView1_NodeExpand(ByVal sender As Object, ByVal e As RadTreeNodeEventArgs) Handles RadTreeView1.NodeExpand
Dim oradb As String = "Data Source=(DESCRIPTION=(ADDRESS_LIST=" _
                                 + "(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)))" _
                                 + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=something)));" _
                                 + "User Id=something;Password=something;"
 Dim conn As New OracleConnection(oradb)
        conn.Open()

Dim sql As New OracleCommand("SELECT rug.user_group_id, rug.user_group_desc, rh.code_hotel FROM rpt_user_groups rug, rpt_user_group_xref rugx, rpt_hotels rh WHERE rugx.rpt_user_group_parent_id in @ParentId AND rugx.rpt_user_group_child_id = rug.user_group_id AND rug.user_group_id = rh.user_group_id(+) ORDER BY rug.user_group_desc,rug.user_group_id", conn) ' VB.NET

        sql.Parameters.Add("@ParentId", e.Node.Value)
        Dim adapter As New OracleDataAdapter(sql)  --- my parameter is not replaced with '1'
Dim data As New DataTable()
adapter.Fill(data)    <----- fails here with message (An exception of type 'Oracle.DataAccess.Client.OracleException' occurred in Oracle.DataAccess.dll but was not handled in user code.Additional information: External component has thrown an exception.)

End Sub


when i execute the query i get a error pop-up:  ORA-00936  missing expression

any help would be greatly appreciated.
anagm



     
0
Comment
Question by:anagm
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Priest04 earned 500 total points
ID: 22638500
I am not very familiar wit Oracle database, but according to this article

http://download.oracle.com/docs/cd/B19306_01/win.102/b14307/OracleParameterClass.htm

parameter sign is not @, but is :1, :2, :3... etc

Dim sql As New OracleCommand("SELECT rug.user_group_id, rug.user_group_desc, rh.code_hotel FROM rpt_user_groups rug, rpt_user_group_xref rugx, rpt_hotels rh WHERE rugx.rpt_user_group_parent_id in :1 AND rugx.rpt_user_group_child_id = rug.user_group_id AND rug.user_group_id = rh.user_group_id(+) ORDER BY rug.user_group_desc,rug.user_group_id", conn) ' VB.NET

also I am not sure if this is the correct Oracle syntax

WHERE rugx.rpt_user_group_parent_id in 1

in SQL server, it would be

WHERE rugx.rpt_user_group_parent_id = 1

try to declare parameter with all attributes. Someting like

OracleParameter prm = new OracleParameter("ParentId",OracleType.VarChar); // change to according type
prm.Direction = ParameterDirection.Input;
prm.Value = "1";
sql.Parameters.Add(prm);

Goran
0
 

Author Comment

by:anagm
ID: 22638584
It worked when i gave ':ParentId' instead of '@ParentId'
Dim sql As New OracleCommand("SELECT rug.user_group_id, rug.user_group_desc, rh.code_hotel FROM rpt_user_groups rug, rpt_user_group_xref rugx, rpt_hotels rh WHERE rugx.rpt_user_group_parent_id in :ParentId AND rugx.rpt_user_group_child_id = rug.user_group_id AND rug.user_group_id = rh.user_group_id(+) ORDER BY rug.user_group_desc,rug.user_group_id", conn)


sql.Parameters.Add(":ParentId", e.Node.Value)
0
 

Author Closing Comment

by:anagm
ID: 31502936
thank you, Goran.
anagm
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now