Delete rows from DataGridView and Table Permanently
Please, Could anyone help me or point me to an article or the Code necessary.
I am having no luck solving this.
I need todelete a row from both aDataGridView and its bindedDatabase tableprogrammatically.I would attach the "delete code" to the Button on the form.
I would point to the row in the GridView and press that button on the form.
I cannot use theTableBindingNavigator because of other requirements.
Also, I need to make sure that the deleted row stays deleted permanently.
I have been battling with this and cannot seem to acheive a permanent delete.
Every thing seems to delete correctly, but if I exit this form back to the Main form and then return again, all my deleted items reappear.
I can supply my code if necessary. I think it is somewhere in the permanent database binding.
I am NOT very experienced in VB so any help would be greatly appreciated.
Thanks
Bernard
Thanks Martin,
You are right , a little complex at my level but I will try it as soon as I get on my computer at home. it seems to make sense although there are a couple of things I have never encountered before.
1. What if the database gets moved to a differrent directory after the program has been built and handed onto a different computer,
will the "Provider=Microsoft.........c:\SomeDataBAse.mdb" work?
Is there a way to hunt for this directory programatically perhaps?
And
2. in the SQL definition "Dim sqlString As String .... " line:
I know how to obtain my Main Id Value for the wanted row to delete,
Can I make the string something like this:
"DELETE FROM NewTable Where NewID =" & IRowIndex 'Programatically defined.
Thanks very much for your help and direction.
Bernard
Just for your interest, but please do not waste any of your time on this,
I have included the code that was giving me trouble and not keeping the deleted rows permanently deleted.
Private Sub DeleteButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteButton.Click
Dim NumberOfRows As Integer = 0
NumberOfRows = Me.Database1DataSet.NewTable.Rows.Count ‘# of rows in table
Dim MyDeleteRow As Database1DataSet.NewTableRow
Dim Icounter As Integer = 0 'local Looping counter
Dim IGridPos As Integer = NewTableBindingSource.Position 'Row Position in GridView clicked-on or highlit.(This is the row to be deleted).(Starting at 0)
‘This line of code is not needed
Dim IRowIndex As Integer ' Value in Column "NewID".
IRowIndex = NewTableBindingSource.Current("NewID") 'Column "NewID" Value in DataGridView. This is also the index value of the row in the Data Table.
For Icounter = 0 To (NumberOfRows - 1)
MyDeleteRow = Database1DataSet.NewTable(Icounter)
If MyDeleteRow.NewID = IRowIndex Then ‘Found row in Table
Me.Database1DataSet.NewTable.Rows(Icounter).Delete()
GoTo FoundAndDeleted 'Accept, EndEdit and Update.
End If
Next
MessageBox.Show("ERROR: this row does not exist in DataTable!!!" & vbCrLf & "Nothing was deleted")
GoTo EndNothing
FoundAndDeleted:
Me.Database1DataSet.AcceptChanges() 'This has to be used after Row Delete.
'Endedit and Update database
If Me.Validate Then
Me.NewTableBindingSource.EndEdit()
Me.NewTableTableAdapter.Update(Me.Database1DataSet.NewTable)
Else
System.Windows.Forms.MessageBox.Show(Me, "Validation error occured.", _
"Save", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning)
End If
EndNothing:
End Sub
This works. You'll need to replace specifics in your own situation.
This is in my public class Form1 -
private BindingSource bindingSource1 = new BindingSource();
private SqlDataAdapter dataAdapter = new SqlDataAdapter();
The Course_Array_Final function just cleans out spaces in the datagridview.
This is the function -
private void btnClean_Click(object sender, EventArgs e)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
Connect();
DialogResult drCDL = MessageBox.Show("You are about to send edited " +
"to the University server. Are you certain " +
"you want to do this?", "Upload to " +
"SERVER", MessageBoxButtons.YesNoCancel);
if (drCDL == DialogResult.No ||
drCDL == DialogResult.Cancel)
{
MessageBox.Show("The <whatever> table remains as is. No " +
"changes have been made.");
Disconnect();
return;
}
for (int x = dgvCoursePriority.Rows.Count - 2; x >= 0; x--)
{
DataGridViewRow dgvRow = dgvCoursePriority.Rows[x];
if (dgvCoursePriority.Rows[x].Cells["sect"].Value.ToString() != null)
{
if (dgvCoursePriority.Rows[x].Cells["sect"].Value.ToString() == "N")
{
dgvCoursePriority.Rows.Remove(dgvRow);
}
}
}
dataAdapter.Update((DataTable)bindingSource1.DataSource);
Course_Array_Final("<table>", "<table>");
this.Cursor = Cursors.Default;
MessageBox.Show("Your submission to the <whatever> table was " +
"successful.");
Disconnect();
}
