0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

DBから取得したデータをDataBindで表示しようとしたときに制約エラーなどが生じたときの対処

Posted at

DBから取得したデータをListViewに表示しようとしたら制約エラーが生じたが、
取得したデータのどのレコードのどのカラムが原因かが分からない場合の対処方法。

before.aspx.vb
PageDataSet.SampleTable.Clear()
Using ta As New dsMainPageTableAdapters.SampleTableTableAdapter(ConnectionString)
  ta.Fill(PageDataSet.SampleTable)

  '取得したデータをListViewにDataBind
  lvListTable.DataBind()
End Using

SQLが間違っていて、Nullがあったり、重複が許されない値が重複していたりして制約エラーが生じた。
そこで、以下のようにログを出して原因を特定した。

PageDataSet.SampleTable.Clear()
+ PageDataSet.EnforceConstraints = False
Using ta As New dsMainPageTableAdapters.SampleTableTableAdapter(ConnectionString)
  ta.Fill(PageDataSet.SampleTable)
+  Try
+    PageDataSet.EnforceConstraints = True
+  Catch ex As ConstraintException
+    Dim errorRows As DataRow() = PageDataSet.ExtensionTable.GetErrors()
+    For Each row As DataRow In errorRows
+      Dim msg As String = "▼ エラー行 ID: " & SafeToString(row, "ID")
+      For Each col As DataColumn In row.GetColumnsInError()
+        msg &= vbCrLf & "列: " & col.ColumnName & " → エラー: " & row.GetColumnError(col)
+      Next
+      '必要に応じてログや画面出力
+    Next
+  End Try

  '取得したデータをListViewにDataBind
  lvListTable.DataBind()
End Using

+Private Function SafeToString(row As DataRow, columnName As String) As String
+    If row.Table.Columns.Contains(columnName) AndAlso Not IsDBNull(row(columnName)) Then
+       Return row(columnName).ToString()
+    Else
+        Return "(NULL)"
+    End If
+End Function
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?