Webpage needs to detect that a SQL View is bad before getting an error.

[denny1824]

On the webpage I use a view to fill a dataset.

strCommand = "Select * from view"
objConnection = New SqlClient.SqlConnection(strconnect)
objCommand = New SqlClient.SqlDataAdapter(strCommand, objConnection)
objCommand.Fill(dsDataSet, "view")

This worked fine until the DBA messed up the view. Then my page got an error when it tried to do the fill. I need a way for my webpage to detect if the view is good or bad before getting an error so that if the DBA messes up a view again that the webpage doesn't get an error.

Error handling is setup in a specific way on the page already, so that I am not allowed to handle this specific error in a different way than the rest of the possible errors. So, I need to detect that it is a bad view before I get an error.

The webpage is in VB.Net.

Thank You

[Frinavale]

What is the error?

-Frinny

[denny1824]

The error is "The IListSource does not contain any data sources."

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: The IListSource does not contain any data sources.

[Frinavale]

What line does this error occur on?
Could you please post the code where this error occurs?

[denny1824]

I already posted the code. The error happens on the last line (line 4). The .Fill causes an error when the view is bad. A bad view is one that tries to access a field that no longer exists in the table.

I need a way to know that the view is bad before I get to line 4 and it gets the error.

Thank You

[Frinavale]

I don't know how you are getting that error.
According to the details provided by MSDN on the SqlDataAdapter.Fill method:

If a command does not return any rows, no tables are added to the DataSet, and no exception is raised

The only thing I can think of is that an error/exception is encountered while populating the data tables during the Fill process.

If this is expected, then you should make use of a Try Catch block:

Try
    strCommand = "Select * from view"
    objConnection = New SqlClient.SqlConnection(strconnect)
    objCommand = New SqlClient.SqlDataAdapter(strCommand, objConnection)
    objCommand.Fill(dsDataSet, "view")
Catch ex As Exception
   'This is the case when "view" was invalid
End Try
 

Because you said that you cannot implement your own error handling, I would recommend trying to add the "view" table to the SqlAdapter before filling it:

    Dim dsDataSet As New DataSet
    dsDataSet.Tables.Add("view")
 
    strCommand = "Select * from view"
    objConnection = New SqlClient.SqlConnection(strconnect)
    objCommand = New SqlClient.SqlDataAdapter(strCommand, objConnection)
    objCommand.Fill(dsDataSet, "view") 'Fills "view", which exists in the DataSet.
 
 

If this doesn't work, use the Try Catch block.

[denny1824]

Adding the view as a table before doing the fill fixed the error on the page.

dsDataSet.Tables.Add("view")

Thank You