|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DataTable FillSchema error - Pulling my hair out!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 What page is it in the David Sceppa book?
Robin S. ------------------ Show quoteHide quote "Henry Jones" <he***@yada.com> wrote in message news:OwZm7CjGHHA.1816@TK2MSFTNGP06.phx.gbl... > 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 > > > > > > I'll check on Monday as my book is at work.
Henry Show quoteHide quote "RobinS" <RobinS@NoSpam.yah.none> wrote in message news:C82dnQU0RudStufYnZ2dnUVZ_uG3nZ2d@comcast.com... > What page is it in the David Sceppa book? > > Robin S. > ------------------ > "Henry Jones" <he***@yada.com> wrote in message > news:OwZm7CjGHHA.1816@TK2MSFTNGP06.phx.gbl... >> 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 >> >> >> >> >> >> > > 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. ---------------------------------------- Show quoteHide quote "Henry Jones" <he***@yada.com> wrote in message news:%23HjIMW$GHHA.5020@TK2MSFTNGP03.phx.gbl... > I'll check on Monday as my book is at work. > > Henry > > "RobinS" <RobinS@NoSpam.yah.none> wrote in message > news:C82dnQU0RudStufYnZ2dnUVZ_uG3nZ2d@comcast.com... >> What page is it in the David Sceppa book? >> >> Robin S. >> ------------------ >> "Henry Jones" <he***@yada.com> wrote in message >> news:OwZm7CjGHHA.1816@TK2MSFTNGP06.phx.gbl... >>> 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 >>> >>> >>> >>> >>> >>> >> >> > 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 Show quoteHide quote "RobinS" <RobinS@NoSpam.yah.none> wrote in message news:HfCdnfVCubknBubYnZ2dnUVZ_rOqnZ2d@comcast.com... > 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" <he***@yada.com> wrote in message > news:%23HjIMW$GHHA.5020@TK2MSFTNGP03.phx.gbl... >> I'll check on Monday as my book is at work. >> >> Henry >> >> "RobinS" <RobinS@NoSpam.yah.none> wrote in message >> news:C82dnQU0RudStufYnZ2dnUVZ_uG3nZ2d@comcast.com... >>> What page is it in the David Sceppa book? >>> >>> Robin S. >>> ------------------ >>> "Henry Jones" <he***@yada.com> wrote in message >>> news:OwZm7CjGHHA.1816@TK2MSFTNGP06.phx.gbl... >>>> 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 >>>> >>>> >>>> >>>> >>>> >>>> >>> >>> >> > > 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. --------------------------------- Show quoteHide quote "Henry Jones" <he***@yada.com> wrote in message news:uObHxdTHHHA.4712@TK2MSFTNGP04.phx.gbl... >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" <RobinS@NoSpam.yah.none> wrote in message > news:HfCdnfVCubknBubYnZ2dnUVZ_rOqnZ2d@comcast.com... >> 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" <he***@yada.com> wrote in message >> news:%23HjIMW$GHHA.5020@TK2MSFTNGP03.phx.gbl... >>> I'll check on Monday as my book is at work. >>> >>> Henry >>> >>> "RobinS" <RobinS@NoSpam.yah.none> wrote in message >>> news:C82dnQU0RudStufYnZ2dnUVZ_uG3nZ2d@comcast.com... >>>> What page is it in the David Sceppa book? >>>> >>>> Robin S. >>>> ------------------ >>>> "Henry Jones" <he***@yada.com> wrote in message >>>> news:OwZm7CjGHHA.1816@TK2MSFTNGP06.phx.gbl... >>>>> 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 >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >> >> > > 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 Show quoteHide quote "RobinS" <RobinS@NoSpam.yah.none> wrote in message news:LpCdnbswAqhF3OPYnZ2dnUVZ_h63nZ2d@comcast.com... > 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" <he***@yada.com> wrote in message > news:uObHxdTHHHA.4712@TK2MSFTNGP04.phx.gbl... >>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" <RobinS@NoSpam.yah.none> wrote in message >> news:HfCdnfVCubknBubYnZ2dnUVZ_rOqnZ2d@comcast.com... >>> 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" <he***@yada.com> wrote in message >>> news:%23HjIMW$GHHA.5020@TK2MSFTNGP03.phx.gbl... >>>> I'll check on Monday as my book is at work. >>>> >>>> Henry >>>> >>>> "RobinS" <RobinS@NoSpam.yah.none> wrote in message >>>> news:C82dnQU0RudStufYnZ2dnUVZ_uG3nZ2d@comcast.com... >>>>> What page is it in the David Sceppa book? >>>>> >>>>> Robin S. >>>>> ------------------ >>>>> "Henry Jones" <he***@yada.com> wrote in message >>>>> news:OwZm7CjGHHA.1816@TK2MSFTNGP06.phx.gbl... >>>>>> 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 >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>> >>> >> >> > > 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. ---------------- Show quoteHide quote "Henry Jones" <he***@TheCheckIsInTheMail.com> wrote in message news:OHqVqTjHHHA.4688@TK2MSFTNGP04.phx.gbl... > 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" <RobinS@NoSpam.yah.none> wrote in message > news:LpCdnbswAqhF3OPYnZ2dnUVZ_h63nZ2d@comcast.com... >> 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" <he***@yada.com> wrote in message >> news:uObHxdTHHHA.4712@TK2MSFTNGP04.phx.gbl... >>>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" <RobinS@NoSpam.yah.none> wrote in message >>> news:HfCdnfVCubknBubYnZ2dnUVZ_rOqnZ2d@comcast.com... >>>> 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" <he***@yada.com> wrote in message >>>> news:%23HjIMW$GHHA.5020@TK2MSFTNGP03.phx.gbl... >>>>> I'll check on Monday as my book is at work. >>>>> >>>>> Henry >>>>> >>>>> "RobinS" <RobinS@NoSpam.yah.none> wrote in message >>>>> news:C82dnQU0RudStufYnZ2dnUVZ_uG3nZ2d@comcast.com... >>>>>> What page is it in the David Sceppa book? >>>>>> >>>>>> Robin S. >>>>>> ------------------ >>>>>> "Henry Jones" <he***@yada.com> wrote in message >>>>>> news:OwZm7CjGHHA.1816@TK2MSFTNGP06.phx.gbl... >>>>>>> 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 >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>>> >>> >>> >> >> > > I will try it and see if it works, but I already have a workaround and I'm
not using the FillSchema. Thanks, H Show quoteHide quote "RobinS" <RobinS@NoSpam.yah.none> wrote in message news:JtednbeD0ekUDx3YnZ2dnUVZ_qSrnZ2d@comcast.com... > 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" <he***@TheCheckIsInTheMail.com> wrote in message > news:OHqVqTjHHHA.4688@TK2MSFTNGP04.phx.gbl... >> 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" <RobinS@NoSpam.yah.none> wrote in message >> news:LpCdnbswAqhF3OPYnZ2dnUVZ_h63nZ2d@comcast.com... >>> 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" <he***@yada.com> wrote in message >>> news:uObHxdTHHHA.4712@TK2MSFTNGP04.phx.gbl... >>>>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" <RobinS@NoSpam.yah.none> wrote in message >>>> news:HfCdnfVCubknBubYnZ2dnUVZ_rOqnZ2d@comcast.com... >>>>> 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" <he***@yada.com> wrote in message >>>>> news:%23HjIMW$GHHA.5020@TK2MSFTNGP03.phx.gbl... >>>>>> I'll check on Monday as my book is at work. >>>>>> >>>>>> Henry >>>>>> >>>>>> "RobinS" <RobinS@NoSpam.yah.none> wrote in message >>>>>> news:C82dnQU0RudStufYnZ2dnUVZ_uG3nZ2d@comcast.com... >>>>>>> What page is it in the David Sceppa book? >>>>>>> >>>>>>> Robin S. >>>>>>> ------------------ >>>>>>> "Henry Jones" <he***@yada.com> wrote in message >>>>>>> news:OwZm7CjGHHA.1816@TK2MSFTNGP06.phx.gbl... >>>>>>>> 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 >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > 
Other interesting topics
Populating combox from dataset
Is this a bug I see before me, or an incomplete understanding of scope? Shared textfile...threading Namespace for stdole? Look at this debugging output about TreeViews How to prevent keydown events on toolbar Apostrophe Problem - HELP Drop shadow under image Re: Turn off deprecated warnings? How to use log4net for a windows forms application using COM classes |
|||||||||||||||||||||||
