Output as Int row number after order by currency

Question

I'm new with C#. I would like some help finishing the code to execute a query to return the row number after an order by (currency). The table has user (person name) and currency (their amount of points). The query will order the database by currency then return the row number of that user (string input).

Table
user | currency
personname | #

This is what I have so far from googling.

 public int overallrank(string user)
    {
        String sql = "SELECT (SELECT COUNT(*) FROM " + channel + " AS chan2 WHERE chan2.currency > chan1.currency ) as ChanRank FROM " + channel + " as chan1 WHERE chan1.user = ' " + user + "'";
        cmd = new SQLiteCommand(sql, myDB);
        var result = cmd.ExecuteScalar();
        return result == DBNull.Value ? 0 : (int)result;
    }

`ExecuteScalar` is for running a query that returns one row with one column. Your query returns multiple columns. Look into using `ExecuteReader`. — juharr, Feb 14 '15 at 02:00
Your "triangular join" to get a fake row number is going to perform *horribly* for large data sets! Consider doing `create table tmp as select /*your query*/.` and then selecting the implicitly-created `rowid` column as [this](http://stackoverflow.com/a/19199219/57611) suggests. — ErikE, Feb 14 '15 at 02:43

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

As @juharr points out in his comment, ExecuteScalar is expecting a single row and single column. In that cell you need to get the rank from the database. Currently you are selecting all of the columns in chan1 along with the rank.

To get just the rank, you can modify your code like so:

public long overallrank(string user)
{
    String sql = "SELECT (SELECT COUNT(*) FROM " + channel + " AS chan2 WHERE chan2.currency > chan1.currency ) as ChanRank FROM " + channel + " as chan1 WHERE chan1.user = '" + user +"'";
    using (cmd = new SQLiteCommand(sql, myDB))
    {
       var result = cmd.ExecuteScalar();
       return result == DBNull.Value ? 0L : (long)result;
    }
}

edited May 23 '17 at 11:43

Community

1
1

answered Feb 14 '15 at 02:36

Matthew Jaspers

1,546
1
10
13

I'm getting a System.InvalidCastException on this line "count = (Int32) cmd.ExecuteScalar();" - how do I fix it? I tried changing it to executereader with no luck. – Jan Feb 14 '15 at 02:49
No, changing to `ExecuteReader` wouldn't help if you're trying to get a single value back. You can change the type that you're casting the result back to, I would start out by changing the cast from `(Int32)` to `(int)`. – Matthew Jaspers Feb 14 '15 at 03:05
I tried making that change, same error mistake still :( – Jan Feb 14 '15 at 03:10
Is there actually any result for that query or is it returning NULL? You can do `var result = cmd.ExecuteScalar();` and then compare to DBNull.Value: `return result == DBNull.Value ? 0 : (int)result`. – Matthew Jaspers Feb 14 '15 at 03:14
I tried changing it your way same error, but on return result == DBNull.Value ? 0 : (int)result; - maybe my query is bad. I updated my first post with your suggestion. – Jan Feb 14 '15 at 03:28
What is the message from the exception? It should tell you the two types it was trying to cast between. – Matthew Jaspers Feb 14 '15 at 04:09
It says Unhandled Exception: System.NullReferenceException: Object reference not set to an instance of an object. – Jan Feb 14 '15 at 04:57
I've updated my answer. I've tested this as working with both null and and an integer return. It looks like the `ExecuteScalar` is returning a `long` rather than an `int`. – Matthew Jaspers Feb 14 '15 at 13:50
Looks like the error was my fault. The string user input was case sensitive, not sure why it mattered for this query compared to others. Thanks so much for your time. Another user recommended I use create table tmp as select /*your query*/ to reduce load, could you help me add that? – Jan Feb 14 '15 at 18:01
I have added COLLATE NOCASE to the end of my query to fix the problem. One thing I noticed is the result is always 1 less because rank 1 starts at 0, so I added one to (long)result at the end. – Jan Feb 14 '15 at 18:14

Output as Int row number after order by currency

1 Answers1