Using DateTime as parameter for a Date field

Using DateTime as parameter for a Date field

Postby kurtbilde » Wed 24 Jul 2013 19:45

Hi,

I'm having issues pasing DateTime (dato is a DateTime) to a parameter... This is the right way to construct the parameter isn't it?!?

The Oracle field that the parameter is generated for, is a Date field....

Code: Select all
              OracleParameter param1 = dataAdapter.SelectCommand.CreateParameter();
              param1.OracleDbType = OracleDbType.Date;
              param1.Direction = System.Data.ParameterDirection.Input;
              param1.ParameterName = "Dato";
              param1.Value = dato;
              dataAdapter.SelectCommand.Parameters.Add(param1);


-Kurt
kurtbilde
 
Posts: 113
Joined: Wed 16 Mar 2005 16:02
Location: Odense, Denmark

Re: Using DateTime as parameter for a Date field

Postby Pinturiccio » Thu 25 Jul 2013 13:42

Yes, you initialize parameter correctly. Unfortunately, we don't know the dato variable value and the values in your table. Here is an example of how to work with a parameter, corresponding to a Date type field.

DDL/DML script:
Code: Select all
CREATE TABLE SCOTT.DEPT (
  DEPTNO NUMBER(5),
  DNAME VARCHAR2(14 BYTE),
  LOC VARCHAR2(13 BYTE),
  DATO DATE,
  CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO))

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK', to_date('2013/07/25 15:31:49', 'yyyy/mm/dd hh24:mi:ss'));
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS', to_date('2013/07/25 15:31:49', 'yyyy/mm/dd hh24:mi:ss'));
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO', to_date('2013/07/24 15:31:49', 'yyyy/mm/dd hh24:mi:ss'));
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON', to_date('2013/07/24 15:31:49', 'yyyy/mm/dd hh24:mi:ss'));

C# code:
Code: Select all
OracleConnection conn = new OracleConnection("your connection string");
conn.Open();
OracleCommand comm = new OracleCommand("select * from dept where dato=:dato", conn);
DateTime dato = new DateTime(2013, 7, 25, 15, 31, 49);
OracleDataAdapter dataAdapter = new OracleDataAdapter(comm);
OracleParameter param1 = dataAdapter.SelectCommand.CreateParameter();
param1.OracleDbType = OracleDbType.Date;
param1.Direction = System.Data.ParameterDirection.Input;
param1.ParameterName = "dato";
param1.Value = dato;
dataAdapter.SelectCommand.Parameters.Add(param1);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
foreach (DataRow row in dt.Rows)
{
    foreach (DataColumn col in dt.Columns)
        Console.Write(row[col] + "\t");
    Console.WriteLine();
}

If the issue is still reproduced with this code, please describe it in more details.
Pinturiccio
Devart Team
 
Posts: 1930
Joined: Wed 02 Nov 2011 09:44

Re: Using DateTime as parameter for a Date field

Postby kurtbilde » Mon 29 Jul 2013 13:51

Hi,

Thanks for the answer...

Dato is Danish for Date ;-)

-Kurt
kurtbilde
 
Posts: 113
Joined: Wed 16 Mar 2005 16:02
Location: Odense, Denmark


Return to dotConnect for Oracle