locked
Inserting into database in C# class RRS feed

  • Question

  • User1503923713 posted

    During an adaption of a book based example application, to use an access database instead of sql server - I have come up with the final issue: It will not insert into the database! I suspect it might be to do with the OrderID, I do have it set to autonumber, but I am not too sure. This is the class that does the inserts, by adding customer information to customer table, order information to order table and ordeitems information to the orderitems table. When submitting this information on the webpage, there is the custom error that an error has occurred in your order due to this code page code behind

    bool success = OrderDB.WriteOrder(order);

                Session["cart"] = null;

                Session["order"] = null;

    if (success)

                    Response.Redirect("Completed.aspx");

                else

                    Response.Redirect("Completed.aspx?Error=1"); //I am recieving this

    The class:

      public static class OrderDB

        {

            static OleDbTransaction tran;

            static OleDbConnection con;

     

            public static bool WriteOrder(Order o)

            {

                string cs = WebConfigurationManager

                    .ConnectionStrings["OICConnectString"]

                        .ConnectionString;

                con = new OleDbConnection(cs);

                con.Open();

                tran = con.BeginTransaction();

                try

                {

                    InsertCustomer(o.Cust);

                    int oNum = InsertOrder(o);

                    foreach (CartItem item in o.Cart.GetItems())

                        InsertItem(item, oNum);

                    tran.Commit();

                    con.Close();

                    return true;

                }

                catch (Exception ex)

                {

                    tran.Rollback();

                    return false;

                }

            }

     

            private static void InsertCustomer(Customer cust)

            {

                OleDbCommand cmd = new OleDbCommand();

                cmd.Connection = con;

                cmd.Transaction = tran;

                try

                {

                    cmd.CommandText = "INSERT INTO Customers "

                        + "(CustomerLastName, CustomerFirstName, "

                        + "CustomerAddress1, CustomerAddress2, City, County, Postcode, Country"

                        + "CustomerPhoneNo, CustomerEmail) "

                        + "VALUES (@CustomerLastName, @CustomerFirstName, "

                        + "@CustomerAddress1, @CustomerAddress2, @City, @County, @Postcode,"

                        + "@Country, @CustomerPhoneNo, @CustomerEmail)";

                    cmd.Parameters.AddWithValue(

                        "@CustomerLastName", cust.CustomerLastName);

                    cmd.Parameters.AddWithValue(

                        "@CustomerFirstName", cust.CustomerFirstName);

                    cmd.Parameters.AddWithValue(

                        "@CustomerAddress1", cust.CustomerAddress1);

                    cmd.Parameters.AddWithValue(

                       "@CustomerAddress2", cust.CustomerAddress2);

                    cmd.Parameters.AddWithValue(

                        "@City", cust.City);

                    cmd.Parameters.AddWithValue(

                        "@County", cust.County);

                    cmd.Parameters.AddWithValue(

                        "@Postcode", cust.Postcode);

                    cmd.Parameters.AddWithValue(

                        "@Country", cust.Country);

                    cmd.Parameters.AddWithValue(

                        "@CustomerPhoneNo", cust.CustomerPhoneNo);

                    cmd.Parameters.AddWithValue(

                        "@CustomerEmail", cust.CustomerEmail);

                    cmd.ExecuteNonQuery();

                }

                catch (SqlException ex)

                {

                    if (ex.Number == 2627) // Duplicate key

                    {

                        cmd.CommandText = "UPDATE Customers "

                            + "SET CustomerLastName = @CustomerLastName, "

                            + "CustomerFirstName = @CustomerFirstName, "

                            + "CustomerAddress1 = @CustomerAddress1, "

                            + "CustomerAddress2 = @CustomerAddress2, "

                            + "City = @City, "

                            + "County = @County, "

                            + "Postcode = @Postcode, "

                             + "Country = @Country, "

                            + "CustomerPhoneNo = @CustomerPhoneNo "

                            + "WHERE CustomerEmail = @CustomerEmail ";

                        cmd.ExecuteNonQuery();

                    }

                    else

                        throw ex;

                }

            }

     

            private static int InsertOrder(Order o)

            {

                OleDbCommand cmd = new OleDbCommand();

                cmd.Connection = con;

                cmd.Transaction = tran;

                cmd.CommandText = "INSERT INTO Orders "

                    + "(OrderDate, CustomerEmail, "

                    + "SubTotal, VAT, "

                    + "Shipping) "

                    + "VALUES (@OrderDate, @CustomerEmail, "

                    + "@SubTotal, @VAT, "

                    + "@Shipping)";

                cmd.Parameters.AddWithValue(

                    "@OrderDate", DateTime.Now);

                cmd.Parameters.AddWithValue(

                    "@OrderDate", DateTime.Now);

                cmd.Parameters.AddWithValue(

                    "@CustomerEmail", o.Cust.CustomerEmail);

                cmd.Parameters.AddWithValue(

                    "@Subtotal", o.SubTotal);

                cmd.Parameters.AddWithValue(

                    "@VAT", o.VAT);

                cmd.Parameters.AddWithValue(

                    "@Shipping", o.Shipping);

                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@IDENTITY";

                return Convert.ToInt32(cmd.ExecuteScalar());

            }

     

            private static void InsertItem(CartItem item,

                int oNum)

            {

                OleDbCommand cmd = new OleDbCommand();

                cmd.Connection = con;

                cmd.Transaction = tran;

                cmd.CommandText = "INSERT INTO OrderItems "

                    + "(OrderID, ProductID, "

                    + "Name, Price, Quantity) "

                    + "VALUES (@OrderID, @ProductID, "

                    + "@Name, @Price, @Quantity)";

                cmd.Parameters.AddWithValue(

                    "@OrderID", oNum);

                cmd.Parameters.AddWithValue(

                    "@ProductID", item.ID);

                cmd.Parameters.AddWithValue(

                    "@Name", item.Name);

                cmd.Parameters.AddWithValue(

                    "@Price", item.Price);

                cmd.Parameters.AddWithValue(

                    "@Quantity", item.Quantity);

                cmd.ExecuteNonQuery();

            }

     

        }

    Friday, April 9, 2010 5:07 AM

Answers

  • User-1199946673 posted

            private static int InsertOrder(Order o)

            {

                OleDbCommand cmd = new OleDbCommand();

                cmd.Connection = con;

                cmd.Transaction = tran;

                cmd.CommandText = "INSERT INTO Orders "

                    + "(OrderDate, CustomerEmail, "

                    + "SubTotal, VAT, "

                    + "Shipping) "

                    + "VALUES (@OrderDate, @CustomerEmail, "

                    + "@SubTotal, @VAT, "

                    + "@Shipping)";

                cmd.Parameters.AddWithValue(

                    "@OrderDate", DateTime.Now);

                cmd.Parameters.AddWithValue(

                    "@OrderDate", DateTime.Now);

                cmd.Parameters.AddWithValue(

                    "@CustomerEmail", o.Cust.CustomerEmail);

                cmd.Parameters.AddWithValue(

                    "@Subtotal", o.SubTotal);

                cmd.Parameters.AddWithValue(

                    "@VAT", o.VAT);

                cmd.Parameters.AddWithValue(

                    "@Shipping", o.Shipping);

                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@IDENTITY";

                return Convert.ToInt32(cmd.ExecuteScalar());

            }

     

    This is where the problems begins. First of all, you're specifying the OrderDate parameter twice. Since OleDb parameters are recognized by position, not by Name, it means that the second time you add this parameter, a date is inserted in the CustomerEmail, An EmailAddress is inserted in subtotal, A subtotal into VAT, and VAT into shipping. Off course this won't work, so you need to remove one of the OrderDate Parameters

    Then it still won't work. For OrderDate, you're using DateTime.Now. However, you must realize that this will return a DateTime Value including Miliseconds. An Access Database (or better said a Jet Database Enigine), cannot deal with milliseconds. So when you're entering DateTime into an mdb file, you need to convert them into an OleAutomation Date:

     

    cmd.Parameters.AddWithValue( "@OrderDate", DateTime.Now.ToOADate);

    Or Skip the parameter and use Now() in the query itself:

    cmd.CommandText = "INSERT INTO Orders "

                    + "(OrderDate, CustomerEmail, "

                    + "SubTotal, VAT, "

                    + "Shipping) "

                    + "VALUES (Now(), @CustomerEmail, "

                    + "@SubTotal, @VAT, "

                    + "@Shipping)";

    More info:

    http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 9, 2010 9:04 AM

All replies

  • User1477435862 posted

    The WriteOrder method is returning false because an exception is being thrown.  Have a look at the exception's message and it will give you the details you need.

    Friday, April 9, 2010 7:48 AM
  • User-1199946673 posted

            private static int InsertOrder(Order o)

            {

                OleDbCommand cmd = new OleDbCommand();

                cmd.Connection = con;

                cmd.Transaction = tran;

                cmd.CommandText = "INSERT INTO Orders "

                    + "(OrderDate, CustomerEmail, "

                    + "SubTotal, VAT, "

                    + "Shipping) "

                    + "VALUES (@OrderDate, @CustomerEmail, "

                    + "@SubTotal, @VAT, "

                    + "@Shipping)";

                cmd.Parameters.AddWithValue(

                    "@OrderDate", DateTime.Now);

                cmd.Parameters.AddWithValue(

                    "@OrderDate", DateTime.Now);

                cmd.Parameters.AddWithValue(

                    "@CustomerEmail", o.Cust.CustomerEmail);

                cmd.Parameters.AddWithValue(

                    "@Subtotal", o.SubTotal);

                cmd.Parameters.AddWithValue(

                    "@VAT", o.VAT);

                cmd.Parameters.AddWithValue(

                    "@Shipping", o.Shipping);

                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@IDENTITY";

                return Convert.ToInt32(cmd.ExecuteScalar());

            }

     

    This is where the problems begins. First of all, you're specifying the OrderDate parameter twice. Since OleDb parameters are recognized by position, not by Name, it means that the second time you add this parameter, a date is inserted in the CustomerEmail, An EmailAddress is inserted in subtotal, A subtotal into VAT, and VAT into shipping. Off course this won't work, so you need to remove one of the OrderDate Parameters

    Then it still won't work. For OrderDate, you're using DateTime.Now. However, you must realize that this will return a DateTime Value including Miliseconds. An Access Database (or better said a Jet Database Enigine), cannot deal with milliseconds. So when you're entering DateTime into an mdb file, you need to convert them into an OleAutomation Date:

     

    cmd.Parameters.AddWithValue( "@OrderDate", DateTime.Now.ToOADate);

    Or Skip the parameter and use Now() in the query itself:

    cmd.CommandText = "INSERT INTO Orders "

                    + "(OrderDate, CustomerEmail, "

                    + "SubTotal, VAT, "

                    + "Shipping) "

                    + "VALUES (Now(), @CustomerEmail, "

                    + "@SubTotal, @VAT, "

                    + "@Shipping)";

    More info:

    http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 9, 2010 9:04 AM
  • User2077524089 posted

    Hi,

    Please check this Function InsertOrder(Order o)  at the end of the line of this function you wrote

    return Convert.ToInt32(cmd.ExecuteScalar()); instead of cmd.Executenonquery()

    check it once

    Friday, April 9, 2010 9:04 AM
  • User-1199946673 posted

    Please check this Function InsertOrder(Order o)  at the end of the line of this function you wrote

    return Convert.ToInt32(cmd.ExecuteScalar()); instead of cmd.Executenonquery()

     

    ExecuteScalar is the way to go there! SELECT @@IDENTITY will return only 1 value, which is exactly what ExecuteScalar is also doing!

    http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

    Friday, April 9, 2010 9:10 AM
  • User1503923713 posted

    I changed the date like you said using NOW()  

    +

    "VALUES (Now(), @CustomerEmail, "

    +

    "@SubTotal, @VAT, "

    +

    "@Shipping)"; but I still have the same message so there must be something else wrong tooEmbarassed

    Friday, April 9, 2010 3:48 PM
  • User-1199946673 posted

    I changed the date like you said using NOW()
     

     

    And did you remove these two lines?

                cmd.Parameters.AddWithValue("@OrderDate", DateTime.Now);

                cmd.Parameters.AddWithValue("@OrderDate", DateTime.Now);

     

    When you've done that you need to debug the code to see where the error occurs and what error you get?

    Friday, April 9, 2010 4:47 PM
  • User1503923713 posted

     Yes I did, sorry I forgot to mention

    Friday, April 9, 2010 4:55 PM
  • User-1199946673 posted

    I also see that you didn't specify the parameters for the "UPDATE Customers" command! 

    Friday, April 9, 2010 5:05 PM
  • User1503923713 posted

    Ok, I have done that but still it does not work. I think I will play around with it for a while, and see what happens. Thank you though, :-) 

    Saturday, April 10, 2010 5:12 AM