Question Can not call a View from SQL in .Net

[jamie_pattison]

I am trying to call a View on my database from my .Net code. It works if i type in a Select command instead of the View name but using the View name produces the error "The request for procedure 'SomeView' failed because 'SomeView' is a view object."

My code is

        Dim sqlcon As SqlConnection = New SqlConnection("connection string here")
        Dim dv As New DataView
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter("SomeView", sqlcon)

        sqlcon.Open()
        da.Fill(ds)
        dv = ds.Tables(0).DefaultView

        DataGridView1.DataSource = dv
        sqlcon.Close()

Could anyone help and advise why this is occurring and how to overcome this?

Thanks in advance

 

[LuisCalado]

The SqlDataAdapter accepts as first argument an SqlCommand wich can be a Select statement or stored procedure.
In this case you can replace "SomeView" with "Select * from SomeView"

 

[jamie_pattison]

Thanks for your reply. I know that, but i would like to call the view by name and not have it coded into my project. Again i could use a Store Procedure but since the Data would be read only i think a View woudl be the way to go.

 

[JohnH]

A Sql database View is a virtual table, and not a procedure, though similar since it's a stored SELECT query.

SELECT * FROM DbViewName

 

[cjard]

Thanks for your reply. I know that, but i would like to call the view by name and not have it coded into my project. Again i could use a Store Procedure but since the Data would be read only i think a View woudl be the way to go.

You can consider that a view and a table are the same thing, except views dont store data, they generate it on the fly from the query that defines them


You cannot parameterise a table/view name like you can a column value. You have to use string concatenation to achieve your goal:


Dim da As New SqlDataAdapter("SELECT * FROM " & viewName, sqlcon)


MAKE SURE that the text in viewName can NEVER be typed in freehand by the user, unless you want to be hacked.. It MUST come from a pre-approved list of values stored in your program and not a config file etc!

At the very least, refuse to run the query if it contains any character other than a-z, 0-9 and underscores