"cptkirkh" <khill@scic.com> 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"
>