How to Export data from Listview and Save in DataBase

Question

i am working on inventory software in which I've saved data in list view and now i want to save data in list view in MY SQL where I've table with same fields as in list view, i want whenever i click on my Finish button the data just go and save into the Table in list view, the Code for Adding data in Table is :

 {    
            int totalPrice = 0;
            int val1, val2;
            string totalp;

            val1 = Convert.ToInt32(txtProductPrice.Text);
            val2 = Convert.ToInt32(txtProductQuantity.Text);
            totalPrice = val1 * val2;

            totalp = Convert.ToString(totalPrice);

            //   int totalPrice = 0;

            lvProductInfo.Items.Add(""); //  QuestionID 
            lvProductInfo.Items[lvProductInfo.Items.Count - 1].SubItems.Add(txtProdcutCode.Text); //Question
            lvProductInfo.Items[lvProductInfo.Items.Count - 1].SubItems.Add(txtProductQuantity.Text); //Option1
            lvProductInfo.Items[lvProductInfo.Items.Count - 1].SubItems.Add(txtProductPrice.Text); //Option2
            lvProductInfo.Items[lvProductInfo.Items.Count - 1].SubItems.Add(totalp); //Option3

            //clearing text boxes.
            txtProdcutCode.Clear();
            txtProductQuantity.Clear();
            txtProductPrice.Clear();
            txtCashRecived.Clear();

            //focusing on product code.
            txtProdcutCode.Focus();
        }

now i am confused how can i access data from list view and save it in consecutive fields in the Table of SQL, the fields name in table are ItemNo, ProductCode, ProductQuantity, ProductPrice.

EDIT:

I've tried the Following code to Add data into the Database table, i am able to add each thing (like productPrice,ProductQuantity) except the Product code which is in String Type. the Code i tried is :

  private void button1_Click(object sender, EventArgs e)
        {
            string sql;

            int ProductQuantity = 0;
            string ProductCode;
            int ProductPrice = 0;
            int totalPrice = 0;

            for (int i = 0; i < lvProductInfo.Items.Count; i++)
            {
                ProductCode += Convert.ToInt32(lvProductInfo.Items[i].SubItems[1].Text);
                ProductQuantity += Convert.ToInt32(lvProductInfo.Items[i].SubItems[2].Text);
                ProductPrice += Convert.ToInt32(lvProductInfo.Items[i].SubItems[3].Text);
                totalPrice += Convert.ToInt32(lvProductInfo.Items[i].SubItems[4].Text);
            }

            sql = "";
            sql += "INSERT INTO PurchaseLog (ProductCode,ProductQuantity,ProductPrice,TotalPrice)";
            sql += " VALUES ('" + ProductCode + "','" + ProductQuantity + "','" + ProductPrice + "','" + totalPrice + "')";

            clsConnection clsCn = new clsConnection();
            SqlConnection cn = null;
            SqlCommand cmd = new SqlCommand();

            clsCn.fnc_ConnectToDB(ref cn);

            cmd.Connection = cn;
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();                

            this.Close();
        }

but i am not able to Add Product Code into the Database Table. Please guide me Through it.

score 2 · Accepted Answer · answered Jan 08 '14 at 11:07

That's amusing! You need to extend you for loop and stop concatenating values onto you variables. If you run the debugger and look at the sql string you produce you'll see what's happening.

Code should be this:

    for (int i = 0; i < lvProductInfo.Items.Count; i++)
    {
        ProductCode = Convert.ToInt32(lvProductInfo.Items[i].SubItems[1].Text);
        ProductQuantity = Convert.ToInt32(lvProductInfo.Items[i].SubItems[2].Text);
        ProductPrice = Convert.ToInt32(lvProductInfo.Items[i].SubItems[3].Text);
        totalPrice = Convert.ToInt32(lvProductInfo.Items[i].SubItems[4].Text);


        sql = "";
        sql = "INSERT INTO PurchaseLog (ProductCode,ProductQuantity,ProductPrice,TotalPrice)"
            + " VALUES ('" + ProductCode + "','" + ProductQuantity + "','" + ProductPrice + "','" + totalPrice + "')";

        clsConnection clsCn = new clsConnection();
        SqlConnection cn = null;
        SqlCommand cmd = new SqlCommand();

        clsCn.fnc_ConnectToDB(ref cn);

        cmd.Connection = cn;
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();


        this.Close();
    }

}

Note no more "+=" and you add the ListViewItems one by one.

How to Export data from Listview and Save in DataBase

EDIT:

1 Answers1