Data Query

Sean89

Can someone tell me why this is returning 0?

        static void Main(string[] args)
        {
            string dataPath = @"C:\Documents and Settings\Administrator\Desktop\Transimount_Data.MDB";
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; data source=" + dataPath);

            OleDbCommand cmd = new OleDbCommand("SELECT * FROM Mounts WHERE Group_Number = 1", conn);

            conn.Open();
            int count = cmd.ExecuteNonQuery();
            conn.Close();

            Console.WriteLine(count); 
        }

I am using this to get how many records are in Group 1 of a collection of parts. The datatable in the database currently looks like this:

Mount_Number	Carrier_Part_Number	Group_Number
KMC84191	72-62039-02	          5
KMK64191	72-62039-00	          3
KMK71074	72-62030-aa	          1
KMK71374	72-62030-bb	          1
KMK72073	72-62030-cc	          1
KMK72673	72-62030-dd	          1
KMM61901	72-62038-03	          6
KMM61990	72-62038-05	          2
KMM62600	72-62038-04	          6
KMM62690	72-62038-02	          2
KMM64191	72-62039-01	          4

I'm a little tired so I hope this is a stupid mistake ;) Thanks for any help!

Kayess Tech

Hi Sean, If all you want the the count then just change your SQL to SELECT COUNT(*) FROM Mounts WHERE Group_Number = 1 HTH Web design and hosting http://www.kayess.com.au

Sean89

Thanks for the response! I tried it and it still seems to be returning 0. Even if I change the query to select everything: SELECT * FROM Mounts It still seems to say that the number of rows affected is 0. I'll just use this till I decide to figure out whats wrong:

            OleDbCommand cmd = new OleDbCommand("SELECT COUNT(*) FROM Groups", conn);

            conn.Open();

            OleDbDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                int count = reader[0];
            }

            conn.Close();

It works at least ;P

Rob Graham

Since your query changed no rows, the rows affected will be 0... You can't get a rowcount from a datareader until you read to the end. The result of your query IS a single record containing a single column whose value is the number of records matching the query. I wouldn't know how else to get it than he way you settled on... We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest. Patricia Aburdene Bulls make money, bears make money, pigs get slaughtered. Jim Cramer

Sean89

Thats what I thought too, but for some reason I thought I had done it with ExecuteNonQuery(); before... Guess not ;P Thanks for the help.

Kuira

Try this:

static void Main(string[] args)
{
   string dataPath = @"C:\Documents and Settings\Administrator\Desktop\Transimount_Data.MDB";
   OleDbConnection conn = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; data source=" + dataPath);

   OleDbCommand cmd = new OleDbCommand("SELECT COUNT(*) FROM Mounts WHERE Group_Number = 1", conn);

   conn.Open();
   int count = cmd.ExecuteScalar();
   conn.Close();

   Console.WriteLine(count); 
}

Kuira

Sean89

Yeah that works :) Thanks!