SQL Adapter.Fill(dataTable) fetches wrong rows

Question

I've encountered a problem in my class for fetching SQL (Ingres.Client lib) data.

SQL query select works fine in SQL DBA client (VDBA) for Ingres Database. Rows gets fetched just right. But my code return shifted data, wrong rows.

E.g. Im trying to fetch data where the DATE is BETWEEN two dates but it returns data that is clearly outside the select scope.

SQL CLASS

internal class SQL
{
    private string _connectionString = "REDACTED";



    public DataTable ExecuteQuery(string query, List parameters = null)
    {
        using (var connection = new IngresConnection(_connectionString))
        {
            connection.Open();
            using (var command = new IngresCommand(query, connection))
            {
                try
                {
                    using (var adapter = new IngresDataAdapter(command))
                    {
                        adapter.AcceptChangesDuringFill = false;


                        var dataTable = new DataTable();
                        adapter.Fill(dataTable);
                        return dataTable;
                    }
                }
                catch (Exception e)
                {
                    // log the error
                    Console.WriteLine(e.Message);
                    throw;
                }
            }
        }
    }
}

MainWindows.xaml.cs

SQL S = new SQL();
            

var query = $"select datanl from REDACTED where datanl between '2023-01-04 00:00:00' and '2023-01-05 00:00:00' and belart = 'A'  and planist = 1 order by datanl asc";

dataTable = S.ExecuteQuery(query);
...

First five rows that VDBA returns:

04.01.2023 00:03:10
04.01.2023 00:03:18
04.01.2023 00:06:23
04.01.2023 00:07:07
04.01.2023 00:08:14

First five rows that app SQL class returns:

04.01.2023 1:06:08
04.01.2023 1:08:31
04.01.2023 1:08:34
04.01.2023 1:08:37
04.01.2023 1:08:42

Why does Adapter.Fill(dataTable) return shifted results? It seems that it got "pushed" but why?

I've tried adding the .AcceptChangesDuringFill = true / false but neither of them seems to do nothing.

Tried to use IngresDataReader reader = command.ExecuteQuery(); and then dataTable.Load(reader); the result is the same.

I'm really not getting why it acts like this..

EDIT:

Tried to change SQL fetch method to another:

public DataTable PullData(string INPUT)
    {
        
        using (IngresConnection I_CON = new IngresConnection())
        {
            using (IngresCommand I_CMD = new IngresCommand())
            {
                //Handle Errors @ Connecting
                try
                {
                    I_CON.ConnectionString = _connectionString;
                    I_CMD.Connection = I_CON;
                    I_CMD.CommandType = System.Data.CommandType.Text;
                    I_CMD.CommandText = INPUT;
                    I_CON.Open();
                    Console.WriteLine(INPUT);
                    using (IngresDataReader I_RDR = I_CMD.ExecuteReader())
                    {
                        var dataTable = new DataTable();
                        while (I_RDR.Read())
                        {
                            Console.WriteLine(I_RDR.GetValue(0));
                        }

                        dataTable.Load(I_RDR);
                        
                        I_RDR.Close();
                        I_CON.Close();
                        I_CON.Dispose();

                        return dataTable;
                    }
                }
                catch (Exception E)
                {
                    throw;
                }

            }

        }
        
    }

Did not work, returned the same mashup like the previous one

SQL Adapter.Fill(dataTable) fetches wrong rows

Answers (1)

Related Questions