locked
Update RRS feed

  • 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

    Thursday, May 21, 2009 3:33 PM

Answers

  • 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...

     

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 21, 2009 5:49 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 21, 2009 5:56 PM
  • 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...)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 21, 2009 6:03 PM

All replies

  • 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.

    Thursday, May 21, 2009 3:56 PM
  • 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) + "'";
     
    Thursday, May 21, 2009 4:04 PM
  • 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..

    Thursday, May 21, 2009 4:06 PM
  • 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..?

    Thursday, May 21, 2009 4:09 PM
  • User-54340632 posted

    Could you try changing:

    DateTime.Now.Subtract(ts)

    to

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

    or perhaps

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

    Thursday, May 21, 2009 4:15 PM
  • 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..

    :(

     

    Thursday, May 21, 2009 4:34 PM
  • User-1200636353 posted
    can you post the line of code where you used the constant that worked.
    Thursday, May 21, 2009 4:49 PM
  • 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..

     

    Thursday, May 21, 2009 4:58 PM
  • 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.

    Thursday, May 21, 2009 5:14 PM
  • 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
    Thursday, May 21, 2009 5:29 PM
  • 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.

    Thursday, May 21, 2009 5:42 PM
  • 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...

     

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 21, 2009 5:49 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 21, 2009 5:56 PM
  • 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...)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 21, 2009 6:03 PM