CODEWITHSUNDEEP
c#sqlsqlite
Cracker Boy
Cracker Boy

Reputation: 59

Query results are not correct in C#.NET with SQLite database

I have an application implemented in C#.NET and SQLite database. I need to count some values of a columns (status) of database table (fruit). The table is like following.

Id  B   M   status
1   10  101 2
2   11  102 2
3   11  103 0
4   12  104 2
5   13  105 2
6   13  105 2
7   14  106 2
8   14  106 2
9   14  106 2
10  14  106 2

I have used following methods to extract the values from the table and show output.

public void CountingValues()
        {    
        string strA, strB, strC;
        double Total;            

        strA = countA();
        strB = countB();
        strC = countC();    

        MessageBox.Show(strA.ToString());
        MessageBox.Show(strB.ToString()); 
        MessageBox.Show(strC.ToString());

}
    public string countA() 
    {
        return GetData("SELECT COUNT(status) FROM fruit WHERE status = 2;");
    }
    public string countB()
    {
        return GetData("SELECT COUNT(status) FROM fruit WHERE status = 1;");
    }
    public string countC()
    {
        return GetData("SELECT COUNT(status) FROM fruit WHERE status = 0;");
    }


    private String GetData(String cmd)
    {
        String result = "";
        SQLiteCommand command = new SQLiteCommand(connection);
        command.CommandText = cmd;
        SQLiteDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            result = reader[0].ToString();                
        }
        reader.Dispose();
        command.Dispose();
        return result;
    }

The results of strA, strB and strC should be 9, 0, and 1 respectively. But, it is showing 4, 0, and 6. Could anyone tell me please what is wrong here ?

Upvotes: 1

Views: 1162

Answers (1)

Erkin Djindjiev
Erkin Djindjiev

Reputation: 577

Try using select count(*) instead of select count(status).. Also if you are using visual studio put a breakpoint on the "result = reader[0].ToString()" and see what value is being populated.

Anytime i do database debugging I check to make sure the datatable is the same as what i would expect to receive from the sql query. put a breakpoint on return dt and make sure that you get the same datatable result as if you were to just execute this query on the db. Make sure you change the sqlconnection object to the type you are using.

public DataTable GetData()
{
     SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BarManConnectionString"].ConnectionString);
     conn.Open();
     string query = "SELECT count(*) from fruit where status = 2";
     SqlCommand cmd = new SqlCommand(query, conn);

     DataTable dt = new DataTable();
     dt.Load(cmd.ExecuteReader());
     return dt;
}

Upvotes: 1

Related Questions