Using Odbc driver query given result in database but not filling datatable in c#, there is no error-CodePudding

Just started using windows 11 and installed Oracle drivers for 32Bit and 64Bit, wrote program using C# to fetch data from Oracle database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data.Odbc;
using System.Data;
using System.Data.SqlClient; 
 
namespace ApexAutoEmailConsol
{
    static class ServiceLog
    {
        static String connectionString = "Dsn=Prod21_32;uid=ebseb;pwd=ebseb";
        static string strQuery = string.Empty;
         
        public static string OutstandingInvoices()
        {
            try
            {
                OdbcConnection oCon = new OdbcConnection();
                oCon.ConnectionString = connectionString;
                oCon.Open();

                DataTable dtSales = new DataTable();

                strQuery = "SELECT * from apps.org_organization_definitions HO";
               // if I run above query in Toad it's giving result.
                OdbcDataAdapter myAdp = new OdbcDataAdapter(strQuery, oCon);
                myAdp.Fill(dtSales);
                
                //Adapter not filling data to the datatable.
                if (dtSales.Rows.Count <= 0)
                {
                    return "";
                }
 
                return strReturn;
            }
            catch (Exception Ex)
            {
                WriteErrorLog(Ex.ToString());
                return "";
            }
        }    
}

When I copy strQuery and run on Toad, getting result but datatable is still empty.

What is the problem? The same code is working perfect on my Windows10 machine.

CodePudding user response：

UnCOMMITted data is only visible within the session that created it (and will ROLLBACK at the end of the session if it has not been COMMITted). If you can't see the data from another session (C#) then make sure you have issued a COMMIT command in the SQL client (Toad).

If you have issued a COMMIT and still can't see the data then make sure that both the SQL Client (Toad) and the C# program are connecting to the same database and are querying the same user's schema of that database.

CodePudding user response：

It's very unique problem, I had it around 2 years ago with my another machine, where I was not able to get some query result in Toad. Some queries are working but some of with specific table in joing was giving empty result. That time I added following language setting in my environment variable and was worked. NLS_LANG = American_America.UTF8

Used same in my new machine and now am getting result with Visual Studio 2022.