Simulating RowCount in SQL CE 3.0

Question

0.00/5 (No votes)

See more:

Hi my friends,

I have a database on "SQL Compact Edition 3.0". And I cant upgrade it to new version. I want to get row count from some record that i select and as default this version of sql ce does not support.

So, How can I simulate RowCount in "Sql CE 3.0"?

I tried a lot of way for resolve this issue but my solutions didn't works well.

Thanks for your help. I waiting for them :-)

Posted 2-Jan-12 2:28am

mhd.rad

Updated 2-Jan-12 2:31am

v3

Add a Solution

Comments

DaveAuld 2-Jan-12 8:40am

"I want to get row count from some record", that would be 1 then ???? Do you mean table? or do you mean you want the rowcount for a returned query? Please update the question and clarify. Thanks.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2012-01-02T02:47:00

I'm not sure what RowCount has to do with the record you just inserted - if you inserted it you know how many of it there is...

But to get the RowCount in SqlCE is as easy as it is in MsSql:

SQL

SELECT COUNT(*) FROM MyTable

If this isn't what you are after, what is the problem you are having, exactly?

":). Yah, its really angry me.
I tried Top at first but, The SQL CE 3.0 does not support "Top" function :(
I use this code:

define @a = 20 --@a is a variable ;) if(SELECT COUNT(*) FROM table WHERE id BETWEEN 10 AND a<10) { -- @a++ }

But there is a problem. How to make a loop in SQL?"

I had forgotten that TOP was introduced in V3.5! :O

To be honest, I'm not sure I would bother.
Bearing in mind that SQLCE is single user, and all the resources used are local to your PC (or other equipment), it makes no odds particularly if you retrieve all the records and filter them locally. Or at least retrieve all the IDs into an array and then request just the relevant records. (SQLCE is not very efficient when it comes to complex queries at the best of times)

What are you trying to use the DB on?

Wendelius · Answer 2 · 2012-01-02T08:52:00

Try modifying the query to use correlated sub-query. Something like:

SQL

SELECT ...
FROM YourTable yt
WHERE 10 > (SELECT COUNT(*)
             FROM YourTable yt2
             WHERE yt2.Id < yt.Id)
That should bring you the first 10.

If you want to go further in pages add necessary where conditions (Id > greatest value this far)

Simulating RowCount in SQL CE 3.0

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0