We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Adding rows from listview to database

linkcube1
linkcube1 asked
on
Medium Priority
651 Views
Last Modified: 2012-05-11
I'm running vs 2008 using C# windows forms. I'm trying to get a query I made to add items from a listview to a database to work But I can't figure out why its not entering into the database. The picture is my listview table and the code is a foreach loop that goes though each row and adds it to the database using ListView.Item.Subitem.  I also included the my load method to show how the items are being added to the listView. Please help! dfdf
public static void addtoCashedOutBooks(ListView listView1)
        {
            SqlCeConnection con = BookStoreDB.GetConnection();
            string insertStatement =
                "INSERT CashedOutBooks " +
                " (BookID, MemberID, Title, Author, Price)" +
                "VALUES (@BookID, @MemberID, @Title, @Author, @Price)";
            SqlCeCommand insertCommand =
                new SqlCeCommand(insertStatement, con);
            con.Open();
            SqlCeCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@BookID", listView1.Items[0].SubItems[0].Text);
            cmd.Parameters.AddWithValue("@MemberID", listView1.Items[0].SubItems[1].Text);
            cmd.Parameters.AddWithValue("@Title", listView1.Items[0].SubItems[2].Text);
            cmd.Parameters.AddWithValue("@Author", listView1.Items[0].SubItems[3].Text);
            cmd.Parameters.AddWithValue("@Price", listView1.Items[0].SubItems[4].Text);
            //comm.Parameters.AddWithValue("@MemberID", listView1.SelectedItems[0].SubItems[1].Text);
            //comm.Parameters.AddWithValue("@Title", listView1.SelectedItems[0].SubItems[2].Text);
            //comm.Parameters.AddWithValue("@Author", listView1.SelectedItems[0].SubItems[3].Text);
            //comm.Parameters.AddWithValue("@Price", listView1.SelectedItems[0].SubItems[4].Text);
            //comm.ExecuteNonQuery();
            con.Close();

        }

Open in new window

int ID = 18;
        decimal CashOutTotal;
        private void frmMemberCashOutList_Load(object sender, EventArgs e)
        {
            SqlCeConnection con = BookStoreDB.GetConnection();
            SqlCeCommand comm = new SqlCeCommand("SELECT * FROM CashOutDB Where MemberID= '" + ID + "'", con);
            con.Open();
            SqlCeDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                string[] data = new string[6];
                data[0] = reader["BookID"].ToString();
                data[1] = reader["MemberID"].ToString();         
                data[2] = reader["Title"].ToString();
                data[3] = reader["Author"].ToString();
                data[4] = reader["Price"].ToString();
                ListViewItem liv = new ListViewItem(data);
                listView1.Items.Add(liv);
               // member.PinNumber = (int)reader["MemberTotalPrice"]; //grab the total price to text box

            }
            con.Close();
        }

        private void btnCashOut_Click(object sender, EventArgs e)
        {
            foreach (ListViewItem item in listView1.Items)
            {
                CashOutBooksDB.addtoCashedOutBooks(listView1);
                // CashOutBooksDB.deleteFromList(listView1);
            }
        }

Open in new window

Comment
Watch Question

Carlos VillegasFull Stack .NET Developer

Commented:
Hi, in your addtoCashedOutBooks method, you need to pass a ListViewItem, instead of the ListView.
Carlos VillegasFull Stack .NET Developer

Commented:
Like:
public static void addtoCashedOutBooks(ListViewItem item)
{
    SqlCeConnection con = BookStoreDB.GetConnection();
    string insertStatement =
        "INSERT CashedOutBooks " +
        " (BookID, MemberID, Title, Author, Price)" +
        "VALUES (@BookID, @MemberID, @Title, @Author, @Price)";
    SqlCeCommand insertCommand =
        new SqlCeCommand(insertStatement, con);
    con.Open();
    SqlCeCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@BookID", item.SubItems[0].Text);
    cmd.Parameters.AddWithValue("@MemberID", item.SubItems[1].Text);
    cmd.Parameters.AddWithValue("@Title", item.SubItems[2].Text);
    cmd.Parameters.AddWithValue("@Author", item.SubItems[3].Text);
    cmd.Parameters.AddWithValue("@Price", item.SubItems[4].Text);
    cmd.ExecuteNonQuery();
    //comm.Parameters.AddWithValue("@MemberID", listView1.SelectedItems[0].SubItems[1].Text);
    //comm.Parameters.AddWithValue("@Title", listView1.SelectedItems[0].SubItems[2].Text);
    //comm.Parameters.AddWithValue("@Author", listView1.SelectedItems[0].SubItems[3].Text);
    //comm.Parameters.AddWithValue("@Price", listView1.SelectedItems[0].SubItems[4].Text);
    //comm.ExecuteNonQuery();
    con.Close();
}

Open in new window


and:
private void btnCashOut_Click(object sender, EventArgs e)
{
    foreach (ListViewItem item in listView1.Items)
    {
        // Pass the item, not the list
        CashOutBooksDB.addtoCashedOutBooks(item);
        // CashOutBooksDB.deleteFromList(listView1);
    }
}

Open in new window


Also I sugest to change your code to open and close the connection on the btnCashOut_Click method, then pass the command instance from this method to addtoCashedOutBooks, like this:

addtoCashedOutBooks(SqlCeCommand cmd, ListViewItem item);

Open in new window

Carlos VillegasFull Stack .NET Developer

Commented:
mmm by the way, that command is wrong, I think that you want this:
SqlCeConnection con = BookStoreDB.GetConnection();
string insertStatement =
    "INSERT CashedOutBooks " +
    " (BookID, MemberID, Title, Author, Price)" +
    "VALUES (@BookID, @MemberID, @Title, @Author, @Price)";
SqlCeCommand insertCommand = new SqlCeCommand(insertStatement, con);
con.Open();
insertCommand.CommandType = CommandType.Text;
insertCommand.Parameters.AddWithValue("@BookID", item.SubItems[0].Text);
insertCommand.Parameters.AddWithValue("@MemberID", item.SubItems[1].Text);
insertCommand.Parameters.AddWithValue("@Title", item.SubItems[2].Text);
insertCommand.Parameters.AddWithValue("@Author", item.SubItems[3].Text);
insertCommand.Parameters.AddWithValue("@Price", item.SubItems[4].Text);
insertCommand.ExecuteNonQuery();
con.Close();

Open in new window

Full Stack .NET Developer
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
that works thanks!
Carlos VillegasFull Stack .NET Developer

Commented:
Good to know bro
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.