Update

Question

User-305388250 posted

Hey,

I am using the following web method:-

#region Admin Insert

[WebMethod(Description = "This web method will check for an admin user.")]

public string Admin_Check()

{

string resultStr = string.Empty;

string connStr = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|/forum.mdb;Persist Security Info=True";

string cmdTxt = "UPDATE members SET[access] = 'Admin' WHERE date_added < '" + DateTime.Now.Equals(-10) + "'";

System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(connStr);

System.Data.OleDb.OleDbCommand myCmd = new System.Data.OleDb.OleDbCommand(cmdTxt, myConn);

myConn.Open();

myCmd.ExecuteNonQuery();

myConn.Close();

resultStr = "Added";

return resultStr;

}

#endregion

I want this to check to see if a member has been a member for more than 10 days. If this is true then the update changes the "access" row in the database. At the moment when i consume this web service it inserted "Admin" for every row even though some of them are 2 days old. I think the following bit of code is wrong:-

DateTime.Now.Equals(-10)

I want this to say if the date is more than 10 days old..

Any Ideas how i can do this?

Thanks

Answer 1

User-1200636353 posted

DateTime.Now.Equals(-10) returns a boolean value, whereas I think that you are wanting an actual DateTime value.

 

I'm not too good at dealing with database statements yet, but maybe try something like:

DateTime.Now.AddDays(-10.0)

 

This actually returns a DateTime object, and it should be one that is 10 days prior to the current time.

Answer 2

User-54340632 posted

You can use a timespan to help you get this.  Define the timespan, then subtract it from your date.

  

TimeSpan ts = new TimeSpan(10, 0, 0, 0);
string cmdTxt = "UPDATE members SET[access] = 'Admin' WHERE date_added < '" + DateTime.Now.Subtract(ts) + "'";

 

Answer 3

User-305388250 posted

Hey,

Thanks for your reply. That does not work, the date field in my database has the following date format:-

25/04/2009 14:35:24

So i dont know why its not working as it should work..

Answer 4

User-305388250 posted

Hey,

Thanks Aivenssar2, i have tried that code but it does not seem to work either..

#region Admin Insert
    [WebMethod(Description = "This web method will check for an admin user.")]
    public void Admin_Check()
    {
        TimeSpan ts = new TimeSpan(10, 0, 0, 0);
        string connStr = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|/forum.mdb;Persist Security Info=True";

        string cmdTxt = "UPDATE members SET[access] = 'Admin' WHERE date_added < '" + DateTime.Now.Subtract(ts) + "'";

        System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(connStr);
        System.Data.OleDb.OleDbCommand myCmd = new System.Data.OleDb.OleDbCommand(cmdTxt, myConn);

        myConn.Open();
        myCmd.ExecuteNonQuery();

        myConn.Close();
    }
    #endregion

 Is it because of the format my date and time is in the database..?

Answer 5

User-54340632 posted

Could you try changing:

DateTime.Now.Subtract(ts)

to

DateTime.Now.Subtract(ts).ToString()

or perhaps

DateTime.Now.Subtract(ts).ToShortDateString()

Answer 6

User-305388250 posted

Hey,

Still no luck:-

I tried both ways, but it does not update the field. I tried to use a constant for test purposes and that worked. So it means it does not recognize the WHERE clause..

:(

 

Answer 7

User-1200636353 posted
can you post the line of code where you used the constant that worked.

Answer 8

User-305388250 posted

I have just tried this:-

string cmdTxt = "UPDATE members SET[access] = 'Admin' WHERE date_added < '21/05/2009 21:11:33'";

And it does not work. Sorry i was meant to say it did not work when i used a constant. This is a field from the database. It does not update the "access" row and change it to Admin..

 

Answer 9

User-1200636353 posted

Well, the DateTime.Now.AddDays(-10.0) will not work because it gives your a 12-hour time with AM/PM.

 

DateTime.Now.AddDays(-10.0).ToString("dd/MM/yyyy HH:mm:ss")

will give you a DateTime string formatted for what you should need.

 

maybe try this an see what happens.

Answer 10

User-54340632 posted

Try changing your cmdTxt to:

string cmdTxt = "UPDATE members SET[access] = 'Admin' WHERE date_added < #" + DateTime.Now.Subtract(ts).ToShortDateString()  + "#";

basically, change the ' marks to # signs

Answer 11

User-1200636353 posted

i dont know if .ToShortDateString() would work, since i think (judging from the example of "21/05/2009") that he needs his date string in the format of day/month/year, and i think that by default, getting a date string using a method will give it in month/day/year.

 

maybe the ' to # may help though.

Answer 12

User-305388250 posted

Hey,

None of the above methods seem to be working :-(]

I have found something weird though. I used the following code:-

string cmdTxt = "UPDATE members SET[access] = 'Admin' WHERE date_added < '" + DateTime.Now.ToString() + "'";

And i had the following dates in my database:-

25/04/2009 03:20:56

25/04/2009 14:35:24

27/03/2009 16:10:32

27/05/2009 19:59:28

21/04/2009 21:11:33

BUT it only update the following one:-

21/04/2009 21:11:33

When it should have updated:-

25/04/2009 03:20:56

25/04/2009 14:35:24

27/03/2009 16:10:32

21/04/2009 21:11:33

The top four dates should have been updated...

 

 

 

Answer 13

User-54340632 posted

Because it treated it as a string i'm guessing.  Today's date in that format is 21/05/2009.  only 21/04/2009 is less than that based on the dates i see above.

What type is the database field?  Did you try it with # instead of '?  I am guessing you're using microsoft access?  Access needs # around the dates not tick marks.

Answer 14

User-1200636353 posted

Using DateTime.Now.AddDays(-10.0).ToString("dd/MM/yyyy HH:mm:ss") gives the following:

 

11/05/2009 17:57:17

 

so i don't see why it wouldn't work, unless it is like Aivenssar2 said, that it is being treated as a string comparison.

 

<strike>Maybe you could try using the DateDiff function (if you are using SQL)...</strike>

 

<strike>http://msdn.microsoft.com/en-us/library/ms189794.aspx </strike>

 (scratch that, forgot that you were using another database type...)