GetDate

Marvin kakuru

Hi vulpes,

Thanks a lot I was able to get my program to select date values from access, it worked like light. I have another challenge, I was using some code you had prescribed and worked perfectly when I was using mysql database, however with access it is giving me some trouble.

The code is supposed to select data in my access database where the date of entry and today's date are different by 90 days, below is the code I was using with mysql, but I have edited it to match the OleDb

{

OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\katoto\Documents\Visual Studio 2010\Projects\Genesis Philcollins\data\thyfarm.accdb;Jet OLEDB:Database Password=Kyozi");

string sql = ("select motherUIC,mother,UIC,Nam,animalsex,TagNo,Status,Owner,Day,Dat,wkno,mont,yr,presence from Herdregistar where DATEDIFF(day, [date], GetDate()) >= 90 AND status like '" + "Incalf" + "'");

//string sql = ("select motherUIC,mother,UIC,Name,animalsex,TagNo,Status,Owner,Day,Date,wkno,month,year,presence from Herdregistar where DATEDIFF(day, Convert(varchar(10), GetDate(),103), Convert(varchar(10), [date],103)) >= 90");

DataSet ds;

OleDbCommand cmd = new OleDbCommand();

OleDbDataAdapter da = new OleDbDataAdapter();

//SqlDataReader dr = new SqlDataReader();

cmd = new OleDbCommand(sql, cn);

da = new OleDbDataAdapter(cmd);

//dr = new SqlDataReader(da);

// cb = new SqlCommandBuilder(da);

ds = new DataSet();

da.Fill(ds, "select motherUIC,mother,UIC,Nam,animalsex,TagNo,Status,Owner,Day,Dat,wkno,mont,yr,presence");

da.Fill(ds);

dataGridView1.DataSource = ds.Tables[0];

}

When I run the above code I get an error "Undefined function 'GetDate' in expression."

Thanks again,

Best Regards.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Marvin kakuru14y ago
thanks vulpes it has finally worked thought i used 'd' instead of "d" DATEDIFF("d", [dat], Date()) >= 90
Vulpes14y ago
I've found some documentation for Access's version of the DateDiff function:

http://www.techonthenet.com/access/functions/date/datediff.php

It appears from the above that you need to specify "d" rather than day for the first argument so I'd try:

DATEDIFF("d", [dat], Date()) >= 90
Marvin kakuru14y ago

Hai guys I have realized that the only problem with the code is the area shaded red, because when cut out this part the code runs and the datagrid is filled with data where status like "incalf"
OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\katoto\Documents\Visual Studio 2010\Projects\Genesis Philcollins\data\thyfarm.accdb;Jet OLEDB:Database Password=Kyozi");
string sql = ("select motherUIC,mother,UIC,Nam,animalsex,TagNo,Status,Owner,day,Dat,wkno,mont,yr,presence from Herdregistar where DATEDIFF(day, [dat], Date()) >= 90 AND status like '" + "Incalf" + "'");

the purpose of this part of the code is to select data where the difference between today's date and that date selected is = or greater than 90
is there another way I can run this using an access database.i also think worth noting is that the access database did not let me use date as a field name, it stated that the word date was reserved for tha access database and if used would generate errors, could this be the problem.
Thanks again
Sam Hobbs14y ago
Oh, this is the same as in other threads. I answered anohter thread but Vulpes had already answered that question here.
Marvin kakuru14y ago
hi vulpes,
thanks i have tried replacing the offending line with just :

da.Fill(ds);

how ever the error persists " Invalid procedure call"

any advise.
Vulpes14y ago
Try replacing the offending line with just :

da.Fill(ds);

as you've already specified the SQL query when creating your OleDbCommand object and then used the latter to create the OleDbDataAdapter.
Marvin kakuru14y ago

Hi vulpes,

I altered the code as you advised however another error has come up.

OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\katoto\Documents\Visual Studio 2010\Projects\Genesis Philcollins\data\thyfarm.accdb;Jet OLEDB:Database Password=Kyozi");

            string sql = ("select motherUIC,mother,UIC,Nam,animalsex,TagNo,Status,Owner,day,Dat,wkno,mont,yr,presence from Herdregistar where DATEDIFF(day, [dat], Date()) >= 90 AND status like '" + "Incalf" + "'");

            DataSet ds;



            OleDbCommand cmd = new OleDbCommand();

            OleDbDataAdapter da = new OleDbDataAdapter();
            //SqlDataReader dr = new SqlDataReader();

            cmd = new OleDbCommand(sql, cn);

            da = new OleDbDataAdapter(cmd);

            ds = new DataSet();

            da.Fill(ds, "select motherUIC,mother,UIC,Nam,animalsex,TagNo,Status,Owner,day,Dat,wkno,mont,yr,presence");
            dataGridView1.DataSource = ds.Tables[0];

the error indicates the above code in yellow shade and it states " Invalid procedure call"

how can overcome this.

Thanks again and again.Best Regards
Vulpes14y ago
In MS Access, the equivalent of GetDate() is Date(). So, I'd try:

string sql = ("select motherUIC,mother,UIC,Nam,animalsex,TagNo,Status,Owner,Day,Dat,wkno,mont,yr,presence from Herdregistar where DATEDIFF(day, [date], Date()) >= 90 AND status like '" + "Incalf" + "'");