Field allows null but still get error!

[pachjo]

I have a single table with 7 columns, one which can be null.

When I run the following:

 da = New System.Data.OleDb.OleDbDataAdapter("select [PO No] as po_no, [Part No] as part_no, " & _
                                                     "description, vendor, pr, [Order Date] as registered_date, " & _
                                                     "Ordered from [sheet1$]", cnn)

        ' load into import table

        da.Fill(ds.POImports)

I get the error "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

Even if I set the field in the .xls file to something still the error appears.

Even if I set the entire table to to allow null in all fields I get the error.

The table has be deleted and recreated and has no link to any other table so I cannot see where it is falling over?

Help any one?

 

[jmcilhinney]

Try getting the data with a DataReader instead. That way you can at least see what data the query is retrieving and then you can hopefully determine where the issue is.

 

[pachjo]

Hi, thanks.

I managed to resolve it like this:

da = New System.Data.OleDb.OleDbDataAdapter("select [PO No] as po_no, [Part No] as part_no, " & _
                                                        "description, vendor, iif(pr is null,0,pr) as pr, [Order Date] as registered_date, " & _
                                                        "Ordered from [sheet1$]", cnn)

 

[cjard]

The table has be deleted and recreated and has no link to any other table so I cannot see where it is falling over?

Help any one?

Search the forums for Nitpick.. its a dll I wrote a little while ago.. you would use it something like (exact info is in the thread):

Dim ds as DataSet 'whatever dataset operation
Try
  'database operation
Catch Exception
  MessageBox.Show(NitPick.WhatsWrong(ds))
End Try

You either get a stringbuilder or a string back out of it containing e.g.
"In Table 1 [name x], row 4, column 2: The value 'abc' is longe rthan the limit allowed for this column"

Or something


Its not rocket science, you can do this yourself like:

For Each dr as DataRow in MyDataTable
if dr.RowError <> String.Empty Then MessageBox.Show(dr.RowError)


I just found that dataset stops on the first error, so I would clone the set, find the first error, remove the row, reenable constraints, find the next error... and so on for 1000 errors... Just take a look for it, and youll find out what the problem really was


By the way.. you cant always make number columns null, it depends on the number in use.. some will throw an invalidcast exception.. It doesnt matter if you AllowNull in your datatable, if you set the Null Action to be (throw Exception)

 

[pachjo]

Cool cjard, I will look at nitpick excellent!

Thanks for the tip