Tabeladapter with row_number() over...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
In your place I would create such data adapters either manually or using a
code generator.
 
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.
 
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!
 
Back
Top