DataTable FillSchema error - Pulling my hair out!

[Henry Jones]

VB.NET VS 2005

When I try to fill the Dataset, I issue the following commands:

Dim objDA As New SqlDataAdapter(query, objConn)


objDA.SelectCommand.CommandTimeout = 0

Dim ds As New DataSet

objDA.FillSchema(ds, SchemaType.Source)

objDA.Fill(ds)



I get an error: Failed to enable constraints. One or more rows contain
values violating non-null, unique or foreign-key constraints. After pulling
the remaining hairs out of my head (which only took a couple of minutes!) ,
I finally figured out when I send a CASE statement in the query, I get the
error. All other queries work just fine. This only errors out when I use
the FillSchema command.



I found in the Microsoft ADO.NET book by David Sceppa, some code that should
trap the FillSchema problem.

objDA.MissingSchemaAction = MissingSchemaAction.Error

AddHandler objDA.FillError, AddressOf objDA_FillError

Public Sub objDA_FillError(ByVal sender As Object, ByVal e As
FillErrorEventArgs)

MessageBox.Show("You have an error in the Fill Schema Routine " & vbCrLf &
e.Errors.Message)

e.Continue = True

End Sub





but it doesn't work. Is there a way to trap errors with the FillSchema
command and gracefully handle the error?



Thanks,



Henry

 

[RobinS]

What page is it in the David Sceppa book?

Robin S.

 

[Henry Jones]

I'll check on Monday as my book is at work.

Henry

 

[RobinS]

Ok; I recently picked up a copy of that, but haven't
opened it yet. If I have time tomorrow, I'll see if
I can find the appropriate section.

Just to clarify the problem, you're trying to figure
out how to catch the error on the FillSchema? Can you
post the SQL you're using so I can try something similar?

Robin S.
----------------------------------------

 

[Henry Jones]

I think I found the page in the book. It's Chapter 5, "Retrieving Data
Using DataAdapter Objects" page 198, 199. I did some more testing and it
seems it only fails when a query contains a CASE statement. It works in SQL
server Management Console, but not using FillSchema.

The actual code I tried was on page 196 but found the error routines on
198-199.

Henry

 

[RobinS]

Can you post the query you're trying, with the CASE
statement in it? Or some kind of example of it that
I can use with Northwind or Pubs or AdventureWorks?

Robin S.
---------------------------------

 

[Henry Jones]

Hi Robin,

Here is the query that I am using, and it is for my databases here at work.

Select I.InstID, M.InstName, Case when R.RegionDescription Is Null then ' '
else R.RegionDescription end as Region,

Case when S.SubRegionName Is Null then ' ' else S.SubRegionName end as
SubRegion from InstXRegionXSubRegion I

JOIN Regions R ON I.regionID = R.regionID left JOIN SubRegions S ON
I.SubRegionID = S.SubRegionID

JOIN InstMaster M ON I.InstID = M.InstID Where I.SubRegionID = 219



This gives you an example of how I'm using the Case statement.



Thanks,



Henry

 

[RobinS]

What if you use IIF statements instead of Case statements?

Select i.instid, m.instname, IIF(r.regiondescription is null,'',
r.regiondescription) as region, iif(s.subregionname is null, '',
s.subregionname) as subregion
...

I wasn't sure this was supported by SQLServer (I used to
use it in Access), but according to SQLServer Books, it is.

Robin S.
----------------

 

[Henry Jones]

I will try it and see if it works, but I already have a workaround and I'm
not using the FillSchema.

Thanks,

H