Solved

Passing a parameter to a SQL statement with Oracle Database

Posted on 2008-10-03
3
541 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

911 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