Newbie: Am I doing this right?

[GriffMG]

Hi,

I'm new to C# and I have just started a pretty big conversion from Clipper... an early hurdle has been, what should have been, a really simple form with a table in SQL displayed in a DataGridView. My problem has been concurrency violations, particularly when I've tried to delete a record programatically - which I need to do because a) I want to do some validation first and b) for some reason the interactive delete from 'Allow Users to Delete' will not delete the last record in the table (it deletes it from the grid, but not the underlying table).

So, DataGridView, linked (bound) to a DataTable and the only way that actually seems to work is not to 'Delete' the record from the datatable, but to make a note of the identity (if that is available - new records don't have one in a DatagridView!) use a remove and then issue a SQL command to delete the underlying record.

Thus I end up with this:

        private void btnDeleteElement_Click(object sender, EventArgs e)
        {
            int i = 0;
            foreach (DataGridViewRow drv in dgvElements.SelectedRows)
            {
                if (drv.IsNewRow )
                {
                    MessageBox.Show("You Cannot Delete the 'New record line'", "Problem", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    drv.Selected = false;
                    i = -1;
                    break;
                }
                else
                {
                    i++;
                }
            }
            if(i > 0)
            {
                if (MessageBox.Show("Delete " + i.ToString() + " Record(s)", "Are You Sure", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    SqlCommand command = validConnection.CreateCommand();
                    foreach (DataGridViewRow drv in dgvElements.SelectedRows)
                    {
                        string strSearch = drv.Cells[1].Value.ToString();
                        // use a select filter, because it works better when you delete records
                        foreach (DataRow row in dataTable.Select("Description='" + strSearch.Replace("'", "''") + "'"))
                        {
                            dataTable.Rows.Remove(row);
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@Description", strSearch);
                            command.CommandText = "Delete from tblElements where Description = @Description ";
                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

This allows my user to select a number of records, then check that none of them are the 'new record' and then processes the list as I described above.

As I'm planning to use this, very simple, form as the template for the other ones I need - I want to be sure I have the right approach. Am I in the ball park?

In my example, I'm not using the identity field to delete the records (as I said I don't seem to have one for newly added records - at least not in the grid rows) but the Descripton field, which is also unique, and I'm doing a replace in the search variable to weed out single quotes - which are allowed in the field.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[artnethouse]

Why not to build around this?
cmd.Parameters.Add(new SqlCeParameter("@Id", dt.Rows["Id", DataRowVersion.Original].ToString()));
cmd.CommandText = "your query";
cmd.ExecuteNonQuery();

Also identity field - is a must. Create it.

 

[GriffMG]

Hi

The table has an identity field, for existing records this shows (for the time being) on the datagridview - but not for newly added records... it's driving me mad this dgv

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[artnethouse]

Hi!
Every new records can be accomodated by a set of mandatory values, you should generate id, if you want - keep it invisible.
It should behave like a normal field value, check carefully sequence of events.
Also very useful thing - create small test project, do things there, see difference. Check DataRowVersion in cycle. Good luck

 

[GriffMG]

Hi Artnethouse

I think you misunderstand, the underlying table has an autoincrement identity field (the first field in the table) and when I add records using the dgv the underlying table is getting new records and is showing the identity very well.

It is the dgv that is not showing the identity field, it lets me complete all the others and I can (to a point) validate them and make sure there are no nul problems.

But, and it is driving me crazy, when you use the 'new record' item on the dgv to add a new record, it appears on the dgv WITHOUT an identity - the column is nul, and it is nul in the linked datatable - so I can't use it to locate the underlying records.

Is there a way to encourage the dgv/datatable to provide the ID field?



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[artnethouse]

Hi
Just now did test - autoincrement works,
but in your case - does it refresh datagrid after every operation?
It is about events again. Seems there is not exchange between data table and grid in correct moment.

 

[GriffMG]

I'm trying to find the right place to do an update... but no luck yet

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[artnethouse]

Hi something else to check this:

DataTable dt = new DataTable();
BindingSource bs = new BindingSource();
radGridView1.EndEdit();
bs = (BindingSource)radGridView1.DataSource;
dt = ((DataTable)bs.DataSource).GetChanges();
if (dt != null)
{
//do

 

[GriffMG]

Thank you Art,

Where should I be doing that?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.