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"  wrote in message 
news:1185569469.468787.10970@w3g2000hsg.googlegroups.com...

>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"
>