OracleDataAdapter not populating DataTable from request
I am writing a C # application for SQL Server and Oracle databases which are defined in the application config file. When I run the code in SQL Server environment, it works fine. I am getting correct results.
The working SQL Server code is here:
sqlConn.Open();
stmt = "SELECT ACTION_ID, ACTION FROM DB.ACTIONS WHERE ACTION_DATE < GETDATE() AND STATUS = 'Pending'";
SqlDataAdapter sqlAdapt = new SqlDataAdapter(stmt, sqlConn);
sqlAdapt.Fill(dt);
sqlConn.Close();
The Oracle code that doesn't work looks like this:
oraConn.Open();
stmt = "SELECT ACTION_ID, ACTION FROM DB.ACTIONS WHERE ACTION_DATE < SYSDATE AND STATUS = 'Pending'";
OracleDataAdapter oraAdapt = new OracleDataAdapter(stmt, oraConn);
oraAdapt.Fill(dt);
oraConn.Close();
I am trying to populate a datatable dt with results. I first tried to use a DataSet and populated the DataTable with a DataSet, but that didn't work in Oracle when it did in SQL Server. There seems to be something simple missing.
I don't get any errors, I just get 0th result from Oracle even though I know there is data in there.
source to share
Run the query directly in the DB (for example using sqlplus, TOAD, SQLDEveloper, etc.). Does he return something? If not:
a) In SQL Server comparison, comparison (STATUS = 'Pending') is case insensitive in Oracle, it is case sensitive. Maybe you have data in a status column like "PENDING"?
b) Is ACTION_DATE a numeric date type? If it's varchar, it won't work.
Hope it helps.
source to share
My suggestion is to change the code to split the diagnostic layer.
1) replace the statement with a simple "select sysdate from dual"; to see if there is any line return.
2) If nothing comes back, it might be a connection issue. view the connection property.
3) if something is returned but not populated in the dataset, try using an oracle reader to return the result and actually see the result.
Hope this helps you.
source to share