i am trying to get the row nos. as [ DuplicateNo ] column in select query.
<div>
string oledbQueryStr =
"SELECT " + PKTOAPPEND +
" AS [UploadModelCSVID]," + CRBYTOAPPEND +
" AS [CrById],ROW_NUMBER() OVER ( ORDER BY MachineryName ) AS DuplicateNo,"
+ "* FROM [Sheet1$];";
before
this i was successfully doing--
string oledbQueryStr = "SELECT " + PKTOAPPEND + " AS [UploadModelCSVID]," + CRBYTOAPPEND + " AS [CrById]," + "* FROM [Sheet1$];";
but
in 1st query if i insert breakpoint and execute in Sql server by replacing [Sheet1] by my table name then it works perfectly.
can
anyone tell me what to do in oledb commands to insert row numbers????
or
is there any need of cursor or some??
the
1st query results in the exception--
</div>
Syntax
error (missing operator) in query expression 'ROW_NUMBER() OVER ( ORDER BY MachineryName )'.
I have two big books on Excel and cross searched OLEDB documentation and I found as well no references that the MSSQL ROW_NUMBER function is supported in Excel OLEDB.
ankush_kulka...
Member
23 Points
22 Posts
getting row numbers in select query result
Apr 16, 2011 09:02 AM|LINK
Hi
i am trying to get the row nos. as [ DuplicateNo ] column in select query.
<div>
string oledbQueryStr = "SELECT " + PKTOAPPEND + " AS [UploadModelCSVID]," + CRBYTOAPPEND + " AS [CrById],ROW_NUMBER() OVER ( ORDER BY MachineryName ) AS DuplicateNo," + "* FROM [Sheet1$];";
before this i was successfully doing--
string oledbQueryStr = "SELECT " + PKTOAPPEND + " AS [UploadModelCSVID]," + CRBYTOAPPEND + " AS [CrById]," + "* FROM [Sheet1$];";
but in 1st query if i insert breakpoint and execute in Sql server by replacing [Sheet1] by my table name then it works perfectly.
can anyone tell me what to do in oledb commands to insert row numbers????
or is there any need of cursor or some??
the 1st query results in the exception--
</div>Syntax error (missing operator) in query expression 'ROW_NUMBER() OVER ( ORDER BY MachineryName )'.
Lannie
Contributor
3840 Points
749 Posts
Re: getting row numbers in select query result
Apr 16, 2011 08:36 PM|LINK
Replace
DuplicateNo," + "FROM
with
DuplicateNo" + "FROM
there should be no comma before a FROM statement.
ankush_kulka...
Member
23 Points
22 Posts
Re: getting row numbers in select query result
Apr 18, 2011 07:55 AM|LINK
It is DuplicateNo," + " * FROM
* is there between DuplicateNo and FROM.
ankush_kulka...
Member
23 Points
22 Posts
Re: getting row numbers in select query result
Apr 18, 2011 08:49 AM|LINK
the 1st query results in the exception--
Syntax error (missing operator) in query expression 'ROW_NUMBER() OVER ( ORDER BY MachineryName )'.
Lannie
Contributor
3840 Points
749 Posts
Re: getting row numbers in select query result
Apr 18, 2011 08:09 PM|LINK
It is harder to read this new smaller font that appeare in the recent forum upgrade.
Do we even know if Excel OLEDB supports the ROW_NUMBER() function at all???
ankush_kulka...
Member
23 Points
22 Posts
Re: getting row numbers in select query result
Apr 19, 2011 06:44 AM|LINK
thats what i need to know..
ROW_NUMBER() must be having different syntax in oledb command altogether I searched through internet but no use.
Also REPLACE() doesnt work under oledb.
megh1207
Contributor
2276 Points
495 Posts
Re: getting row numbers in select query result
Apr 19, 2011 08:16 AM|LINK
you can get row number using query in following way
select ID, Name, ROW_Number() over(order by ID) as 'RowNum' from User
here this query will help you to get row number from your table.
ankush_kulka...
Member
23 Points
22 Posts
Re: getting row numbers in select query result
Apr 19, 2011 12:33 PM|LINK
yes
but whether it would work under oledb command??
Lannie
Contributor
3840 Points
749 Posts
Re: getting row numbers in select query result
Apr 19, 2011 09:39 PM|LINK
I have two big books on Excel and cross searched OLEDB documentation and I found as well no references that the MSSQL ROW_NUMBER function is supported in Excel OLEDB.
ankush_kulka...
Member
23 Points
22 Posts
Re: getting row numbers in select query result
Apr 20, 2011 05:30 AM|LINK
thank you..
i also found no reference to it on net.