ODP.NET OracleDataReader returns no rows on server (data is returned fine when running locally in Visual Studio)

Hi, I've recently installed ODP.NET and got it working fine on my machine. I can connect to the server, run a query and return data.

So, I tried to publish the website on the server to see if it would work there. And I'm having problems. It seems that currently I can connect to a table but that no rows are returned from the table? So, I can run the exact same code on my pc in visual studio and everything runs fine... but when i publish it to the server, no data is returned in the datareader.

Any ideas?
AidenAAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
No error (like an ORA-00942) and no rows means you are connecting and looking at a valid table and no rows exist.  There really isn't anything else that can cause that.

>>And I also know the other databases which exist on that database.

Typo or SQL Server terms applied to Oracle?  I hope you mean schemas.

Do you have admin access to the database?  When your app connects from the web server connect to the database using sqlplus and issue:
column machine format a20
column program format a20
select machine, program, count(*) from v$session group by machine, program;

then look for connections from the web server.
0
 
slightwv (䄆 Netminder) Commented:
Are both machines connecting to the same database and same schema in the same database?

Verify the tnsnames.ora file has the same entries on both machines.  If you connect and do not get any rows, odds are you aren't looking where you think you are.
0
 
AidenAAuthor Commented:
well, i've checked all the tnsnames.ora on both machines and they're both the same. At the moment I have two oracle homes on each computer (I think? mine and the server) after the installation of ODP.NET. So, as I don't know which one is being referenced I just copied across the tnsnames.ora file from the previous home into the new ODP.NET home. Anyway, they all look the same. well, except the ones on the server have

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


at the bottom after the line. The first line is on both local pc and on server, while the second line is just on the server. Funny thing is, I was getting an uable to resolve tnsnames error while connecting which then mysteriously disappeared... and now i get no error while connecting, but still the OracleDataReader is empty.

The thing is, if I change the table name to something that I definitely know is not there, or anywhere else, then I get the error Oracle.DataAccess.Client.OracleException ORA-00942: table or view does not exist. So, it looks like it's finding the table?

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
slightwv (䄆 Netminder) Commented:
>>(I think? mine and the server)

Now separate machines.  More than one Oracle product installed on the same machine.

>>So, it looks like it's finding the table?

I agree.  That's why I posted "If you connect and do not get any rows, odds are you aren't looking where you think you are."

Is it finding the correct table?  It might be looking in a different schema and/or different database.
0
 
AidenAAuthor Commented:
but i get an error when i change the table name? so surely it's the same database and schema?

but actually, i tried to run an update query and same thing.. nothing happened but no error either?

i'm pretty stuck here, not sure what to do with this...
0
 
slightwv (䄆 Netminder) Commented:
>>so surely it's the same database and schema?

Can you not have two databases with the same table names or two database schemas with the same table names?

>>i tried to run an update query and same thing

With no rows being returned, Oracle doesn't see any rows in the table, what is there to update?
0
 
AidenAAuthor Commented:
i'm not sure i get you. I can set a table name to such a value that I know it does not exist in any other database. Therefore, I know that the table does not exist in any other database or schema on the server. And I also know the other databases which exist on that database. So, I am sure that is the only table with that name...
0
 
AidenAAuthor Commented:
question marked as abandoned, but I'll accept when other question solved as presumably it will solve this...
0
 
AidenAAuthor Commented:
ok thanks for all help on other question... i'll accept this without adding to KB as other question deserved more points anyway!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.