Re: Cannot save data into access database table

hans_v — Thu, 28 Aug 2008 10:01:53 GMT

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)

Re: Cannot save data into access database table

photossed — Thu, 28 Aug 2008 02:38:31 GMT

Anyone knows where the problem is?

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

Re: Cannot save data into access database table

photossed — Thu, 28 Aug 2008 01:01:43 GMT

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 ?

Re: Cannot save data into access database table

photossed — Wed, 27 Aug 2008 23:10:09 GMT

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()

Re: Cannot save data into access database table

Mikesdotnetting — Wed, 27 Aug 2008 22:45:55 GMT

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.

Re: Cannot save data into access database table

photossed — Wed, 27 Aug 2008 22:28:40 GMT

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???

Re: Cannot save data into access database table

Mikesdotnetting — Wed, 27 Aug 2008 19:14:12 GMT

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

Cannot save data into access database table

photossed — Wed, 27 Aug 2008 08:30:50 GMT

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();
        }
    }
}

Access Databases and AccessDataSource Control

Re: Cannot save data into access database table

Re: Cannot save data into access database table

Re: Cannot save data into access database table

Re: Cannot save data into access database table

Re: Cannot save data into access database table

Re: Cannot save data into access database table

Re: Cannot save data into access database table

Cannot save data into access database table