|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Why not a view...?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 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. Show quote "Zamdrist" <zamdr***@gmail.com> wrote in message news:1137531860.016097.294580@f14g2000cwb.googlegroups.com... > 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 > 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? Show quote "Zamdrist" <zamdr***@gmail.com> wrote in message news:1137531860.016097.294580@f14g2000cwb.googlegroups.com... > 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, 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 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 Show quote "Zamdrist" <zamdr***@gmail.com> wrote in message news:1137533507.412806.47290@g44g2000cwa.googlegroups.com... > 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 wrote:
> Zam: Yes, thank you. I just don't see the point in using a Text type Command> > You can use it as a view but with the syntax similar to a real table... > SELECT Col1, Col2 FROM MyView 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. 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 Show quote "Zamdrist" wrote: > W.G. Ryan - MVP wrote: > > 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. > > 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. 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 Show quote "Zamdrist" wrote: > 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. > >  |
|||||||||||||||||||||||
Other interesting topics