FormatException Error String to DateTime

Question

I have been sitting on my seat for over an hour not knowing what's the error.. Could someone please assist?

Error is said to be "When converting a string to DateTime, parse the string to take the date before putting each variable into the DateTime object.

The field name "LastLoginTime" is a DATETIME datatype in my database.

These are the codes..

protected void Page_Load(object sender, EventArgs e)
{
    AuditNLoggingDAO al = new AuditNLoggingDAO();

    int result = 0;

    int resultLogout = 0;

    DateTime dateTimeOfLatestLogin = DateTime.MinValue;

    //Get IP Address of Client's Machine
    String externalIP = null;
    try
    {
        externalIP = (new WebClient()).DownloadString("http://checkip.dyndns.org/");
        externalIP = (new Regex(@"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}")).Matches(externalIP)[0].ToString();
    }
    catch (Exception ex)
    {
        logManager log = new logManager();
        log.addLog("Retrieval of IP Address", "IP Address", ex);
    }

    if (!Page.IsPostBack)
    {
        if (!String.IsNullOrEmpty(Session["LoginUserName"].ToString()))
        {
            String loginUsername = Session["LoginUserName"].ToString();

            //Get latest Login time
            DataSet ds = new DataSet();

            ds = al.getAuditData(Session["LoginUserName"].ToString());

            foreach (DataRow r in ds.Tables[0].Rows)
            {
                dateTimeOfLatestLogin = Convert.ToDateTime(r["LastLoginTime"]);
            }

            result = al.trackLogout(loginUsername, DateTime.Now, externalIP, Convert.ToDouble(latitudeTB.Value), Convert.ToDouble(longitudeTB.Value));
            resultLogout = al.updateLLogoutT(loginUsername, DateTime.Now, externalIP);
        }

        loginDetails.InnerText = "You logged into your account at " + dateTimeOfLatestLogin.ToString("hh:mm:ss tt dd/MM/yyyy") + " SGT.";
        logoutDetails.InnerText = "You logged out from your session at " + (DateTime.Now).ToString("hh:mm:ss tt dd/MM/yyyy") + " SGT.";
    }
}

I can't seemt to be able to find the mistake.. I'm guessing it's my dateTimedateTimeOfLatestLogin variable..

al.trackLogout method,

//Track Logout Activity
public int trackLogout(String username, DateTime dateTimeActivity, String ipaddress, Double latitude, Double longitude)
{
    int result = 0;

    StringBuilder sqlCmd = new StringBuilder();
    sqlCmd.AppendLine("INSERT INTO AuditActivity (Username, DateTimeActivity, IPAddressActivity, LatitudeActivity, LongitudeActivity, ActivityType) ");
    sqlCmd.AppendLine("VALUES (@addUsername, @addDT, @addIPAddress, @addLat, @addLng, @addActivity)");

    try
    {
        SqlConnection myConn = new SqlConnection(DBConnectionStr);

        myConn.Open();

        SqlCommand cmd = new SqlCommand(sqlCmd.ToString(), myConn);

        cmd.Parameters.AddWithValue("@addUsername", username);
        cmd.Parameters.AddWithValue("@addDT", dateTimeActivity);
        cmd.Parameters.AddWithValue("@addIPAddress", ipaddress);
        cmd.Parameters.AddWithValue("@addLat", latitude);
        cmd.Parameters.AddWithValue("@addLng", longitude);
        cmd.Parameters.AddWithValue("@addActivity", "Logout");

        result = cmd.ExecuteNonQuery();

        myConn.Close();

        return result;
    }
    catch (SqlException ex)
    {
        logManager log = new logManager();
        log.addLog("AuditNLoggingDAO.trackLogout", sqlCmd.ToString(), ex);
        return 0;
    }
}

Answer 1

Not C# but don't have a C# project open to write in, so it is in VB.Net; minor syntax tweaks and changes but otherwise the same:

Public Function trackLogout(username As String, dateTimeActivity As DateTime, ipaddress As String, latitude As Double, longitude As Double) As Integer
    Dim result As Integer = 0

    Try
        Using conn As New SqlConnection(DBConnectionStr)
            Dim sb As New StringBuilder
            sb.AppendLine("INSERT INTO AuditActivity (Username, DateTimeActivity, IPAddressActivity, LatitudeActivity, LongitudeActivity, ActivityType) ")
            sb.AppendLine("VALUES (@addUsername, @addDT, @addIPAddress, @addLat, @addLng, @addActivity)")

            Using cmd As New SqlCommand() With {.CommandText = sb.ToString(), .Connection = conn, .CommandType = CommandType.Text}
                cmd.Parameters.AddWithValue("@addUsername", username)
                cmd.Parameters.AddWithValue("@addDT", dateTimeActivity)
                cmd.Parameters.AddWithValue("@addIPAddress", ipaddress)
                cmd.Parameters.AddWithValue("@addLat", latitude)
                cmd.Parameters.AddWithValue("@addLng", longitude)
                cmd.Parameters.AddWithValue("@addActivity", "Logout")

                result = cmd.ExecuteNonQuery
            End Using
        End Using
    Catch ex As Exception
        result = -1
        ' Whatever your current implementation is.
    End Try

    Return result
End Function

Unfortunately, doing inline sql execution requires StringBuilder. You were correct on that. I use Stored Procedures to execute structured SQL Statements, so I do not have to manage the SQL in the application.

Answer 2

My Revised Codes that i'm using right now..

protected void Page_Load(object sender, EventArgs e)
{
    AuditNLoggingDAO al = new AuditNLoggingDAO();

    int result = 0;

    int resultLogout = 0;

    //Get IP Address of Client's Machine
    String externalIP = null;
    try
    {
        externalIP = (new WebClient()).DownloadString("http://checkip.dyndns.org/");
        externalIP = (new Regex(@"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}")).Matches(externalIP)[0].ToString();
    }
    catch (Exception ex)
    {
        logManager log = new logManager();
        log.addLog("Retrieval of IP Address", "IP Address", ex);
    }

    CultureInfo provider = CultureInfo.InvariantCulture;

    if (!String.IsNullOrEmpty(Session["LoginUserName"].ToString()))
    {
        String loginUsername = Session["LoginUserName"].ToString();

        //Get latest Login time
        DataSet ds = new DataSet();

        ds = al.getAuditData(Session["LoginUserName"].ToString());

        DateTime dateTimeOfLatestLogin = DateTime.MinValue;

        /*foreach (DataRow r in ds.Tables[0].Rows)
        {
            //dateTimeOfLatestLogin = DateTime.ParseExact(r["LastLoginTime"].ToString(), "dd-MM-yyyy hh:mm:ss", CultureInfo.InvariantCulture);
            String dtDBString = r["LastLoginTime"].ToString();
            dateTimeOfLatestLogin = Convert.ToDateTime(dtDBString);

            //Debug.WriteLine(dateTimeOfLatestLogin);
            //"MM-dd-yyyy HH:mm:ss tt"
        }*/

        String DBConnectionStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

        SqlConnection connection = new SqlConnection(DBConnectionStr);
        string sql = "select LastLoginTime FROM AuditTrails WHERE Username=@USERID";
        SqlCommand command = new SqlCommand(sql, connection);
        command.Parameters.AddWithValue("@USERID", loginUsername);
        try
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    string timeTaken = reader["LastLoginTime"].ToString();
                    dateTimeOfLatestLogin = Convert.ToDateTime(timeTaken);
                }
            }
            connection.Close();
        }
        catch (SqlException ex)
        {
            logManager log = new logManager();
            log.addLog("LogoutWithDesc.aspx.cs", "PageLoad", ex);
        }

        resultLogout = al.updateLLogoutT(loginUsername, DateTime.Now, externalIP);
        result = al.trackLogout(loginUsername, externalIP, Convert.ToDouble(latitudeTB.Value), Convert.ToDouble(longitudeTB.Value));

        loginDetails.InnerText = "You logged into your account at " + dateTimeOfLatestLogin.ToString("hh:mm:ss tt dd/MM/yyyy") + " SGT.";
        logoutDetails.InnerText = "You logged out from your session at " + (DateTime.Now).ToString("hh:mm:ss tt dd/MM/yyyy") + " SGT.";
    }     
}