I have a query that looks up the highest value for column a and then
returns column a and column b. It uses a nested select in the from
clause. I want to use bind variables for my parameters but have found
out i can't use bind variables in the from clause. It keeps returning
no records when i can run this same query in toad and it returns
records. My question is can anyone help me wiht how to do this? Here
is my code:

Dim con As New OracleConnection(cnCICString)
Dim cmd As New OracleCommand
Dim da As OracleDataAdapter = New OracleDataAdapter(cmd)
con.Open()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "select ec.ec_export_number,ec_date from
event_campaign ec, " & _
" (select max(ec_export_number) as maxexport from
event_campaign" & _
" where EC_PURPOSE = :event_purpose AND EC_ITEM_SENT
= :item_sent" & _
" and EC_SQL = :sql_totalcount ) maxresults where " & _
" ec.ec_export_number = maxresults.maxexport and EC_PURPOSE
= :event_purpose AND EC_ITEM_SENT = :item_sent " & _
" and EC_SQL = :sql_totalcount"

cmd.Parameters.Add(":sql_totalcount", OracleDbType.Varchar2).Value =
"select cli_rid from dual"
cmd.Parameters.Add(":event_purpose",
OracleDbType.Varchar2).Value = "20070511TXAM"
cmd.Parameters.Add(":item_sent", OracleDbType.Varchar2).Value
= "POSTCARD"

Colon's are only required in the SQL statement, remove them from the
parameter name argument passed to the Add method.

"cptkirkh" <khill (AT) scic (DOT) com> wrote