0

I am trying to populate a series of dropdown lists in a GridView. In the RowDataBound event, I call based on other info in the gridviewrow to get the items for populating dropdowns in the row. I am getting "ORA-01036: illegal variable name/number error on parameter" on the following code.

Dim rowTemp As GridViewRow = ddlItemDesc.Parent.Parent

Dim lblYear As Label = rowTemp.FindControl("lblSchoolYear")
Dim strYear As String = lblYear.Text.Trim()

Dim strSelect As String = "SELECT FOO.BAR.PRODUCT_CODE || ' :: ' || FOO.BAR.PRODUCT_DESC AS txt, FOO.BAR.PRODUCT_KEY AS val, :currentYear AS CURRENT_YEAR FROM FOO.BAR WHERE (FOO.BAR.PRODUCT_KEY=:itemKey)"

Dim daTemp As New OracleDataAdapter(strSelect, oConn)   
daTemp.SelectCommand.Parameters.Add("currentYear", CDate(strYear))
daTemp.SelectCommand.Parameters.Add("itemKey", strItemVal(0))
Dim dtProd As New DataTable
daTemp.Fill(dtProd)

If I remove ":currentYear AS CURRENT_YEAR" from the SELECT statement, it runs fine. I use the same statement in another SELECT from a join of other tables with no problem.

The strYear comes from a label in the gridviewrow and I have checked that it has data and that it is valid for CDate (I've also tried it simply as a string with the same results). StrItemVal(0) comes from another dropdownlist (the one that is successfully populated using :currentYear being passed in). This code only gets called if that dropdown list has a value in it that is selected.

monkeypushbutton
  • 196
  • 1
  • 9
  • 21
  • 2
    Why would you bother passing in a parameter which is just going to come back in the results, but which isn't part of the query itself? – Jon Skeet Dec 07 '12 at 14:55
  • 1
    @Jon Skeet It gets fed into a subroutine that populates the dropdownlist. Other times, the table used has multiple year's options. Probably should just write one for only one year but I got so caught up in trying to get this to work with the exisiting one I didn't even think about it not being necessary. That said, as I am still learning Oracle and wonder why it worked before and not this time. – monkeypushbutton Dec 07 '12 at 15:10

0 Answers0