CODEWITHSUNDEEP
c#sqlsqlite
David Tunnell
David Tunnell

Reputation: 7542

Count SQL Query not returning expected value

I'm trying to have a method return a count statement.

    public int getSettingsCount(string UserId, string Setting)
    {
        int LastSetting;
        //var user = new SqlDataLayer();
        using (var db = new SQLite.SQLiteConnection(this.DBPath))
        {
            {
                List<int> _setting = db.Query<int>("SELECT COUNT(*) FROM QTabSettings WHERE UserId = 1058 AND Setting = 'ServerDropdown' GROUP BY UserId;");
                LastSetting = Convert.ToInt32(_setting.SingleOrDefault());
            }
            return LastSetting;
        }
    }

When I execute the query it returns the correct value (6). However I am getting the value (0) from my above query.

How can I get the method to return the count as an int?

Upvotes: 1

Views: 139

Answers (2)

Jonathan Carroll
Jonathan Carroll

Reputation: 870

If I understand your question properly I think this is what you want:

LastSetting = _setting.FirstOrDefault();

or:

LastSetting = _setting[0];

Upvotes: 0

Rahul
Rahul

Reputation: 77896

You are using LIMIT 1 and still using List<int> that's strange and unnecessary. Also since it's count(*) there is no need of LIMIT 1 since the result would be a scalar data. Should change it to

int _setting = db.Query<int>("SELECT COUNT(*) FROM QTabSettings WHERE UserId = 1058 AND Setting = 'ServerDropdown';");
LastSetting = _setting;

Upvotes: 1

Related Questions