This content has been marked as final. Show 5 replies
Two possible reasons I can think of off the top of my head (though unlikely). You use DB Change Notification (aka Continuous Query Notification). This would exhibit the behavior you describe if you are not capturing a change that alters the schema on the client. However, I find this doubtful because using DCN is not trivial. You would know you were using it if you were the code developer.
The other reason is that you are using edition based redefinition and you are still using the old edition. This is doubtful too if you are querying a table. Views are editionable, but not yet tables.
If neither of these sound fruitful, try using the Oracle Developer Tools to perform the same query and see what results you get. ODT uses ODP.NET underlying it for data access.
I was thinking it was the BindByName Property too.
Although you don't say what you mean by "invalid results" - an error ? Or are the results returned with wrong data?
In any case if you have not set BindByName =true for your command object then it will default to position.
If you want to use bind by position the parameters have to be in the right order as Eidercom says.
Thanks for all your efforts. After 5 hours of trying a million different things, I found the problem.
It's wasn't the need for BindByName, as that's bitten me before, and I had that in place. And by invalid data, I mean the right results were not returned to the .Net app.
I am very new to Oracle. I have 14 years of experience with MS SQL Server and DB2 databases. My Oracle training with PL/SQL was over a year ago, and I've used it very little since then, and only SELECT statements, not INSERTs or UPDATEs. But late in the day a co-worker reminded me that an update has to be followed by a Commit, which I was not doing. So, in SQL Developer I had changed a few records in a test account, then did my query to check that my where clause worked as expected, and of course, it did. But until the Commit was done, those changes were not visible outside that session, so the .Net app could not see those changes to the records. Once I did the Commit, of course it would see them and my query was validated.
I will spare you a rant against Oracle about if I wanted to be able to roll back a change I'd explicitly use Begin Transaction and Commit/Rollback commands before and after the update, as it works in every other popular database out there like SQL Server, DB2, MySQL, etc. But since it wouldn't do any good, I'll spare you all. :-)