you can't do this? @SortBy

You can only pass literal values in - not column names. If you wanted to do
something like that, you would have to create a string with the query and
execute it.
Also, you can try passing in @.SortBy as an integer, and sorting on the
column order, instead of using the column name to perform the sort. I
haven't tried it, but it might work - not sure if the order by can use any
parameters.
"djc" <noone@.nowhere.com> wrote in message
news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> I'm very new to stored procedures! I am using MSDE2000 and asp.net
WebMatrix
> to work on this page. Currently I
> have this in the stored procedure:
> CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> AS
> SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> Status
> FROM tblMain
> ORDER BY @.SortBy
> GO
> I get this error when trying to save it:
> "The select item identified by the ORDER BY number 1 contains a variable
as
> part of the expression identifying a column position. Variables are only
> allowed when ordering by an expression referencing a column name."
> any help would be very much appreciated. Thanks!
>
>
> CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> AS
> SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> Status
> FROM tblMain
> ORDER BY @.SortBy
No, but you can do this:
-- Note changes:
-- dbo should own everything
-- specify the size of your varchar in characters
-- this appends the content of @.sortBy onto the end of the sql string.
-- the sp_ExecuteSQL system stored procedure compiles it and runs it.
CREATE PROCEDURE dbo.GetAllIssues
(
@.sortBy VARCHAR(20)
)
AS
DECLARE @.sql VARCHAR(1000)
SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy
EXECUTE sp_ExecuteSql @.sql
GO
--
Peace & happy computing,
Mike Labosh, MCSD
"I have no choice but to believe in free will."
I guess that means the ORDER BY can't use a parameter for the ASC or DESC
options either then huh? So much for benefiting from stored procedures, in
my case at least. I'll go back to just putting the SQL directly in my code.
thanks for the reply.
"Marina" <someone@.nospam.com> wrote in message
news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> You can only pass literal values in - not column names. If you wanted to
do
> something like that, you would have to create a string with the query and
> execute it.
> Also, you can try passing in @.SortBy as an integer, and sorting on the
> column order, instead of using the column name to perform the sort. I
> haven't tried it, but it might work - not sure if the order by can use any
> parameters.
> "djc" <noone@.nowhere.com> wrote in message
> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
> WebMatrix
> > to work on this page. Currently I
> > have this in the stored procedure:
> >
> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> > AS
> >
> > SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> > Status
> > FROM tblMain
> > ORDER BY @.SortBy
> >
> > GO
> >
> > I get this error when trying to save it:
> > "The select item identified by the ORDER BY number 1 contains a variable
> as
> > part of the expression identifying a column position. Variables are only
> > allowed when ordering by an expression referencing a column name."
> >
> > any help would be very much appreciated. Thanks!
> >
> >
> >
>
ok. cool. I will do that. Currently I'm using something like this:
<snip>
IF (@.SortBy = 'MainID')
BEGIN
SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
Status
FROM tblMain
ORDER BY MainID
END
ELSE IF (@.SortBy = 'Title')
BEGIN
SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
Status
FROM tblMain
ORDER BY Title
END
</snip>
BUT then I realized I also need to dynamically assign the ASC or DESC
options. This is for an asp.net datagrid and I need the first click to sort
ASC and the second click on the same column to sort DESC etc... Using the
same method I have now would require a very long and likely unefficient
stored procedure. Any ideas for incorporating that?
thanks again!
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eERQItFwEHA.1988@.TK2MSFTNGP12.phx.gbl...
> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> > AS
> >
> > SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> > Status
> > FROM tblMain
> > ORDER BY @.SortBy
> No, but you can do this:
> -- Note changes:
> -- dbo should own everything
> -- specify the size of your varchar in characters
> -- this appends the content of @.sortBy onto the end of the sql string.
> -- the sp_ExecuteSQL system stored procedure compiles it and runs it.
> CREATE PROCEDURE dbo.GetAllIssues
> (
> @.sortBy VARCHAR(20)
> )
> AS
> DECLARE @.sql VARCHAR(1000)
> SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
> EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy
> EXECUTE sp_ExecuteSql @.sql
> GO
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "I have no choice but to believe in free will."
>
> BUT then I realized I also need to dynamically assign the ASC or DESC
> options. This is for an asp.net datagrid and I need the first click to
> sort
> ASC and the second click on the same column to sort DESC etc... Using the
> same method I have now would require a very long and likely unefficient
> stored procedure. Any ideas for incorporating that?
Oh, c'mon, THINK! :)
CREATE PROCEDURE dbo.GetAllIssues
(
@.sortBy VARCHAR(20),
@.sortOrder VARCHAR(4) -- contains ' DESC' (space+DESC) or ''
)
AS
DECLARE @.sql VARCHAR(1000)
SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy + @.sortOrder
EXECUTE sp_ExecuteSql @.sql
GO
--
Peace & happy computing,
Mike Labosh, MCSD
"I have no choice but to believe in free will."
"djc" <noone@.nowhere.com> wrote in message
news:uxlPo2FwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> ok. cool. I will do that. Currently I'm using something like this:
> <snip>
> IF (@.SortBy = 'MainID')
> BEGIN
> SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> Status
> FROM tblMain
> ORDER BY MainID
> END
> ELSE IF (@.SortBy = 'Title')
> BEGIN
> SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> Status
> FROM tblMain
> ORDER BY Title
> END
> </snip>
>
> thanks again!
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:eERQItFwEHA.1988@.TK2MSFTNGP12.phx.gbl...
>> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
>> > AS
>> >
>> > SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
>> > Status
>> > FROM tblMain
>> > ORDER BY @.SortBy
>> No, but you can do this:
>> -- Note changes:
>> -- dbo should own everything
>> -- specify the size of your varchar in characters
>> -- this appends the content of @.sortBy onto the end of the sql string.
>> -- the sp_ExecuteSQL system stored procedure compiles it and runs it.
>> CREATE PROCEDURE dbo.GetAllIssues
>> (
>> @.sortBy VARCHAR(20)
>> )
>> AS
>> DECLARE @.sql VARCHAR(1000)
>> SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
>> EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy
>> EXECUTE sp_ExecuteSql @.sql
>> GO
>> --
>> Peace & happy computing,
>> Mike Labosh, MCSD
>> "I have no choice but to believe in free will."
>>
>
I am kind of posting frantically here and moving on before getting
replies... hehe.. I'm on a roll! I do see the simplicity of adding the
ASC/DESC into the EXECUTE sp_ExecuteSql method. I did not ask my question
clearly... I was looking for a way other than the sp_ExecuteSql method. I
had found further information in the meantime that suggested using EXEC or
EXECUTE sp_ExecuteSql has many disadvantages. I am now playing with this
setup:
CREATE PROCEDURE dbo.GetAllIssues
(
@.SortBy VarChar(50), @.Asc_Desc VarChar(8)
)
AS
SELECT MainID, Title, ShortDesc, EstimatedDeliveryDate, Status
FROM tblMain
ORDER BY
CASE @.Asc_Desc
WHEN 'desc' THEN
CASE @.SortBy
WHEN 'MainID' THEN MainID
END
END
Desc,
CASE @.Asc_Desc
WHEN 'desc' THEN
CASE @.SortBy
WHEN 'Title' THEN Title
END
END
DESC,
CASE @.Asc_Desc
WHEN 'desc' THEN
CASE @.SortBy
WHEN 'EstimatedDeliveryDate' THEN EstimatedDeliveryDate
END
END
DESC,
CASE @.Asc_Desc
WHEN 'desc' THEN
CASE @.SortBy
WHEN 'Status' THEN Status
END
END
DESC,
CASE @.Asc_Desc
WHEN 'asc' THEN
CASE @.SortBy
WHEN 'MainID' THEN MainID
END
END,
CASE @.Asc_Desc
WHEN 'asc' THEN
CASE @.SortBy
WHEN 'Title' THEN Title
END
END,
CASE @.Asc_Desc
WHEN 'asc' THEN
CASE @.SortBy
WHEN 'EstimatedDeliveryDate' THEN EstimatedDeliveryDate
END
END,
CASE @.Asc_Desc
WHEN 'asc' THEN
CASE @.SortBy
WHEN 'Status' THEN Status
END
END
GO
It works but again I AM new to TSQL aside from the basic SELECT, INSERT,
UPDATE commands. I'm trying to transition from putting my sql directly in my
code to using stored procedures as I see is recommended. The last part of my
task is how/where in my asp.net code do I communicate the asc or desc
parameter? I need to put something in place so that the page knows whether
its the first or second click etc... so it can pass the correct asc/desc
value. I'm not sure where/how that code will go. I'm new to asp.net as
well..hehe.
I appreciate your input. Any other comments are welcome... any ideas on the
last part of my task are certianly welcome as well.
-djc
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23l9hnSGwEHA.1452@.TK2MSFTNGP11.phx.gbl...
> > BUT then I realized I also need to dynamically assign the ASC or DESC
> > options. This is for an asp.net datagrid and I need the first click to
> > sort
> > ASC and the second click on the same column to sort DESC etc... Using
the
> > same method I have now would require a very long and likely unefficient
> > stored procedure. Any ideas for incorporating that?
> Oh, c'mon, THINK! :)
> CREATE PROCEDURE dbo.GetAllIssues
> (
> @.sortBy VARCHAR(20),
> @.sortOrder VARCHAR(4) -- contains ' DESC' (space+DESC) or ''
> )
> AS
> DECLARE @.sql VARCHAR(1000)
> SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
> EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy +
@.sortOrder
> EXECUTE sp_ExecuteSql @.sql
> GO
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "I have no choice but to believe in free will."
> "djc" <noone@.nowhere.com> wrote in message
> news:uxlPo2FwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> > ok. cool. I will do that. Currently I'm using something like this:
> >
> > <snip>
> > IF (@.SortBy = 'MainID')
> >
> > BEGIN
> > SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate,
> > Status
> > FROM tblMain
> > ORDER BY MainID
> > END
> >
> > ELSE IF (@.SortBy = 'Title')
> >
> > BEGIN
> > SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate,
> > Status
> > FROM tblMain
> > ORDER BY Title
> > END
> > </snip>
> >
> >
> > thanks again!
> >
> > "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> > news:eERQItFwEHA.1988@.TK2MSFTNGP12.phx.gbl...
> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> >> > AS
> >> >
> >> > SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate,
> >> > Status
> >> > FROM tblMain
> >> > ORDER BY @.SortBy
> >>
> >> No, but you can do this:
> >>
> >> -- Note changes:
> >> -- dbo should own everything
> >> -- specify the size of your varchar in characters
> >> -- this appends the content of @.sortBy onto the end of the sql string.
> >> -- the sp_ExecuteSQL system stored procedure compiles it and runs it.
> >>
> >> CREATE PROCEDURE dbo.GetAllIssues
> >> (
> >> @.sortBy VARCHAR(20)
> >> )
> >> AS
> >> DECLARE @.sql VARCHAR(1000)
> >>
> >> SET @.sql = 'SELECT MainID, Title, ShortDesc, DateCreated,
> >> EstimatedDeliveryDate, Status FROM tblMain ORDER BY ' + @.sortBy
> >>
> >> EXECUTE sp_ExecuteSql @.sql
> >> GO
> >>
> >> --
> >> Peace & happy computing,
> >>
> >> Mike Labosh, MCSD
> >> "I have no choice but to believe in free will."
> >>
> >>
> >
> >
>
>I am kind of posting frantically here and moving on before getting
> replies... hehe.. I'm on a roll! I do see the simplicity of adding the
> ASC/DESC into the EXECUTE sp_ExecuteSql method. I did not ask my question
> clearly... I was looking for a way other than the sp_ExecuteSql method. I
> had found further information in the meantime that suggested using EXEC or
> EXECUTE sp_ExecuteSql has many disadvantages. I am now playing with this
> setup:
The disadvantages that you refer to are that anytime you EXEC @.sql or EXEC
ExecuteSql @.sql, you are forcing SQL Server to parse, optimize and compile
your code, whereas a plain vanilla stored procedure that's not dynamic only
gets parsed optimized and compiled when it's created; not every time it's
run.
If you do a lot of dynamic stuff like this, then here are your options:
1. Build your SQL like this:
"EXECUTE sp_DexecuteSQL " & sql
and assign it to a SqlCommand.CommandText
2. Build your SQL in a string and pass it to a SqlParameter.Value wich you
Add() to a SqlCommand.Parameters collection where the SqlCommand.CommandText
is "sp_ExecuteSQL"
One thing you will want to consider is the subtle differences between these:
"EXECUTE @.sql" and "EXECUTE sp_ExecuteSql @.sql"
I personally am a bit weak on the details, but if you bump over to the
microsoft.public.sqlserver.programming group, you can ask them the
differences, and they are THE final SQL gods.
> SELECT MainID, Title, ShortDesc, EstimatedDeliveryDate, Status
> FROM tblMain
> ORDER BY
> CASE @.Asc_Desc
> WHEN 'desc' THEN
> CASE @.SortBy
> WHEN 'MainID' THEN MainID
> END
> END
> Desc,
You *definately* want to avoid this foolishness. Any DBA would slap you
with your own mouse cable :)
> The last part of my
> task is how/where in my asp.net code do I communicate the asc or desc
> parameter? I need to put something in place so that the page knows whether
> its the first or second click etc... so it can pass the correct asc/desc
> value. I'm not sure where/how that code will go.
First, assume ASC by default. It's easyier that way. Then you put a
checkbox on your WebForm:
[x] Descending
You set the checkbox to NOT do a PostBack.
Then you make your DataGrid or whatever you're using that the user clicks to
actually perform the sort do the PostBack. Then (I live in COM+ Enterprise
Services and I'm weak on asp) you do something like this in your CodeBehind
Class (aircode):
[VB]
Private Sub PageLoad(stuff)
Dim sql As String = "your stuff"
If Page.IsPostBack() Then
If chkDescending.Checked Then sql &= " DESC"
End If
Dim sortBy = sender.Name '?
Dim cm As New SqlCommand(sql, conn)
With cm ' WAY faster
.CommandText = sql
With .Parameters 'WAY faster
.Add("@.sortBy", SqlDbType.VarChar)
.Item(0).Value = sortBy
etc.
[CS]
private void pageLoad(stuff) {
string sql = "your stuff";
if (Page.IsPostBack)
if (chkDescending.Checked) sql += " DESC";
string sortBy = sender.Name;
SqlCommand cm = new SqlCommand(sql, conn);
cm.CommandText = sql;
cm.Parameters.Add("@.sortBy", SqlDbType.VarChar);
cm.Parameters(0).Value = sortBy
etc.
--
Peace & happy computing,
Mike Labosh, MCSD
"I have no choice but to believe in free will."
All very cool... except for one thing. This approach opens your program up
to a SQL injection attack. These are very common and there are lots of
people out there that pound web sites and internal programs looking for this
gateway to your server.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:ebDfBSHwEHA.1260@.TK2MSFTNGP12.phx.gbl...
> >I am kind of posting frantically here and moving on before getting
>> replies... hehe.. I'm on a roll! I do see the simplicity of adding the
>> ASC/DESC into the EXECUTE sp_ExecuteSql method. I did not ask my question
>> clearly... I was looking for a way other than the sp_ExecuteSql method. I
>> had found further information in the meantime that suggested using EXEC
>> or
>> EXECUTE sp_ExecuteSql has many disadvantages. I am now playing with this
>> setup:
> The disadvantages that you refer to are that anytime you EXEC @.sql or EXEC
> ExecuteSql @.sql, you are forcing SQL Server to parse, optimize and compile
> your code, whereas a plain vanilla stored procedure that's not dynamic
> only gets parsed optimized and compiled when it's created; not every time
> it's run.
> If you do a lot of dynamic stuff like this, then here are your options:
> 1. Build your SQL like this:
> "EXECUTE sp_DexecuteSQL " & sql
> and assign it to a SqlCommand.CommandText
> 2. Build your SQL in a string and pass it to a SqlParameter.Value wich you
> Add() to a SqlCommand.Parameters collection where the
> SqlCommand.CommandText is "sp_ExecuteSQL"
> One thing you will want to consider is the subtle differences between
> these:
> "EXECUTE @.sql" and "EXECUTE sp_ExecuteSql @.sql"
> I personally am a bit weak on the details, but if you bump over to the
> microsoft.public.sqlserver.programming group, you can ask them the
> differences, and they are THE final SQL gods.
>> SELECT MainID, Title, ShortDesc, EstimatedDeliveryDate, Status
>> FROM tblMain
>> ORDER BY
>> CASE @.Asc_Desc
>> WHEN 'desc' THEN
>> CASE @.SortBy
>> WHEN 'MainID' THEN MainID
>> END
>> END
>> Desc,
> You *definately* want to avoid this foolishness. Any DBA would slap you
> with your own mouse cable :)
>> The last part of my
>> task is how/where in my asp.net code do I communicate the asc or desc
>> parameter? I need to put something in place so that the page knows
>> whether
>> its the first or second click etc... so it can pass the correct asc/desc
>> value. I'm not sure where/how that code will go.
> First, assume ASC by default. It's easyier that way. Then you put a
> checkbox on your WebForm:
> [x] Descending
> You set the checkbox to NOT do a PostBack.
> Then you make your DataGrid or whatever you're using that the user clicks
> to actually perform the sort do the PostBack. Then (I live in COM+
> Enterprise Services and I'm weak on asp) you do something like this in
> your CodeBehind Class (aircode):
> [VB]
> Private Sub PageLoad(stuff)
> Dim sql As String = "your stuff"
> If Page.IsPostBack() Then
> If chkDescending.Checked Then sql &= " DESC"
> End If
> Dim sortBy = sender.Name '?
> Dim cm As New SqlCommand(sql, conn)
> With cm ' WAY faster
> .CommandText = sql
> With .Parameters 'WAY faster
> .Add("@.sortBy", SqlDbType.VarChar)
> .Item(0).Value = sortBy
> etc.
> [CS]
> private void pageLoad(stuff) {
> string sql = "your stuff";
> if (Page.IsPostBack)
> if (chkDescending.Checked) sql += " DESC";
> string sortBy = sender.Name;
> SqlCommand cm = new SqlCommand(sql, conn);
> cm.CommandText = sql;
> cm.Parameters.Add("@.sortBy", SqlDbType.VarChar);
> cm.Parameters(0).Value = sortBy
> etc.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "I have no choice but to believe in free will."
>
What am I missing?
SQL Server is primarily optimized for storing and retrieving data.
If the datasets are small enough (and they should be!), isn't using a
Dataview then appropriate?
Do your CRUD with stored procs.
Do your display with a DataView.
With the dataview you can then use the RowFilter and Sort properties to
control the display of the data.
You are opening yourself up for SQL injection attacks and missing/extra
quotes when you use string concatentation in code.
"djc" <noone@.nowhere.com> wrote in message
news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
>I guess that means the ORDER BY can't use a parameter for the ASC or DESC
> options either then huh? So much for benefiting from stored procedures, in
> my case at least. I'll go back to just putting the SQL directly in my
> code.
> thanks for the reply.
> "Marina" <someone@.nospam.com> wrote in message
> news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> You can only pass literal values in - not column names. If you wanted to
> do
>> something like that, you would have to create a string with the query and
>> execute it.
>> Also, you can try passing in @.SortBy as an integer, and sorting on the
>> column order, instead of using the column name to perform the sort. I
>> haven't tried it, but it might work - not sure if the order by can use
>> any
>> parameters.
>> "djc" <noone@.nowhere.com> wrote in message
>> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
>> WebMatrix
>> > to work on this page. Currently I
>> > have this in the stored procedure:
>> >
>> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
>> > AS
>> >
>> > SELECT MainID, Title, ShortDesc, DateCreated,
>> > EstimatedDeliveryDate,
>> > Status
>> > FROM tblMain
>> > ORDER BY @.SortBy
>> >
>> > GO
>> >
>> > I get this error when trying to save it:
>> > "The select item identified by the ORDER BY number 1 contains a
>> > variable
>> as
>> > part of the expression identifying a column position. Variables are
>> > only
>> > allowed when ordering by an expression referencing a column name."
>> >
>> > any help would be very much appreciated. Thanks!
>> >
>> >
>> >
>>
>
I thought I would get that response from someone! hehe. No fear... I'm
commited to learning stored procedures... I was just frustrated. Programming
is more hobby than job for me right now. I'm actually a network
administrator. I just find this stuff fun for some wierd reason!
Please correct me if I'm wrong but I don't think I can use a DataView in my
case. The data I'm retrieving is for the datasource of a DataGrid that needs
to be both sortable and pagable. I'm currently using a DataTable.. can I use
a DataView for that?
any input is appreciated. Thanks.
"Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> What am I missing?
> SQL Server is primarily optimized for storing and retrieving data.
> If the datasets are small enough (and they should be!), isn't using a
> Dataview then appropriate?
> Do your CRUD with stored procs.
> Do your display with a DataView.
> With the dataview you can then use the RowFilter and Sort properties to
> control the display of the data.
> You are opening yourself up for SQL injection attacks and missing/extra
> quotes when you use string concatentation in code.
> "djc" <noone@.nowhere.com> wrote in message
> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> >I guess that means the ORDER BY can't use a parameter for the ASC or DESC
> > options either then huh? So much for benefiting from stored procedures,
in
> > my case at least. I'll go back to just putting the SQL directly in my
> > code.
> >
> > thanks for the reply.
> >
> > "Marina" <someone@.nospam.com> wrote in message
> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> >> You can only pass literal values in - not column names. If you wanted
to
> > do
> >> something like that, you would have to create a string with the query
and
> >> execute it.
> >>
> >> Also, you can try passing in @.SortBy as an integer, and sorting on the
> >> column order, instead of using the column name to perform the sort. I
> >> haven't tried it, but it might work - not sure if the order by can use
> >> any
> >> parameters.
> >>
> >> "djc" <noone@.nowhere.com> wrote in message
> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> >> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
> >> WebMatrix
> >> > to work on this page. Currently I
> >> > have this in the stored procedure:
> >> >
> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> >> > AS
> >> >
> >> > SELECT MainID, Title, ShortDesc, DateCreated,
> >> > EstimatedDeliveryDate,
> >> > Status
> >> > FROM tblMain
> >> > ORDER BY @.SortBy
> >> >
> >> > GO
> >> >
> >> > I get this error when trying to save it:
> >> > "The select item identified by the ORDER BY number 1 contains a
> >> > variable
> >> as
> >> > part of the expression identifying a column position. Variables are
> >> > only
> >> > allowed when ordering by an expression referencing a column name."
> >> >
> >> > any help would be very much appreciated. Thanks!
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
A DataView is generally associated with a DataTable. You fill the DataTable
and use the DefaultView to get the DataView.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"djc" <noone@.nowhere.com> wrote in message
news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
>I thought I would get that response from someone! hehe. No fear... I'm
> commited to learning stored procedures... I was just frustrated.
> Programming
> is more hobby than job for me right now. I'm actually a network
> administrator. I just find this stuff fun for some wierd reason!
> Please correct me if I'm wrong but I don't think I can use a DataView in
> my
> case. The data I'm retrieving is for the datasource of a DataGrid that
> needs
> to be both sortable and pagable. I'm currently using a DataTable.. can I
> use
> a DataView for that?
> any input is appreciated. Thanks.
>
> "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
> news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
>> What am I missing?
>> SQL Server is primarily optimized for storing and retrieving data.
>> If the datasets are small enough (and they should be!), isn't using a
>> Dataview then appropriate?
>> Do your CRUD with stored procs.
>> Do your display with a DataView.
>> With the dataview you can then use the RowFilter and Sort properties to
>> control the display of the data.
>> You are opening yourself up for SQL injection attacks and missing/extra
>> quotes when you use string concatentation in code.
>> "djc" <noone@.nowhere.com> wrote in message
>> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
>> >I guess that means the ORDER BY can't use a parameter for the ASC or
>> >DESC
>> > options either then huh? So much for benefiting from stored procedures,
> in
>> > my case at least. I'll go back to just putting the SQL directly in my
>> > code.
>> >
>> > thanks for the reply.
>> >
>> > "Marina" <someone@.nospam.com> wrote in message
>> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> >> You can only pass literal values in - not column names. If you wanted
> to
>> > do
>> >> something like that, you would have to create a string with the query
> and
>> >> execute it.
>> >>
>> >> Also, you can try passing in @.SortBy as an integer, and sorting on the
>> >> column order, instead of using the column name to perform the sort. I
>> >> haven't tried it, but it might work - not sure if the order by can use
>> >> any
>> >> parameters.
>> >>
>> >> "djc" <noone@.nowhere.com> wrote in message
>> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> >> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
>> >> WebMatrix
>> >> > to work on this page. Currently I
>> >> > have this in the stored procedure:
>> >> >
>> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
>> >> > AS
>> >> >
>> >> > SELECT MainID, Title, ShortDesc, DateCreated,
>> >> > EstimatedDeliveryDate,
>> >> > Status
>> >> > FROM tblMain
>> >> > ORDER BY @.SortBy
>> >> >
>> >> > GO
>> >> >
>> >> > I get this error when trying to save it:
>> >> > "The select item identified by the ORDER BY number 1 contains a
>> >> > variable
>> >> as
>> >> > part of the expression identifying a column position. Variables are
>> >> > only
>> >> > allowed when ordering by an expression referencing a column name."
>> >> >
>> >> > any help would be very much appreciated. Thanks!
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>
ok. I'm still unclear as to whether I could assign a dataView to the
dataSource property of a dataGrid though? (a sortable, pagable, datagrid)
I'll look more into it.
thanks for the reply.
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:OKrTGFQwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> A DataView is generally associated with a DataTable. You fill the
DataTable
> and use the DefaultView to get the DataView.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "djc" <noone@.nowhere.com> wrote in message
> news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> >I thought I would get that response from someone! hehe. No fear... I'm
> > commited to learning stored procedures... I was just frustrated.
> > Programming
> > is more hobby than job for me right now. I'm actually a network
> > administrator. I just find this stuff fun for some wierd reason!
> >
> > Please correct me if I'm wrong but I don't think I can use a DataView in
> > my
> > case. The data I'm retrieving is for the datasource of a DataGrid that
> > needs
> > to be both sortable and pagable. I'm currently using a DataTable.. can I
> > use
> > a DataView for that?
> >
> > any input is appreciated. Thanks.
> >
> >
> >
> > "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
> > news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> >> What am I missing?
> >>
> >> SQL Server is primarily optimized for storing and retrieving data.
> >>
> >> If the datasets are small enough (and they should be!), isn't using a
> >> Dataview then appropriate?
> >>
> >> Do your CRUD with stored procs.
> >> Do your display with a DataView.
> >>
> >> With the dataview you can then use the RowFilter and Sort properties to
> >> control the display of the data.
> >>
> >> You are opening yourself up for SQL injection attacks and missing/extra
> >> quotes when you use string concatentation in code.
> >>
> >> "djc" <noone@.nowhere.com> wrote in message
> >> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> >> >I guess that means the ORDER BY can't use a parameter for the ASC or
> >> >DESC
> >> > options either then huh? So much for benefiting from stored
procedures,
> > in
> >> > my case at least. I'll go back to just putting the SQL directly in my
> >> > code.
> >> >
> >> > thanks for the reply.
> >> >
> >> > "Marina" <someone@.nospam.com> wrote in message
> >> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> >> >> You can only pass literal values in - not column names. If you
wanted
> > to
> >> > do
> >> >> something like that, you would have to create a string with the
query
> > and
> >> >> execute it.
> >> >>
> >> >> Also, you can try passing in @.SortBy as an integer, and sorting on
the
> >> >> column order, instead of using the column name to perform the sort.
I
> >> >> haven't tried it, but it might work - not sure if the order by can
use
> >> >> any
> >> >> parameters.
> >> >>
> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> >> >> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
> >> >> WebMatrix
> >> >> > to work on this page. Currently I
> >> >> > have this in the stored procedure:
> >> >> >
> >> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> >> >> > AS
> >> >> >
> >> >> > SELECT MainID, Title, ShortDesc, DateCreated,
> >> >> > EstimatedDeliveryDate,
> >> >> > Status
> >> >> > FROM tblMain
> >> >> > ORDER BY @.SortBy
> >> >> >
> >> >> > GO
> >> >> >
> >> >> > I get this error when trying to save it:
> >> >> > "The select item identified by the ORDER BY number 1 contains a
> >> >> > variable
> >> >> as
> >> >> > part of the expression identifying a column position. Variables
are
> >> >> > only
> >> >> > allowed when ordering by an expression referencing a column name."
> >> >> >
> >> >> > any help would be very much appreciated. Thanks!
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
Sure. That's a great way to add sort, seek, find, filter to what you see in
a DataGrid.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"djc" <noone@.nowhere.com> wrote in message
news:OQikmKQwEHA.1564@.TK2MSFTNGP09.phx.gbl...
> ok. I'm still unclear as to whether I could assign a dataView to the
> dataSource property of a dataGrid though? (a sortable, pagable, datagrid)
> I'll look more into it.
> thanks for the reply.
> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> news:OKrTGFQwEHA.2016@.TK2MSFTNGP15.phx.gbl...
>> A DataView is generally associated with a DataTable. You fill the
> DataTable
>> and use the DefaultView to get the DataView.
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> __________________________________
>> "djc" <noone@.nowhere.com> wrote in message
>> news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
>> >I thought I would get that response from someone! hehe. No fear... I'm
>> > commited to learning stored procedures... I was just frustrated.
>> > Programming
>> > is more hobby than job for me right now. I'm actually a network
>> > administrator. I just find this stuff fun for some wierd reason!
>> >
>> > Please correct me if I'm wrong but I don't think I can use a DataView
>> > in
>> > my
>> > case. The data I'm retrieving is for the datasource of a DataGrid that
>> > needs
>> > to be both sortable and pagable. I'm currently using a DataTable.. can
>> > I
>> > use
>> > a DataView for that?
>> >
>> > any input is appreciated. Thanks.
>> >
>> >
>> >
>> > "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
>> > news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
>> >> What am I missing?
>> >>
>> >> SQL Server is primarily optimized for storing and retrieving data.
>> >>
>> >> If the datasets are small enough (and they should be!), isn't using a
>> >> Dataview then appropriate?
>> >>
>> >> Do your CRUD with stored procs.
>> >> Do your display with a DataView.
>> >>
>> >> With the dataview you can then use the RowFilter and Sort properties
>> >> to
>> >> control the display of the data.
>> >>
>> >> You are opening yourself up for SQL injection attacks and
>> >> missing/extra
>> >> quotes when you use string concatentation in code.
>> >>
>> >> "djc" <noone@.nowhere.com> wrote in message
>> >> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
>> >> >I guess that means the ORDER BY can't use a parameter for the ASC or
>> >> >DESC
>> >> > options either then huh? So much for benefiting from stored
> procedures,
>> > in
>> >> > my case at least. I'll go back to just putting the SQL directly in
>> >> > my
>> >> > code.
>> >> >
>> >> > thanks for the reply.
>> >> >
>> >> > "Marina" <someone@.nospam.com> wrote in message
>> >> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> >> >> You can only pass literal values in - not column names. If you
> wanted
>> > to
>> >> > do
>> >> >> something like that, you would have to create a string with the
> query
>> > and
>> >> >> execute it.
>> >> >>
>> >> >> Also, you can try passing in @.SortBy as an integer, and sorting on
> the
>> >> >> column order, instead of using the column name to perform the sort.
> I
>> >> >> haven't tried it, but it might work - not sure if the order by can
> use
>> >> >> any
>> >> >> parameters.
>> >> >>
>> >> >> "djc" <noone@.nowhere.com> wrote in message
>> >> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> >> >> > I'm very new to stored procedures! I am using MSDE2000 and
>> >> >> > asp.net
>> >> >> WebMatrix
>> >> >> > to work on this page. Currently I
>> >> >> > have this in the stored procedure:
>> >> >> >
>> >> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
>> >> >> > AS
>> >> >> >
>> >> >> > SELECT MainID, Title, ShortDesc, DateCreated,
>> >> >> > EstimatedDeliveryDate,
>> >> >> > Status
>> >> >> > FROM tblMain
>> >> >> > ORDER BY @.SortBy
>> >> >> >
>> >> >> > GO
>> >> >> >
>> >> >> > I get this error when trying to save it:
>> >> >> > "The select item identified by the ORDER BY number 1 contains a
>> >> >> > variable
>> >> >> as
>> >> >> > part of the expression identifying a column position. Variables
> are
>> >> >> > only
>> >> >> > allowed when ordering by an expression referencing a column
>> >> >> > name."
>> >> >> >
>> >> >> > any help would be very much appreciated. Thanks!
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>
ok. cool. So then this suggested approach is to use a dataView to bind to
the datagrid. I assume the dataView is *not* forward only then, since I need
the datagrid to be pageable. One last question with regard to this approach
compared to calling a stored procedure then... and not the stored procedure
you see below but one without any dynamic sql. Will there be significant
performance differences? In other words would it be faster to sort or filter
a dataview than to call another stored procedure to do it?
thanks again!
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:OavWl0RwEHA.1292@.TK2MSFTNGP10.phx.gbl...
> Sure. That's a great way to add sort, seek, find, filter to what you see
in
> a DataGrid.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "djc" <noone@.nowhere.com> wrote in message
> news:OQikmKQwEHA.1564@.TK2MSFTNGP09.phx.gbl...
> > ok. I'm still unclear as to whether I could assign a dataView to the
> > dataSource property of a dataGrid though? (a sortable, pagable,
datagrid)
> > I'll look more into it.
> >
> > thanks for the reply.
> >
> > "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> > news:OKrTGFQwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> >> A DataView is generally associated with a DataTable. You fill the
> > DataTable
> >> and use the DefaultView to get the DataView.
> >>
> >> --
> >> ____________________________________
> >> William (Bill) Vaughn
> >> Author, Mentor, Consultant
> >> Microsoft MVP
> >> www.betav.com
> >> Please reply only to the newsgroup so that others can benefit.
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >> __________________________________
> >>
> >> "djc" <noone@.nowhere.com> wrote in message
> >> news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> >> >I thought I would get that response from someone! hehe. No fear... I'm
> >> > commited to learning stored procedures... I was just frustrated.
> >> > Programming
> >> > is more hobby than job for me right now. I'm actually a network
> >> > administrator. I just find this stuff fun for some wierd reason!
> >> >
> >> > Please correct me if I'm wrong but I don't think I can use a DataView
> >> > in
> >> > my
> >> > case. The data I'm retrieving is for the datasource of a DataGrid
that
> >> > needs
> >> > to be both sortable and pagable. I'm currently using a DataTable..
can
> >> > I
> >> > use
> >> > a DataView for that?
> >> >
> >> > any input is appreciated. Thanks.
> >> >
> >> >
> >> >
> >> > "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
> >> > news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> >> >> What am I missing?
> >> >>
> >> >> SQL Server is primarily optimized for storing and retrieving data.
> >> >>
> >> >> If the datasets are small enough (and they should be!), isn't using
a
> >> >> Dataview then appropriate?
> >> >>
> >> >> Do your CRUD with stored procs.
> >> >> Do your display with a DataView.
> >> >>
> >> >> With the dataview you can then use the RowFilter and Sort properties
> >> >> to
> >> >> control the display of the data.
> >> >>
> >> >> You are opening yourself up for SQL injection attacks and
> >> >> missing/extra
> >> >> quotes when you use string concatentation in code.
> >> >>
> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> >> >> >I guess that means the ORDER BY can't use a parameter for the ASC
or
> >> >> >DESC
> >> >> > options either then huh? So much for benefiting from stored
> > procedures,
> >> > in
> >> >> > my case at least. I'll go back to just putting the SQL directly in
> >> >> > my
> >> >> > code.
> >> >> >
> >> >> > thanks for the reply.
> >> >> >
> >> >> > "Marina" <someone@.nospam.com> wrote in message
> >> >> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> >> >> >> You can only pass literal values in - not column names. If you
> > wanted
> >> > to
> >> >> > do
> >> >> >> something like that, you would have to create a string with the
> > query
> >> > and
> >> >> >> execute it.
> >> >> >>
> >> >> >> Also, you can try passing in @.SortBy as an integer, and sorting
on
> > the
> >> >> >> column order, instead of using the column name to perform the
sort.
> > I
> >> >> >> haven't tried it, but it might work - not sure if the order by
can
> > use
> >> >> >> any
> >> >> >> parameters.
> >> >> >>
> >> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> >> >> >> > I'm very new to stored procedures! I am using MSDE2000 and
> >> >> >> > asp.net
> >> >> >> WebMatrix
> >> >> >> > to work on this page. Currently I
> >> >> >> > have this in the stored procedure:
> >> >> >> >
> >> >> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> >> >> >> > AS
> >> >> >> >
> >> >> >> > SELECT MainID, Title, ShortDesc, DateCreated,
> >> >> >> > EstimatedDeliveryDate,
> >> >> >> > Status
> >> >> >> > FROM tblMain
> >> >> >> > ORDER BY @.SortBy
> >> >> >> >
> >> >> >> > GO
> >> >> >> >
> >> >> >> > I get this error when trying to save it:
> >> >> >> > "The select item identified by the ORDER BY number 1 contains a
> >> >> >> > variable
> >> >> >> as
> >> >> >> > part of the expression identifying a column position. Variables
> > are
> >> >> >> > only
> >> >> >> > allowed when ordering by an expression referencing a column
> >> >> >> > name."
> >> >> >> >
> >> >> >> > any help would be very much appreciated. Thanks!
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
The DataView is just that--a "view" on a DataTable. DataTables are static
rowsets and not "pageable". That is, they are filled in a single operation
(via the Fill method) and the connection is closed. They do not work like a
server-side cursor that permits you to scroll around in a selected rowset.
Is it smarter to requery to resort? Usually not. Consider that a good app
will keep only as many rows as the user can deal with in memory (in the
DataTable) so it's often necessary to requery to get more data. To resort?
Nope, this can be done by the DataView.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"djc" <noone@.nowhere.com> wrote in message
news:uNh8GIawEHA.1408@.TK2MSFTNGP10.phx.gbl...
> ok. cool. So then this suggested approach is to use a dataView to bind to
> the datagrid. I assume the dataView is *not* forward only then, since I
> need
> the datagrid to be pageable. One last question with regard to this
> approach
> compared to calling a stored procedure then... and not the stored
> procedure
> you see below but one without any dynamic sql. Will there be significant
> performance differences? In other words would it be faster to sort or
> filter
> a dataview than to call another stored procedure to do it?
> thanks again!
> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> news:OavWl0RwEHA.1292@.TK2MSFTNGP10.phx.gbl...
>> Sure. That's a great way to add sort, seek, find, filter to what you see
> in
>> a DataGrid.
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> __________________________________
>> "djc" <noone@.nowhere.com> wrote in message
>> news:OQikmKQwEHA.1564@.TK2MSFTNGP09.phx.gbl...
>> > ok. I'm still unclear as to whether I could assign a dataView to the
>> > dataSource property of a dataGrid though? (a sortable, pagable,
> datagrid)
>> > I'll look more into it.
>> >
>> > thanks for the reply.
>> >
>> > "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
>> > news:OKrTGFQwEHA.2016@.TK2MSFTNGP15.phx.gbl...
>> >> A DataView is generally associated with a DataTable. You fill the
>> > DataTable
>> >> and use the DefaultView to get the DataView.
>> >>
>> >> --
>> >> ____________________________________
>> >> William (Bill) Vaughn
>> >> Author, Mentor, Consultant
>> >> Microsoft MVP
>> >> www.betav.com
>> >> Please reply only to the newsgroup so that others can benefit.
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >> __________________________________
>> >>
>> >> "djc" <noone@.nowhere.com> wrote in message
>> >> news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
>> >> >I thought I would get that response from someone! hehe. No fear...
>> >> >I'm
>> >> > commited to learning stored procedures... I was just frustrated.
>> >> > Programming
>> >> > is more hobby than job for me right now. I'm actually a network
>> >> > administrator. I just find this stuff fun for some wierd reason!
>> >> >
>> >> > Please correct me if I'm wrong but I don't think I can use a
>> >> > DataView
>> >> > in
>> >> > my
>> >> > case. The data I'm retrieving is for the datasource of a DataGrid
> that
>> >> > needs
>> >> > to be both sortable and pagable. I'm currently using a DataTable..
> can
>> >> > I
>> >> > use
>> >> > a DataView for that?
>> >> >
>> >> > any input is appreciated. Thanks.
>> >> >
>> >> >
>> >> >
>> >> > "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
>> >> > news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
>> >> >> What am I missing?
>> >> >>
>> >> >> SQL Server is primarily optimized for storing and retrieving data.
>> >> >>
>> >> >> If the datasets are small enough (and they should be!), isn't using
> a
>> >> >> Dataview then appropriate?
>> >> >>
>> >> >> Do your CRUD with stored procs.
>> >> >> Do your display with a DataView.
>> >> >>
>> >> >> With the dataview you can then use the RowFilter and Sort
>> >> >> properties
>> >> >> to
>> >> >> control the display of the data.
>> >> >>
>> >> >> You are opening yourself up for SQL injection attacks and
>> >> >> missing/extra
>> >> >> quotes when you use string concatentation in code.
>> >> >>
>> >> >> "djc" <noone@.nowhere.com> wrote in message
>> >> >> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
>> >> >> >I guess that means the ORDER BY can't use a parameter for the ASC
> or
>> >> >> >DESC
>> >> >> > options either then huh? So much for benefiting from stored
>> > procedures,
>> >> > in
>> >> >> > my case at least. I'll go back to just putting the SQL directly
>> >> >> > in
>> >> >> > my
>> >> >> > code.
>> >> >> >
>> >> >> > thanks for the reply.
>> >> >> >
>> >> >> > "Marina" <someone@.nospam.com> wrote in message
>> >> >> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> >> >> >> You can only pass literal values in - not column names. If you
>> > wanted
>> >> > to
>> >> >> > do
>> >> >> >> something like that, you would have to create a string with the
>> > query
>> >> > and
>> >> >> >> execute it.
>> >> >> >>
>> >> >> >> Also, you can try passing in @.SortBy as an integer, and sorting
> on
>> > the
>> >> >> >> column order, instead of using the column name to perform the
> sort.
>> > I
>> >> >> >> haven't tried it, but it might work - not sure if the order by
> can
>> > use
>> >> >> >> any
>> >> >> >> parameters.
>> >> >> >>
>> >> >> >> "djc" <noone@.nowhere.com> wrote in message
>> >> >> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> >> >> >> > I'm very new to stored procedures! I am using MSDE2000 and
>> >> >> >> > asp.net
>> >> >> >> WebMatrix
>> >> >> >> > to work on this page. Currently I
>> >> >> >> > have this in the stored procedure:
>> >> >> >> >
>> >> >> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
>> >> >> >> > AS
>> >> >> >> >
>> >> >> >> > SELECT MainID, Title, ShortDesc, DateCreated,
>> >> >> >> > EstimatedDeliveryDate,
>> >> >> >> > Status
>> >> >> >> > FROM tblMain
>> >> >> >> > ORDER BY @.SortBy
>> >> >> >> >
>> >> >> >> > GO
>> >> >> >> >
>> >> >> >> > I get this error when trying to save it:
>> >> >> >> > "The select item identified by the ORDER BY number 1 contains
>> >> >> >> > a
>> >> >> >> > variable
>> >> >> >> as
>> >> >> >> > part of the expression identifying a column position.
>> >> >> >> > Variables
>> > are
>> >> >> >> > only
>> >> >> >> > allowed when ordering by an expression referencing a column
>> >> >> >> > name."
>> >> >> >> >
>> >> >> >> > any help would be very much appreciated. Thanks!
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>
Thanks again. You have been very helpful!
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:ugPl5PpwEHA.3584@.TK2MSFTNGP10.phx.gbl...
> The DataView is just that--a "view" on a DataTable. DataTables are static
> rowsets and not "pageable". That is, they are filled in a single operation
> (via the Fill method) and the connection is closed. They do not work like
a
> server-side cursor that permits you to scroll around in a selected rowset.
> Is it smarter to requery to resort? Usually not. Consider that a good app
> will keep only as many rows as the user can deal with in memory (in the
> DataTable) so it's often necessary to requery to get more data. To resort?
> Nope, this can be done by the DataView.
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "djc" <noone@.nowhere.com> wrote in message
> news:uNh8GIawEHA.1408@.TK2MSFTNGP10.phx.gbl...
> > ok. cool. So then this suggested approach is to use a dataView to bind
to
> > the datagrid. I assume the dataView is *not* forward only then, since I
> > need
> > the datagrid to be pageable. One last question with regard to this
> > approach
> > compared to calling a stored procedure then... and not the stored
> > procedure
> > you see below but one without any dynamic sql. Will there be significant
> > performance differences? In other words would it be faster to sort or
> > filter
> > a dataview than to call another stored procedure to do it?
> >
> > thanks again!
> >
> > "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> > news:OavWl0RwEHA.1292@.TK2MSFTNGP10.phx.gbl...
> >> Sure. That's a great way to add sort, seek, find, filter to what you
see
> > in
> >> a DataGrid.
> >>
> >> --
> >> ____________________________________
> >> William (Bill) Vaughn
> >> Author, Mentor, Consultant
> >> Microsoft MVP
> >> www.betav.com
> >> Please reply only to the newsgroup so that others can benefit.
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >> __________________________________
> >>
> >> "djc" <noone@.nowhere.com> wrote in message
> >> news:OQikmKQwEHA.1564@.TK2MSFTNGP09.phx.gbl...
> >> > ok. I'm still unclear as to whether I could assign a dataView to the
> >> > dataSource property of a dataGrid though? (a sortable, pagable,
> > datagrid)
> >> > I'll look more into it.
> >> >
> >> > thanks for the reply.
> >> >
> >> > "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in
message
> >> > news:OKrTGFQwEHA.2016@.TK2MSFTNGP15.phx.gbl...
> >> >> A DataView is generally associated with a DataTable. You fill the
> >> > DataTable
> >> >> and use the DefaultView to get the DataView.
> >> >>
> >> >> --
> >> >> ____________________________________
> >> >> William (Bill) Vaughn
> >> >> Author, Mentor, Consultant
> >> >> Microsoft MVP
> >> >> www.betav.com
> >> >> Please reply only to the newsgroup so that others can benefit.
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> > rights.
> >> >> __________________________________
> >> >>
> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> news:%237V5GpNwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> >> >> >I thought I would get that response from someone! hehe. No fear...
> >> >> >I'm
> >> >> > commited to learning stored procedures... I was just frustrated.
> >> >> > Programming
> >> >> > is more hobby than job for me right now. I'm actually a network
> >> >> > administrator. I just find this stuff fun for some wierd reason!
> >> >> >
> >> >> > Please correct me if I'm wrong but I don't think I can use a
> >> >> > DataView
> >> >> > in
> >> >> > my
> >> >> > case. The data I'm retrieving is for the datasource of a DataGrid
> > that
> >> >> > needs
> >> >> > to be both sortable and pagable. I'm currently using a DataTable..
> > can
> >> >> > I
> >> >> > use
> >> >> > a DataView for that?
> >> >> >
> >> >> > any input is appreciated. Thanks.
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Jim Hughes" <NOSPAMJ3033@.Hotmail.com> wrote in message
> >> >> > news:eR2lXoHwEHA.1404@.TK2MSFTNGP11.phx.gbl...
> >> >> >> What am I missing?
> >> >> >>
> >> >> >> SQL Server is primarily optimized for storing and retrieving
data.
> >> >> >>
> >> >> >> If the datasets are small enough (and they should be!), isn't
using
> > a
> >> >> >> Dataview then appropriate?
> >> >> >>
> >> >> >> Do your CRUD with stored procs.
> >> >> >> Do your display with a DataView.
> >> >> >>
> >> >> >> With the dataview you can then use the RowFilter and Sort
> >> >> >> properties
> >> >> >> to
> >> >> >> control the display of the data.
> >> >> >>
> >> >> >> You are opening yourself up for SQL injection attacks and
> >> >> >> missing/extra
> >> >> >> quotes when you use string concatentation in code.
> >> >> >>
> >> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> >> news:uKnnFuFwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> >> >> >> >I guess that means the ORDER BY can't use a parameter for the
ASC
> > or
> >> >> >> >DESC
> >> >> >> > options either then huh? So much for benefiting from stored
> >> > procedures,
> >> >> > in
> >> >> >> > my case at least. I'll go back to just putting the SQL directly
> >> >> >> > in
> >> >> >> > my
> >> >> >> > code.
> >> >> >> >
> >> >> >> > thanks for the reply.
> >> >> >> >
> >> >> >> > "Marina" <someone@.nospam.com> wrote in message
> >> >> >> > news:eOlldoFwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> >> >> >> >> You can only pass literal values in - not column names. If
you
> >> > wanted
> >> >> > to
> >> >> >> > do
> >> >> >> >> something like that, you would have to create a string with
the
> >> > query
> >> >> > and
> >> >> >> >> execute it.
> >> >> >> >>
> >> >> >> >> Also, you can try passing in @.SortBy as an integer, and
sorting
> > on
> >> > the
> >> >> >> >> column order, instead of using the column name to perform the
> > sort.
> >> > I
> >> >> >> >> haven't tried it, but it might work - not sure if the order by
> > can
> >> > use
> >> >> >> >> any
> >> >> >> >> parameters.
> >> >> >> >>
> >> >> >> >> "djc" <noone@.nowhere.com> wrote in message
> >> >> >> >> news:OHwF0RFwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> >> >> >> >> > I'm very new to stored procedures! I am using MSDE2000 and
> >> >> >> >> > asp.net
> >> >> >> >> WebMatrix
> >> >> >> >> > to work on this page. Currently I
> >> >> >> >> > have this in the stored procedure:
> >> >> >> >> >
> >> >> >> >> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> >> >> >> >> > AS
> >> >> >> >> >
> >> >> >> >> > SELECT MainID, Title, ShortDesc, DateCreated,
> >> >> >> >> > EstimatedDeliveryDate,
> >> >> >> >> > Status
> >> >> >> >> > FROM tblMain
> >> >> >> >> > ORDER BY @.SortBy
> >> >> >> >> >
> >> >> >> >> > GO
> >> >> >> >> >
> >> >> >> >> > I get this error when trying to save it:
> >> >> >> >> > "The select item identified by the ORDER BY number 1
contains
> >> >> >> >> > a
> >> >> >> >> > variable
> >> >> >> >> as
> >> >> >> >> > part of the expression identifying a column position.
> >> >> >> >> > Variables
> >> > are
> >> >> >> >> > only
> >> >> >> >> > allowed when ordering by an expression referencing a column
> >> >> >> >> > name."
> >> >> >> >> >
> >> >> >> >> > any help would be very much appreciated. Thanks!
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >>
> >> >> >> >>
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
On other database platforms I have done things similar to what you are trying
to do with your strored procedure. Although it is not elegant it works and
the logic is on the Database:
create procedure mysql @.sortby varchar(12) As
if @.sortby = 'CustomerId'
Begin
select CustomerId, CompanyName, Address
from Customers
order by CustomerId
End
else
if @.sortby = 'CompanyName'
begin
select CustomerId, CompanyName, Address
from Customers
order by CompanyName
end
else
begin
select CustomerId, CompanyName, Address
from Customers
end
"djc" wrote:
> I'm very new to stored procedures! I am using MSDE2000 and asp.net WebMatrix
> to work on this page. Currently I
> have this in the stored procedure:
> CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> AS
> SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> Status
> FROM tblMain
> ORDER BY @.SortBy
> GO
> I get this error when trying to save it:
> "The select item identified by the ORDER BY number 1 contains a variable as
> part of the expression identifying a column position. Variables are only
> allowed when ordering by an expression referencing a column name."
> any help would be very much appreciated. Thanks!
>
>
thanks for the input.
"fom" <fom@.discussions.microsoft.com> wrote in message
news:FD631212-3D7C-4C58-AF90-961FA566770A@.microsoft.com...
> On other database platforms I have done things similar to what you are
trying
> to do with your strored procedure. Although it is not elegant it works and
> the logic is on the Database:
> create procedure mysql @.sortby varchar(12) As
> if @.sortby = 'CustomerId'
> Begin
> select CustomerId, CompanyName, Address
> from Customers
> order by CustomerId
> End
> else
> if @.sortby = 'CompanyName'
> begin
> select CustomerId, CompanyName, Address
> from Customers
> order by CompanyName
> end
> else
> begin
> select CustomerId, CompanyName, Address
> from Customers
> end
>
> "djc" wrote:
> > I'm very new to stored procedures! I am using MSDE2000 and asp.net
WebMatrix
> > to work on this page. Currently I
> > have this in the stored procedure:
> >
> > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> > AS
> >
> > SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> > Status
> > FROM tblMain
> > ORDER BY @.SortBy
> >
> > GO
> >
> > I get this error when trying to save it:
> > "The select item identified by the ORDER BY number 1 contains a variable
as
> > part of the expression identifying a column position. Variables are only
> > allowed when ordering by an expression referencing a column name."
> >
> > any help would be very much appreciated. Thanks!
> >
> >
> >
> >
Just as an aside you can do a conditional order by clause like this
order by Case When @.SortBy1 = '1' Then ContactFirstForename
When @.SortBy1 = '2' Then ContactSurname
When @.SortBy1 = '3' Then ContactTelephoneSurname
End
Only thing you may need to do is make sure that the typing is consistant;
you cant mix numeric and character fields; but you can have as many
parameters as you like so you can do @.SortBY1 = 4 then 'A' and add a sortby2
as neccessary
"djc" wrote:
> thanks for the input.
> "fom" <fom@.discussions.microsoft.com> wrote in message
> news:FD631212-3D7C-4C58-AF90-961FA566770A@.microsoft.com...
> > On other database platforms I have done things similar to what you are
> trying
> > to do with your strored procedure. Although it is not elegant it works and
> > the logic is on the Database:
> >
> > create procedure mysql @.sortby varchar(12) As
> > if @.sortby = 'CustomerId'
> > Begin
> > select CustomerId, CompanyName, Address
> > from Customers
> > order by CustomerId
> > End
> > else
> > if @.sortby = 'CompanyName'
> > begin
> > select CustomerId, CompanyName, Address
> > from Customers
> > order by CompanyName
> > end
> > else
> > begin
> > select CustomerId, CompanyName, Address
> > from Customers
> > end
> >
> >
> >
> > "djc" wrote:
> >
> > > I'm very new to stored procedures! I am using MSDE2000 and asp.net
> WebMatrix
> > > to work on this page. Currently I
> > > have this in the stored procedure:
> > >
> > > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> > > AS
> > >
> > > SELECT MainID, Title, ShortDesc, DateCreated, EstimatedDeliveryDate,
> > > Status
> > > FROM tblMain
> > > ORDER BY @.SortBy
> > >
> > > GO
> > >
> > > I get this error when trying to save it:
> > > "The select item identified by the ORDER BY number 1 contains a variable
> as
> > > part of the expression identifying a column position. Variables are only
> > > allowed when ordering by an expression referencing a column name."
> > >
> > > any help would be very much appreciated. Thanks!
> > >
> > >
> > >
> > >
>
>
Thanks for the info.
"Chris Hoare" <ChrisHoare@.discussions.microsoft.com> wrote in message
news:D6580D37-CC7C-4C04-8381-91BFAD177D14@.microsoft.com...
> Just as an aside you can do a conditional order by clause like this
> order by Case When @.SortBy1 = '1' Then ContactFirstForename
> When @.SortBy1 = '2' Then ContactSurname
> When @.SortBy1 = '3' Then ContactTelephoneSurname
> End
> Only thing you may need to do is make sure that the typing is consistant;
> you cant mix numeric and character fields; but you can have as many
> parameters as you like so you can do @.SortBY1 = 4 then 'A' and add a
sortby2
> as neccessary
> "djc" wrote:
> > thanks for the input.
> >
> > "fom" <fom@.discussions.microsoft.com> wrote in message
> > news:FD631212-3D7C-4C58-AF90-961FA566770A@.microsoft.com...
> > > On other database platforms I have done things similar to what you are
> > trying
> > > to do with your strored procedure. Although it is not elegant it works
and
> > > the logic is on the Database:
> > >
> > > create procedure mysql @.sortby varchar(12) As
> > > if @.sortby = 'CustomerId'
> > > Begin
> > > select CustomerId, CompanyName, Address
> > > from Customers
> > > order by CustomerId
> > > End
> > > else
> > > if @.sortby = 'CompanyName'
> > > begin
> > > select CustomerId, CompanyName, Address
> > > from Customers
> > > order by CompanyName
> > > end
> > > else
> > > begin
> > > select CustomerId, CompanyName, Address
> > > from Customers
> > > end
> > >
> > >
> > >
> > > "djc" wrote:
> > >
> > > > I'm very new to stored procedures! I am using MSDE2000 and asp.net
> > WebMatrix
> > > > to work on this page. Currently I
> > > > have this in the stored procedure:
> > > >
> > > > CREATE PROCEDURE GetAllIssues @.SortBy VarChar
> > > > AS
> > > >
> > > > SELECT MainID, Title, ShortDesc, DateCreated,
EstimatedDeliveryDate,
> > > > Status
> > > > FROM tblMain
> > > > ORDER BY @.SortBy
> > > >
> > > > GO
> > > >
> > > > I get this error when trying to save it:
> > > > "The select item identified by the ORDER BY number 1 contains a
variable
> > as
> > > > part of the expression identifying a column position. Variables are
only
> > > > allowed when ordering by an expression referencing a column name."
> > > >
> > > > any help would be very much appreciated. Thanks!
> > > >
> > > >
> > > >
> > > >
> >
> >
> >