Click here to Skip to main content
15,881,882 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi..

currently im writing a database software using sqlite and this problem occurred.

http://i60.tinypic.com/1555hs9.png[^]

at the beginning, the datatable was empty and I typed "Foo" as the name and "Bar" as the Item and when I load the datatable again into the data grid view it contains another "Foo" person without the value I entered into the Item column.

Here's the codes..

C#
private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            int row = e.RowIndex;

            // retrieveing the ID cell of the changed row
            DataGridViewCell IDcell = dataGridView1["ID", row];

            // check if selected row is an existing row or new one
            bool newRow = false;
            if (IDcell.Value.ToString().Length == 0)
                newRow = true;
            else
                newRow = false;


            if (con.State != ConnectionState.Open)
                con.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = con;

            if (newRow)
            {
                string sql = @" insert into myTable(Name, Item)
                                values(@name, @item)";

                cmd.CommandText = sql;

                // setting parameters                
                cmd.Parameters.AddWithValue("@name", dataGridView1["Name", row].Value.ToString());
                cmd.Parameters.AddWithValue("@item", dataGridView1["Item", row].Value.ToString());
            }
            else
            {
                string sql = @" update myTable 
                                set
                                Name = @name,
                                Item = @item
                                where ID = @id";

                cmd.CommandText = sql;

                // setting parameters
                cmd.Parameters.AddWithValue("@name", dataGridView1["Name", row].Value.ToString());
                cmd.Parameters.AddWithValue("@item", dataGridView1["Item", row].Value.ToString());
                cmd.Parameters.Add("@id", DbType.Int32).Value = int.Parse(dataGridView1["ID", row].Value.ToString());
            }

            cmd.ExecuteNonQuery();

            con.Close();
        }


what's the wrong with my code ?
I dont want some meaningless Foo without the Bar.

Thanks in advance !
Posted

1 solution

Your logic is very, very flawed.

Your code is executing whenever the value of any cell changes. OK, fine. Be careful.

You're checking the value of the DGV column called "ID" for the row that changed. (Seriously, if you're giving the user access to the PrimaryKey column of your table you're making a HUGE mistake! A corrupted dataset will ensue.) If this ID cell doesn't have a value (that's a terrible way to check for a value by the way!), execute a database insert. OK.

On the next call to CellValueChanged, you again check the DGV for an ID for that row. Hmmm... there's ID value even though you wrote the data to the database on the last call to CellValueChanged.

Why? Because you never reloaded the DataTable from the database to get the updated ID value from the database and never rebound the grid to this new data. Just because you write a value to the database doesn't mean that the DGV knows that the database changed. It doesn't! It's only looking at the DataTable you bound it to and even the DataTable doesn't know the database changed.


Why are you trying to write data to the database on every cell change? Is this an absolute requirement? Is it acceptable to have incomplete records? What if the user enters data in one field of a record? Is it acceptable that ALL OTHER FIELDS of that record are null?
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900