Hi, guys. I have a question here. My AddListToDB() function will not perform insert data into tblOrderDetail.

I've been searching for answer for this. But, I cannot find any result.

So, please, if someone figured out why my code is not working, it will help me a lot.

note. AddUserToDB() function works properly. Only the AddListToDB() function will not work.

public partial class order_Cart : System.Web.UI.Page
{

    String connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Server.MapPath("~/App_Data/db.mdb");

    OleDbConnection myAccessConnection;

    private SortedList cart;
    private int key;

    protected void Page_Load(object sender, EventArgs e)
    {
        myAccessConnection = new OleDbConnection(connStr);

        this.GetCart();
        if (!IsPostBack)
            this.DisplayCart();
    }
    
    protected void btnRemove_Click(object sender, EventArgs e)
    {
        if (lstCart.SelectedIndex > -1 && cart.Count > 0)
        {
            cart.RemoveAt(lstCart.SelectedIndex);
            this.DisplayCart();
        }
    }
    
    protected void btnEmpty_Click(object sender, EventArgs e)
    {
        cart.Clear();
        lstCart.Items.Clear();
    }
    
    protected void btnCheckOut_Click(object sender, EventArgs e)
    {
        this.GetCart();
        if (cart.Count > 0 && this.txtEmail.Text != "" && this.txtName.Text != "" && this.txtPhone.Text != "")
        {
            AddUserToDB();
            AddListToDB();
            Response.Redirect("Order_Success.aspx");
            Session.Clear();
        }
        else
        {
            lblError.Visible = true;
            lblError.Text = "請輸入您的稱呼以及聯絡方式！";
        }
    }

    private void GetCart()
    {
        if (Session["Cart"] == null)
            Session.Add("Cart", new SortedList());
        cart = (SortedList)Session["Cart"];
    }

    private void DisplayCart()
    {
        lstCart.Items.Clear();
        CartItem item;
        foreach (DictionaryEntry entry in cart)
        {
            item = (CartItem)entry.Value;
            lstCart.Items.Add(item.Display());
        }
    }

    private void AddListToDB()
    {
        OleDbCommand myCommand = new OleDbCommand("INSERT INTO tblOrderDetail(oID, pID, Quantity) VALUES (@oID ,@pID,@Quantity)", myAccessConnection);

        for (int i = 0; i < cart.Count; i++)
        {

            // Mark the Command as a Text
            myCommand.CommandType = CommandType.Text;

            // Add Parameters to Command
            OleDbParameter pID = new OleDbParameter("@pID", OleDbType.Integer);
            pID.Value = cart.GetKey(i);
            myCommand.Parameters.Add(pID);

            OleDbParameter oID = new OleDbParameter("@oID", OleDbType.Integer);
            oID.Value = key;
            myCommand.Parameters.Add(oID);

            OleDbParameter Quantity = new OleDbParameter("@Quantity", OleDbType.Integer);
            Quantity.Value = cart.GetByIndex(i);
            myCommand.Parameters.Add(Quantity);

            try
            {
                openAccessConnection();
                myCommand.ExecuteNonQuery();
                closeAccessConnection();
            }
            catch (Exception exc)
            {
                Response.Write("更新資料庫失敗. 錯誤訊息 : " + exc.Message.ToString());
            }
        }
    }

    private void AddUserToDB()
    {
        OleDbCommand myCommand = new OleDbCommand("INSERT INTO tblOrder(oUserName, oUserPhone, oUserEmail, oDate) VALUES (@oUserName,@oUserPhone,@oUserEmail, @oDate)", myAccessConnection);
        string query = "SELECT @@Identity";

        // Mark the Command as a Text
        myCommand.CommandType = CommandType.Text;

        // Add Parameters to Command
        OleDbParameter oUserName = new OleDbParameter("@oUserName", OleDbType.VarChar);
        oUserName.Value = this.txtName.Text;
        myCommand.Parameters.Add(oUserName);

        OleDbParameter oUserPhone = new OleDbParameter("@oUserPhone", OleDbType.VarChar);
        oUserPhone.Value = this.txtPhone.Text;
        myCommand.Parameters.Add(oUserPhone);

        OleDbParameter oUserEmail = new OleDbParameter("@oUserEmail", OleDbType.VarChar);
        oUserEmail.Value = this.txtEmail.Text;
        myCommand.Parameters.Add(oUserEmail);

        OleDbParameter oDate = new OleDbParameter("@oDate", OleDbType.Date);
        oDate.Value = DateTime.Today;
        myCommand.Parameters.Add(oDate);

        try
        {
            openAccessConnection();
            myCommand.ExecuteNonQuery();
            myCommand.CommandText = query;
            key = (int)myCommand.ExecuteScalar();
            closeAccessConnection();
        }
        catch (Exception exc)
        {
            Response.Write("更新資料庫失敗. 錯誤訊息 : " + exc.Message.ToString());
        }
    }

    protected void openAccessConnection()
    {
        // If condition that can be used to check the access database connection
        // whether it is already open or not.
        if (myAccessConnection.State == ConnectionState.Closed)
        {
            myAccessConnection.Open();
        }
    }

    protected void closeAccessConnection()
    {
        // If condition to check the access database connection state
        // If it is open then close it.
        if (myAccessConnection.State == ConnectionState.Open)
        {
            myAccessConnection.Close();
        }
    }
}

You must add the parameters to the Command Object in the order in which they appear in the SQL.

What do u mean I need to add parameters?  I think I do add the parameters in order to perform database insert.

Is there any code mistake or wrong declaration in the addListToDB function???

The important part in my answer was "in the order in which they appear in the SQL".  @oID appears before @pID in the SQL:

...VALUES (@oID ,@pID,@Quantity)

but is added after it when you add the parameters:

            // Add Parameters to Command
            OleDbParameter pID = new OleDbParameter("@pID", OleDbType.Integer);
            pID.Value = cart.GetKey(i);
            myCommand.Parameters.Add(pID);

            OleDbParameter oID = new OleDbParameter("@oID", OleDbType.Integer);
            oID.Value = key;
            myCommand.Parameters.Add(oID);

Change the order in which they are added.  OleDb parameters work purely on position, not on name.

 so, it's nothing about code error ? Just the order in the code ??

Becuz I was worried about the Key value which supposed to get oID variable in adding function AddUserToDB()

 I tried to change the order of it, but it wont solve the problem.

The order detail table is not inserted with the value I input.

Anyone needs what's wrong ?

 Anyone knows where the problem is? 

I only have this problem for my  website....

As Mike said, first you must change the order of the parameters.

Second, did you try to debug already, are you sure your parameters do contain the right data? I see something in your code that maybe the problem.

You have 3 parameters, oID, pID and Quantity. They all are of type Integer. But when you fill the values of these parameters, it looks to me if they are objects, since they have a value property. So I think it should be:

MyCommand.Parameters.Add(oID.Value)
MyCommand,Parameters.Add(pID.Value)
MyCommand.Parameters.Add(Quantity.Value)