SqlDateTime Overflow exception after Profile_OnMigrateAnonymous

Question

In my web application on visual web developer 2010 express, i'm using profiles to store user credentials and the items in his/her current shopping cart (by using a custom class). I wanted to give the customers the possibility of starting adding items to their shopping carts before they login, so i wrote some code to handle the Profile_OnMigrateAnonymous event.

Here it is :

       void Profile_OnMigrateAnonymous(object sender, ProfileMigrateEventArgs e)
{
    ProfileCommon anonymousProfile = Profile.GetProfile(e.AnonymousID);
    if (anonymousProfile.Orders.SCart != null)
    {
        if (Profile.Orders.SCart == null)
            Profile.Orders.SCart = new Cart();

        Profile.Orders.SCart.Items.AddRange(anonymousProfile.Orders.SCart.Items);
        Profile.Save();

        anonymousProfile.Orders.SCart = null;
        anonymousProfile.Save();
    }

    ProfileManager.DeleteProfile(e.AnonymousID);
    AnonymousIdentificationModule.ClearAnonymousIdentifier();
}

My web.config settings concerning Profiles usage, are listed below:

    <anonymousIdentification enabled="true"/>

<profile automaticSaveEnabled="false">
  <properties>
    <group name="Location">
      <add name="Address"/>
      <add name="Latitude"/>
      <add name="Longitude"/>
      <add name="PhoneNumber"/>
      <add name="Other"/>
    </group>
    <group name="Orders">
      <add name="SCart" serializeAs="Binary" type="Cart" allowAnonymous="true"/>
    </group>
  </properties>
</profile>

There's no problem until VS runs this code :

    private void InsertOrder()
{
    // Setting up queries parameters
    string insertSQL = "";

    insertSQL += "INSERT INTO Orders (";
    insertSQL += "UserName, DateCreated, LastUpdate, Description, PaymentType, Delivery, Total) ";
    insertSQL += "VALUES (@UserName, @DateCreated, @LastUpdate, @Description, @PaymentType, @Delivery, @Total)";

    SqlCommand cmd0 = new SqlCommand(insertSQL, connection);

    cmd0.Parameters.AddWithValue("@UserName", Profile.UserName.ToString());
    cmd0.Parameters.AddWithValue("@DateCreated", Profile.Orders.SCart.DateCreated());
    cmd0.Parameters.AddWithValue("@LastUpdate", Profile.Orders.SCart.LastUpdate());
    cmd0.Parameters.AddWithValue("@Description", Profile.Orders.SCart.GetCartDescription());
    cmd0.Parameters.Add("@PaymentType", SqlDbType.Bit).Value = Profile.Orders.SCart.PaymentType;
    cmd0.Parameters.Add("@Delivery", SqlDbType.Bit).Value = Profile.Orders.SCart.Delivery;
    cmd0.Parameters.AddWithValue("@Total", Profile.Orders.SCart.Total);

    string selectSQL = "SELECT OrderID FROM Orders WHERE UserName=@UserName AND DateCreated=@DateCreated";
    SqlCommand cmd1 = new SqlCommand(selectSQL, connection);

    cmd1.Parameters.AddWithValue("@UserName", Profile.UserName);
    cmd1.Parameters.AddWithValue("@DateCreated", Profile.Orders.SCart.DateCreated());
    SqlDataReader reader;

    string insertSQL2 = "";

    insertSQL2 += "INSERT INTO CategoriesInAnOrder (";
    insertSQL2 += "OrderID, CategoryID, Quantity) VALUES (@OrderID, @CategoryID, @Quantity)";

    try
    {
        connection.Open();
        // Insert new record in Orders table
        cmd0.ExecuteNonQuery();

        // Retrieve OrderID for the record inserted
        reader = cmd1.ExecuteReader();
        reader.Read();
        OrderID = (int)reader["OrderID"];
        reader.Close();

        // For known customers, store information for later usage
        if (!Profile.IsAnonymous)
        {
            foreach (CartItem item in Profile.Orders.SCart.Items)
            {
                SqlCommand cmd2 = new SqlCommand(insertSQL2, connection);
                cmd2.Parameters.AddWithValue("@OrderID", OrderID);
                cmd2.Parameters.AddWithValue("@CategoryID", item.CategoryID);
                cmd2.Parameters.AddWithValue("@Quantity", item.Quantity);
                cmd2.ExecuteNonQuery();
            }
        }
    }
    catch (Exception err)
    {
        pnlWizard.Visible = false;
        lblError.Text = err.Message;
    }
    finally
    {
        // cleanup
        connection.Close();
    }

And then:

-"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and ..."

What's wrong with this code?

Answer 1

At a guess, Profile.Orders.SCart.DateCreated() is returning DateTime.MinValue, which isn't representable as a SqlDateTime.

Work out what date you were expecting, and consider using a SqlDateTime.Null if it's actually meant to be a null value.

You may find that you basically aren't getting the appropriate data at all - I suggest you put some diagnostics in to see the contents of the cart before you start trying to insert it into the database.

Answer 2

Your second query seems to be there only to retrieve new orderid. You should do this in the 1st query using @scope_identity and save one unnecessary round trip to do database. Look here http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx