Invalid Parameter Binding error when using Oracle ODP.NET TableAdapter

If you use Oracle ODP.NET in Visual Studio 2010 (or earlier version) to create a DataTable by using TableAdapter wizard, you may get an Invalid Parameter Binding error if there is a parameter in your query. For example, this simple select query below can cause the error:

select * from Person where personId=:person_uid

The cause is strange because ODP.NET cannot recognize the parameter’s DbType and thus sets the DbType of the parameter to Object, so the fix is simple: just change the DbType from Object to Int32 or the correct DbType.

In Visual Studio, right click the query of the TableAdapter, then click Properties. From the Properties window, click Parameters drop down list, then change the DbType of the parameter from Object to Int32 (or the correct DbType in your application):

Oracle_Invalid_Parameter_Binding

After the change, the error will go away. However, if you ever change the query, you will need to remember to change the DbType again because ODP.NET will set it to Object again.

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Joe G. Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Joe G.
Guest
Joe G.

Thank you thank you!!! I spent hours and hours trying to figure this out. You REALLY saved me. Thanks!