robertb_NZ
Well-known member
I am a relative newbie with VB, having spend more time working with web pages using ASP.NET. I use VS2008.
In my database I had defined a table called "Questions", and I have a windows program with a datagridview (named "gdvQuestions") that displays a single text field from this table. Following my normal practice I have defined a tableadaptor, table, and tablerow called "taQuestions", "tbQuestions", and "trQuestions" respectively, and bound gdvQuestions to this. So far so good, everything works perfectly. However, if you make changes to the gridview, while these are reflected in the tableadaptor (as shown by other gridviews and controls on the form), they were not being updated back to the database. Edits were ignored (as far as the database was concerned), and inserts were causing an error.
I solved the problem for edits with the following code that performs an explicit update. For an existing row, the record key is column 0 (hidden): -
Private Sub gdvQuestions_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles gdvQuestions.CellEndEdit
Dim dgv As DataGridView = DirectCast(sender, DataGridView)
If e.RowIndex >= tbQuestions.Count Then ' New row inserted by entering into the bottom (insert) row of the grid
AddQuestion(dgv.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString)
Else
NTQid = dgv.Rows(e.RowIndex).Cells(0).Value
GetQuestion(NTQid)
trQuestions.NTQText = dgv.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString
taQuestions.Update(trQuestions)
End If
End Sub
If the row exists already we take the Else branch, update the tablerow field within trQuestions, and then update the database with taQuestions.update(trquestions). Works perfectly.
However for new records, after correctly adding the new record to the database through AddQuestion, the program then fails on exiting the event handler, with a message: -
"The following exception occurred in the DataGridView:
System.Data.NoNullAllowedException: Column 'NTQid' does not permit nulls.
at ......
The issue is that after the Insert has been done successfully within AddQuestion, the system then attempts to do the default Insert that is within the bound datasource. It can't do this because it doesn't have enough data, so of course it fails.
What I want to do is to tell the system not to bother: I've already done the Insert. But how do I do this? I looked on the web where it seemed that I should execute "AcceptChanges". The only object that has this method is the table, tbQuestions, there is no AcceptChanges method or property with gdvQuestions, taQuestions, or trQuestions. However if I add: -
taQuestions.AcceptChanges()
into this event handler, the only effect is to cause the error message to appear on this statement instead of after the END statement. taQuestions.RejectChanges() is equally useless.
I wondered about "e.cancel", but in this situation e doesn't have once.
So what do I do next?
Thank you, Robert.
In my database I had defined a table called "Questions", and I have a windows program with a datagridview (named "gdvQuestions") that displays a single text field from this table. Following my normal practice I have defined a tableadaptor, table, and tablerow called "taQuestions", "tbQuestions", and "trQuestions" respectively, and bound gdvQuestions to this. So far so good, everything works perfectly. However, if you make changes to the gridview, while these are reflected in the tableadaptor (as shown by other gridviews and controls on the form), they were not being updated back to the database. Edits were ignored (as far as the database was concerned), and inserts were causing an error.
I solved the problem for edits with the following code that performs an explicit update. For an existing row, the record key is column 0 (hidden): -
Private Sub gdvQuestions_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles gdvQuestions.CellEndEdit
Dim dgv As DataGridView = DirectCast(sender, DataGridView)
If e.RowIndex >= tbQuestions.Count Then ' New row inserted by entering into the bottom (insert) row of the grid
AddQuestion(dgv.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString)
Else
NTQid = dgv.Rows(e.RowIndex).Cells(0).Value
GetQuestion(NTQid)
trQuestions.NTQText = dgv.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString
taQuestions.Update(trQuestions)
End If
End Sub
If the row exists already we take the Else branch, update the tablerow field within trQuestions, and then update the database with taQuestions.update(trquestions). Works perfectly.
However for new records, after correctly adding the new record to the database through AddQuestion, the program then fails on exiting the event handler, with a message: -
"The following exception occurred in the DataGridView:
System.Data.NoNullAllowedException: Column 'NTQid' does not permit nulls.
at ......
The issue is that after the Insert has been done successfully within AddQuestion, the system then attempts to do the default Insert that is within the bound datasource. It can't do this because it doesn't have enough data, so of course it fails.
What I want to do is to tell the system not to bother: I've already done the Insert. But how do I do this? I looked on the web where it seemed that I should execute "AcceptChanges". The only object that has this method is the table, tbQuestions, there is no AcceptChanges method or property with gdvQuestions, taQuestions, or trQuestions. However if I add: -
taQuestions.AcceptChanges()
into this event handler, the only effect is to cause the error message to appear on this statement instead of after the END statement. taQuestions.RejectChanges() is equally useless.
I wondered about "e.cancel", but in this situation e doesn't have once.
So what do I do next?
Thank you, Robert.
