• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • Last Modified:

Adding rows from listview to database

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

0
linkcube1
Asked:
linkcube1
  • 5
1 Solution
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hi, in your addtoCashedOutBooks method, you need to pass a ListViewItem, instead of the ListView.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hi again, to insert the list items into your table, try with this code:
private void btnCashOut_Click(object sender, EventArgs e)
{
    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);
    insertCommand.CommandType = CommandType.Text;

    // Is better set the right type of each param instead of using AddWithValue method.
    SqlCeParameter pBookId = insertCommand.Parameters.AddWithValue("@BookID", "");
    SqlCeParameter pMemberID = insertCommand.Parameters.AddWithValue("@MemberID", "");
    SqlCeParameter pTitle = insertCommand.Parameters.AddWithValue("@Title", "");
    SqlCeParameter pAuthor = insertCommand.Parameters.AddWithValue("@Author", "");
    SqlCeParameter pPrice = insertCommand.Parameters.AddWithValue("@Price", "");
            
    con.Open();
    // Prepare the command is recomemded if supported.
    //insertCommand.Prepare();
    foreach (ListViewItem item in listView1.Items)
    {
        pBookId.Value = item.SubItems[0].Text;
        pMemberID.Value = item.SubItems[1].Text;
        pTitle.Value = item.SubItems[2].Text;
        pAuthor.Value = item.SubItems[3].Text;
        pPrice.Value = item.SubItems[4].Text;
        // Do the insert
        insertCommand.ExecuteNonQuery();
    }
    con.Close();
}

Open in new window

0
 
linkcube1Author Commented:
that works thanks!
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Good to know bro
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now