Why doesn't GetColumnError or GetColumnsInError return anything when the dataset HasErrors?

  • Thread starter Thread starter Tim Frawley
  • Start date Start date
T

Tim Frawley

If I do the following code I get nothing returned.

Dim i As Integer
For i = 0 To ds.Tables(0).Columns.Count - 1
If ds.Tables(0).Rows(0).HasErrors Then
MsgBox(ds.Tables(0).Rows(0).GetColumnError(i))
End If
Next

I get a message box for every column and message boxes are always blank. Why?
 
Tim Frawley said:
If I do the following code I get nothing returned.

Dim i As Integer
For i = 0 To ds.Tables(0).Columns.Count - 1
If ds.Tables(0).Rows(0).HasErrors Then
MsgBox(ds.Tables(0).Rows(0).GetColumnError(i))
End If
Next

I get a message box for every column and message boxes are always blank.
Click to expand...
Why?

Maybe the error is not a column error?
 
(e-mail address removed) (Tim Frawley) wrote in
If I do the following code I get nothing returned.

Dim i As Integer
For i = 0 To ds.Tables(0).Columns.Count - 1
If ds.Tables(0).Rows(0).HasErrors Then
MsgBox(ds.Tables(0).Rows(0).GetColumnError(i))
End If
Next

I get a message box for every column and message boxes are always
blank. Why?
Click to expand...

Use the locals window while debugging and look at the individual row. Look
for where the errors are located. This will give you clues as to where you
need to change your code.
 
Hi Tim,

I cannot reproduce your problem with such limited codes. Here's a KB
article that shows how to obtain extended error information in the DataSet
by using Visual Basic .NET.

http://support.microsoft.com/default.aspx?scid=kb;en-us;308058

According to the KB, you can first check DataSet.HasErrors. And then the
DataTable.HasErrors, and then the rows. If you use DataRow.SetColumnError
method to set error to a column, did you pass an empty string to the Error
Message parameter?

If there’s anything unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Thank you John, Michael, and Kevin for your responses, I will attempt
to be more specific here...

I populate the dataset like so:

Private Function PopulateDataGrid() As Boolean
pstrSQL = "select * from " & pstrTable & " order by 1"
Dim strError As String
Dim da As New OracleDataAdapter(pstrSQL, gstrConnectionString)
Try
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(dsData, pstrTable)
tdbgData.DataSource = dsData.Tables(pstrTable)
Catch Ex As Exception
MsgBox(Ex.ToString)
Return False
Finally
da.Dispose()
da = Nothing
End Try
Return True
End Function


I update the database on a Save Click event. Due to the number of
changes, additions and deletions I must update each row individually
because, if I do not, then these changes may step on each other. I
tried it, it didn't work.

In any case I loop trough the primary dataset looking for the
appropriate RowState and then import that row into a new dataset and
attempt to update it. I catch the errors and then attempt to find
where the error occurred. This is where I would like to test the
GetColumnError so that I would know which cell in the datagrid to
place the focus on as that is where the error occurred. Please see
the 'ADDED (INSERTED) ROWS' Catch statement where I have a loop
looking for the column error.

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSave.Click
Dim strSQL As String
Dim intX As Integer
Dim strError As String, strErrorMessage As String
If dsData.HasChanges Then
Cursor.Current = Cursors.WaitCursor
strSQL = "select * from " & pstrTable
Dim da As New OracleDataAdapter(strSQL, gstrConnectionString)
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim objCommandBuilder As New OracleCommandBuilder(da)
Dim ds As New DataSet
ds = dsData.Clone ' Create a new dataset we will update on.
ds.Tables(pstrTable).Clear() ' Clear all the data from this
new datasets datatable
Dim dr As DataRow
Try
'*/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=*
'*
'* UPDATE DATABASE IN ORDER, DELETED, MODIFIED, ADDED
(INSERT)
'*
'* Enclosing Try block will catch any exceptions that
occur outside of the loops try blocks.
'*
'#=========================================
'#
'# DELETED ROWS
'#
'# Only this loop has been commented as the action is the
same for all three loops
'#
For intX = 0 To dsData.Tables(pstrTable).Rows.Count - 1
If dsData.Tables(pstrTable).Rows.Count - 1 < intX Then
' Test for row deletion as removing a row
permanently from the dataset will drop the count lower
' causing an exception to occur.
Exit For
End If
If dsData.Tables(pstrTable).Rows(intX).RowState =
DataRowState.Deleted Then
dr = dsData.Tables(pstrTable).Rows(intX) ' Get the
row to update from our authoratative dataset.
ds.Tables(pstrTable).ImportRow(dr) ' Import that
row, update each row individually.
Try
If ds.Tables(pstrTable).Rows(0).HasErrors Then
MsgBox("Has Errors!")
End If
da.Update(ds, pstrTable) ' Try database update
strError = "" ' Clear string error as if we
arrive at this line then the update was successful.
Catch Ex As Exception
strError = Mid(Ex.Message, 1, 9) ' Retrieve
the ORA-##### error number to deterine the nature of the error.
Select Case strError
Case "ORA-02292" ' integrity constraint
violated

dsData.Tables(pstrTable).Rows(intX).RejectChanges()
strErrorMessage = strErrorMessage &
"Row #" & CType(intX + 1, String) & " You are not allowed to destroy
an " & _
"existing code that
CWT data currently references. This row will be restored." & vbCrLf
Case Else ' Currently unknown error
MsgBox("Row #" & CType(intX + 1,
String) & vbCrLf & Ex.ToString & vbCrLf)
End Select
End Try
If strError = "" Then
' Update successful, accept changes in
dataset to clear HasChanges property for this row

dsData.Tables(pstrTable).Rows(intX).AcceptChanges()
' Because AcceptChanges will renumber the
dataset rows set
' row number back one to catch any additional
deletes.
intX = intX - 1
End If
End If
ds.Tables(pstrTable).Clear()
pbSaving.Value = pbSaving.Value + 1
Next
'#=========================================
'#
'# MODIFIED ROWS
'#
For intX = 0 To dsData.Tables(pstrTable).Rows.Count - 1
If dsData.Tables(pstrTable).Rows(intX).RowState =
DataRowState.Modified Then
dr = dsData.Tables(pstrTable).Rows(intX)
ds.Tables(pstrTable).ImportRow(dr)
Try
da.Update(ds, pstrTable)
strError = ""
Catch Ex As Exception
strError = Mid(Ex.Message, 1, 9)
Select Case strError
Case "ORA-02291"
strErrorMessage = strErrorMessage &
"Row #" & CType(intX + 1, String) & " " & EX.Message
Case "ORA-02292"

dsData.Tables(pstrTable).Rows(intX).RejectChanges()
strErrorMessage = strErrorMessage &
"Row #" & CType(intX + 1, String) & " You are not allowed to destroy
an " & _
"existing code that
CWT data currently references. This row will be restored." & vbCrLf
Case Else
MsgBox("Row #" & CType(intX + 1,
String) & vbCrLf & Ex.ToString & vbCrLf)
End Select
End Try
If strError = "" Then

dsData.Tables(pstrTable).Rows(intX).AcceptChanges()
End If
End If
ds.Tables(pstrTable).Clear()
Next
'#=========================================
'#
'# ADDED (INSERTED) ROWS
'#
For intX = 0 To dsData.Tables(pstrTable).Rows.Count - 1
If dsData.Tables(pstrTable).Rows(intX).RowState =
DataRowState.Added Then
dr = dsData.Tables(pstrTable).Rows(intX)
ds.Tables(pstrTable).ImportRow(dr)
Try
da.Update(ds, pstrTable)
strError = ""
Catch Ex As Exception
strError = Mid(Ex.Message, 1, 9)
Select Case strError
Case "ORA-02291"
strErrorMessage = strErrorMessage &
"Row #" & CType(intX + 1, String) & " You are trying to enter a code "
& _
"that's not defined.
Use an already valid code, or add the new code to its lookup table."
Dim i As Integer
For i = 0 To
ds.Tables(0).Columns.Count - 1
If ds.Tables(0).Rows(0).HasErrors
Then
' Why does the GetColumnError
always return an empty string event though the Row HasErrors?

MsgBox(ds.Tables(0).Rows(0).GetColumnError(i))
End If
Next
Case "ORA-02292"

dsData.Tables(pstrTable).Rows(intX).RejectChanges()
strErrorMessage = strErrorMessage &
"Row #" & CType(intX + 1, String) & " You are not allowed to destroy
an " & _
"existing code that
CWT data currently references. This row will be restored." & vbCrLf
Case Else
MsgBox("Row #" & CType(intX + 1,
String) & vbCrLf & Ex.ToString & vbCrLf)
End Select
End Try
If strError = "" Then

dsData.Tables(pstrTable).Rows(intX).AcceptChanges()
End If
End If
ds.Tables(pstrTable).Clear()
Next
Catch ex As Exception
MsgBox(ex.ToString)
Finally
da.Dispose()
da = Nothing
ds.Dispose()
ds = Nothing
dr = Nothing
End Try
End If
Cursor.Current = Cursors.Arrow
End Sub
 
Thank you very much for your help.

You are right, the "Updating the Database with a DataAdapter and the
DataSet" would simplify my code.

Do you know what will get updated when I do an update using this
method: "custDA.Update(updTable.Select(Nothing, Nothing,
DataViewRowState.ModifiedCurrent))" and an error is generated? Would
all the updates that didn't have an error be sent to the database or
would the updates prior to the error be sent to the database or would
all the updates be rolled back, or etc.?

This is important as what I am attempting in my code is to process ALL
updates that possibly can go through and display errors in a "grocery
list" message for those that cannot.


As for SetColumnError I could use the SetColumnError method if I knew
which columns to set the error information for. The window I am
creating allows the modification of over 30 lookup tables in our
database with differing requirements from nulls not allowed to
Integrity constraints, Foreign Key constraints, Primary Keys, etc.

Is it possible once the dataset has been filled that you can look for
these types of constraints (specifically Integrity and Foreign Key
constraints) in order to set the appropriate Column Error accordingly?

I do know that I can test the AllowDBNull and Unique properties but I
cannot find a way to look for the Integrity and Foreign Key
contstraints.

I can tell that an error has occurred in a row, is there anyway,
dynamically, to find the offending value/column that is generating the
error without using a SetColumnError method?
 
Hi Tim,

By default, if an error is generated during updating, only updates prior to
the error will be sent to the database. However, you can change this
behavior by changing DataAdapter.ContinueUpdateOnError to true. It will
send all the updates that didn't have an error to the database. Then you
can check the HasErrors property to see if it contains error, or if you
want to process the errors during updating, you can handle the RowUpdating
and RowUpdated events. The RowUpdatingEventArgs and RowUpdatedEventArgs
contain the error message.

See the following for more information:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconupdatingdatabasewithdataadapterdataset.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatacommonrowupdatedeventargsmemberstopic.asp

If you want to get the constraints of a table, please check the
DataTable.Constraints property. Check the following link for more
information:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataDataTableClassConstraintsTopic.asp

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top