Tabeladapter with row_number() over...

Hi Friends,
I have created a tableadapter (using wizard of VS2005) with some SQL query
statement. But I want to write SQL query such a way that it returns table
with one more fields, i.e. Row number.
I have tried this SQL query in SQL server 2005:

select ordernumber, row_number() over( order by ordernumber) sno from
orderhead

It's working in SQL server 2005 but not in table adapter (It gives SQL
syntax error in 'Add Query' wizard).
Please help me to add such a column in SQL.

And why should tableadapter give any error? This query is working with
dataadapter!! I mean, this code is working

Dim ds As New DataSet
Dim da As New SqlClient.SqlDataAdapter("select ordernumber,
row_number() over( order by ordernumber) sno from orderhead" _
, conStr)
da.Fill(ds, "TAB1")


Thanks in Advanced,

-Yogee

Miha
Mon Jun 05 07:16:11 CDT 2006

In your place I would create such data adapters either manually or using a
code generator.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"yogee" <yogee@discussions.microsoft.com> wrote in message
news:D023B556-40A1-47BA-9F62-944CD5918C1B@microsoft.com...
> Hi Friends,
> I have created a tableadapter (using wizard of VS2005) with some SQL query
> statement. But I want to write SQL query such a way that it returns table
> with one more fields, i.e. Row number.
> I have tried this SQL query in SQL server 2005:
>
> select ordernumber, row_number() over( order by ordernumber) sno from
> orderhead
>
> It's working in SQL server 2005 but not in table adapter (It gives SQL
> syntax error in 'Add Query' wizard).
> Please help me to add such a column in SQL.
>
> And why should tableadapter give any error? This query is working with
> dataadapter!! I mean, this code is working
>
> Dim ds As New DataSet
> Dim da As New SqlClient.SqlDataAdapter("select ordernumber,
> row_number() over( order by ordernumber) sno from orderhead" _
> , conStr)
> da.Fill(ds, "TAB1")
>
>
> Thanks in Advanced,
>
> -Yogee

yogee
Tue Jun 06 04:16:02 CDT 2006

First I have created dataset. and then dragged tableadapter into the screen.
So a wizard came to add a query. So it's basically automatic code generation
but no code is generated because I am getting sql syntax error while I supply
above query.

"Miha Markic [MVP C#]" wrote:

> In your place I would create such data adapters either manually or using a
> code generator.
>

Miha
Tue Jun 06 06:09:46 CDT 2006

I did mean I would avoid wizards like approach.
By a code generator I mean something like CodeSmith (www.codesmithtools.com)

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"yogee" <yogee@discussions.microsoft.com> wrote in message
news:72BF3936-303C-47A1-8CFE-EF8709D35D48@microsoft.com...
> First I have created dataset. and then dragged tableadapter into the
> screen.
> So a wizard came to add a query. So it's basically automatic code
> generation
> but no code is generated because I am getting sql syntax error while I
> supply
> above query.
>
> "Miha Markic [MVP C#]" wrote:
>
>> In your place I would create such data adapters either manually or using
>> a
>> code generator.
>>
>

yogee
Tue Jun 06 23:57:02 CDT 2006

Do you mean it's not recommanded to user wizard based dataset?

"Miha Markic [MVP C#]" wrote:

> I did mean I would avoid wizards like approach.
> By a code generator I mean something like CodeSmith (www.codesmithtools.com)
>

Miha
Wed Jun 07 03:27:48 CDT 2006

I wouldn't user wizards for numerous reasons, one of the biggest being: if
your database schema changes, wizard generated code won't reflect changes or
won't yell error!

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"yogee" <yogee@discussions.microsoft.com> wrote in message
news:E0A2817C-73F5-4942-9C52-192DA5BC62CA@microsoft.com...
> Do you mean it's not recommanded to user wizard based dataset?
>
> "Miha Markic [MVP C#]" wrote:
>
>> I did mean I would avoid wizards like approach.
>> By a code generator I mean something like CodeSmith
>> (www.codesmithtools.com)
>>

yogee
Wed Jun 07 03:42:01 CDT 2006

Yes that's problem, I have to use wizard again to regenerate the code.

"Miha Markic [MVP C#]" wrote:

> I wouldn't user wizards for numerous reasons, one of the biggest being: if
> your database schema changes, wizard generated code won't reflect changes or
> won't yell error!
>