locked
TableAdapter parameterized query using ODP.NET RRS feed

  • Question

  • Hopefully someone can help me on this. I'm normally an MSSQL and LINQ-to-SQL guy but now I have the misery pleasure of working on a project with an Oracle 10g database from another department.

    I've installed ODP.NET (Oracle's data provider) and successfully created an ADO.NET DataSet and TableAdapter in my project's code. The default Fill query on my TableAdapter work just fine.

    I'm trying to add another query to return filtered results (the table has an enormous number of records, takes around 20 seconds to execute) but I can't seem to get the parameter to work. Basically I'm querying for course information and I want to filter by term. The normal query works. In my new query I added "WHERE term_code_key = :termCodeFilter" to the end of the query and the designer automatically added :termCodeFilter to the parameter list. I changed my code-behind for my main window to use the new query. When I run the program and use that query, Oracle gives me an error saying "Invalid parameter binding, Parameter name: :termCodeFilter." (MSSQL would use "@" while Oracle uses ":", apparently.) Obviously I'm not setting something up correctly.

    Any hints or thoughts would be greatly appreciated. Unfortunately I have very little ADO.NET or Oracle experience.
    Tuesday, December 1, 2009 8:46 PM

Answers

  • You are using the correct parameter  :parm_name for your Oracle Parameter


    John
    Tuesday, December 1, 2009 9:24 PM
  • I figured out it. There was nothing wrong with my query, just that in the DataSet designer the parameter was set to be of type Object and it needed to be of type String. It works now.
    • Marked as answer by mott555 Thursday, December 3, 2009 4:17 PM
    Thursday, December 3, 2009 4:17 PM

All replies

  • You are using the correct parameter  :parm_name for your Oracle Parameter


    John
    Tuesday, December 1, 2009 9:24 PM
  • I figured out it. There was nothing wrong with my query, just that in the DataSet designer the parameter was set to be of type Object and it needed to be of type String. It works now.
    • Marked as answer by mott555 Thursday, December 3, 2009 4:17 PM
    Thursday, December 3, 2009 4:17 PM