Why not a view...?

[Zamdrist]

Could someone educate me why this code doesn't work if the text
argument of the SqlCommand method is a view, as opposed to a stored
procedure when the view returns the same field(s) of information?
Unfortunately debugging in ASP.Net is beyond me at the moment and all I
get is a generic error back, otherwise I would provide that info.
Thanks...

If Not Page.IsPostBack Then
adoConn.Open()
Dim adoComm_UserList As New SqlCommand("sp_SelectUserList",
adoConn)

With ddlUserList
.DataSource =
adoComm_UserList.ExecuteReader(CommandBehavior.CloseConnection)
.DataTextField = "fldUser"
.DataValueField = "fldUser"
.DataBind()
End With

End If

 

[Marina]

That looks like a stored procedure. If it was a view, it would have a SELECT
statement selecting from the view. Since it's a stored procedure, you need
to set the CommandType of the SqlCommand appropriately.

 

[W.G. Ryan - MVP]

Are you telling it SELECT * FROM ViewName in the other example? Or are you
just substituting the viewname? This could be one problem.. Also, if
you're not using a proc, you may want to specify the CommandType to be Text
but I don't think that's the problem. What does the code look like that
doesn't work?

 

[Zamdrist]

Marina, W.G.

I was just replacing the text command argument of SqlCommand with the
view name as opposed to the stored procedure.

I notice now that CommandType.Text is the default, so that's why just
the view name wouldn't work, when the procedure name would.

I wouldn't see the point of using a view now, if you have to say:
"Select blah From..." anyways. So I'll just use a stored procedure,
which incidently, also seems silly because all I'm doing is a "Select
Distinct field From table..."

Anyhow, that'll work, and thanks for the replies!

Steve

 

[W.G. Ryan - MVP]

Zam:

You can use it as a view but with the syntax similar to a real table...
SELECT Col1, Col2 FROM MyView

You an also wrap the view in a stored proc if need be. Don't avoid using
views if you need them, but for returning rows, just remember to use them
like a table or use a Stored proc.

HTH,

Bill

 

[Zamdrist]

Zam:

You can use it as a view but with the syntax similar to a real table...
SELECT Col1, Col2 FROM MyView

Yes, thank you. I just don't see the point in using a Text type Command
to Select from a view, a view which already does a Select.

I like the stored procedure route, better contained, even if its just a
simple query.

Thank you.

 

[Guest]

Hi Zam,

A view would mainly be used for creating a cut down 'table' of data that
excludes some columns that you don't want a certain group of users to see.
That way, the user can still create a SELECT statement (through your code)
and return columns that are useful to him/her without allowing the user to
see the columns (Salary, for example) that should be hidden from the users
view.

A Stored procedure on the other hand allows criteria in the WHERE portion of
the SELECT statement to be manipulated by the user through the use of
parameters passed to the SP, however, a stored procedure cannot be used to
SELECT specific coloumns. You cannot SELECT @col1, @col2. You must use a view
to do this. They both have their purposes.

Hope this helps
Ant

 

[Zamdrist]

Thank you Ant.

While I appreciate the clarification, my question was regarding why the
particular ADO.Net code I supplied didn't work with a view, when it did
with a stored procedure.

That led to the question (sort of) that I had, why one would use a view
as opposed to a stored procedure, in my case.

All I'm doing really is a Select Distinct to get a lookup list that
will be part of a submitted query, after the user has chosen their
criteria.

So, if all you had was: Select Distinct fldUser From tblLoginLogout

Would you use a view or a stored procedure to return results and
populate an ASP.Net drop-down list? I'm inclined to use a stored
procedure now, because in order to use a view, I would still have to
say Select fldUser from viewname...which, well, seems redundant.

 

[Guest]

Hi Zam,

If you don't need to pull different cols each time then go the SP as thenyou
don't need to select part of the stmt in your front end code.
Regards
Ant