Compare date with Ingres.Client and C#

[Juan_Carlos]

Hello. I work with Ingres.Client 2.1.0.0 in C#.

My code:

IngresConnection IngCnn = new IngresConnection("UID = recauda; PWD = xxxx; Role ID = sprtri; Role Password = xxxx;Host=172.26.31.122;Database=labprod;date_form at=DMY;Connect Timeout=60");
IngresCommand Cmd = IngCnn .CreateCommand();

IngCnn.Open();

CadSQLIngres = "select contrib_origen, contrib_destino, usuario, fecha_orden" +
" from conver_contrib";


DataSet ConverContrib = new DataSet();
IngresDataAdapter DAConCon = new IngresDataAdapter(CadSQLIngres,IngCnn);
DAConCon.Fill(ConverContrib);

TotalAConvertir = ConverContrib.Tables[0].Rows.Count;
foreach (DataRow FilaConCon in ConverContrib.Tables[0].Rows)
{

..........
..........
..........

CadSQLIngres = " update conver_contrib set fecha_conv = 'now'" +
" where contrib_origen = " + FilaConCon["contrib_origen"] +
" and contrib_destino = " + FilaConCon["contrib_destino"] +
" and fecha_orden = '" + FilaConCon["fecha_orden"].ToString() + "'";

NTuplas = Cmd.ExecuteNonQuery();
}

The result of NTuplas is 0 because fecha_orden = '" + FilaConCon["fecha_orden"].ToString() + "'" dates do not match.

If connect by ODBC (OdbcConnection) to Ingres, the update work fine.

thank you very much.

En español.
Con el Ingres.Client, a la hora de hacer el update, el resultado es cero, porque las fechas no coinciden, siendo estas las mismas (mismo campo y misma tabla).

Por ODCB la sentencia se ejecuta sin nigún problema.

Muchas gracias.

[thoda04]

Hi Juan,

What is the Ingres data type of the fecha_orden column? VARCHAR? ANSIDATE? INGRESDATE? TIMESTAMP? What is the metadata type of the FilaConCon["fecha_orden"] column in the DataTable. It would be useful to know what the data type is when .NET converts the value to a String using the default culture on the ToString() call. What is the output of the FilaConCon["fecha_orden"].ToString()? Keep in mind that the date_format=DMY is not used by the data provider but is only passed on to the DBMS server for its processing. All formating rules in ToString() are done purely by the .NET Framework using the default culture.

Hope this helps,
Dave

[Juan_Carlos]

Hi and thanks

Ingres data types of table conver_contrib:
create table conver_contrib(
contrib_origen integer not null default 0,
contrib_destino integer not null default 0,
usuario varchar(7) not null default ' ',
fecha_orden date not null default ' ',
fecha_conv date not null default ' '
)

The metadata type of the FilaConCon["fecha_orden"] column is Date.
The output of the FilaConCon["fecha_orden"].ToString() is "20/01/2012 8:10:54".
I use date_format=DMY to save a date "20/01/2012" and not return error Invalid number of month.

If i use
select varchar(fecha_orden) from conver_contrib
and after
update conver_contrib set fecha_conv = 'now' where varchar(fecha_orden) = '" + FilaConCon["fecha_orden"] +"'"
This work fine

[Juan_Carlos]

Hi.
I discovered that the problem is in the hours and not the date
If the field contains "20/01/2012 8:20:55"
select fecha_orden from conver_contrib
update conver_contrib set fecha_conv = 'now' where fecha_orden = '" + FilaConCon["fecha_orden"].ToStrig()
Don't work
But if the field contains "20/01/2012" the Update work fine.

Thank you very much.

[kschendel]

Are you sure those dates in the table are ingresdate and not ansidate? ansidate doesn't allow a time part.

[Juan_Carlos]

Hi.
The field is declare "fecha_orden date not null default ' '," and I can do two insert
insert into conver_contrib set fecha_orden = 'today' -- >the field contain '06.02.2012'
or
insert into conver_contrib set fecha_orden = 'now' -- >the field contain '06.02.2012 14:39:22'