retrieve all ids from the database

Question

I have a table called "Products". I want to retrieve all ID's of all entries and store them in an array.

 conn = new OleDbConnection();
                conn.ConnectionString = "myconnectionstring";
                conn.Open();
                query = "SELECT * from Products";
                cmd = new OleDbCommand(query,conn);

                reader = cmd.ExecuteReader();
                 //Int16 a=(int)cmd.ExecuteScalar();

                 Int16[] id = { 0 };

                 int counter = 0;
                 while (reader.Read())
                 {
                     counter++;
                     id[counter] = reader.GetInt16(0); //I got error here that aray is     out of bount
                 }
                 foreach (Int16 a in id)
                 {
                     Console.WriteLine(a);
                 }
                conn.Close();
                return id;

I have changed my code...it throws an exception.it say specified cast is not valid..

conn = new OleDbConnection();
                conn.ConnectionString = "my connetion string";
                conn.Open();
                query = "SELECT ID from Products";
                cmd = new OleDbCommand(query,conn);

                reader = cmd.ExecuteReader();
                 //Int16 a=(int)cmd.ExecuteScalar();

                List<Int16> id = new List<Int16>();

                 while (reader.Read())
                 {

                     id.Add(reader.GetInt16(0));
                 }

                conn.Close();
                return id;

Answer 1

My problem is solved. Actually the data I was retrieving was of type int32 .I just change int16 to int32.

conn = new OleDbConnection();
                conn.ConnectionString = "my connetion string";
                conn.Open();
                query = "SELECT ID from Products";
                cmd = new OleDbCommand(query,conn);

                reader = cmd.ExecuteReader();
                 //Int16 a=(int)cmd.ExecuteScalar();

                List<Int32> id = new List<Int32>();

                 while (reader.Read())
                 {

                     id.Add(reader.GetInt32(0));
                 }

                conn.Close();
                return id;

Answer 2

You have declared the array to be of size 1:

Int16[] id = { 0 };

So when you try to go to a place on the array that doesn't exist it blows up:

id[counter] (unless counter = 0 your in trouble)

Instead try using a List:

List id = new List();

Here is some quick code that should be pretty close:

        conn = new OleDbConnection();
        conn.ConnectionString = "myconnectionstring";
        conn.Open();
        query = "SELECT * from Products";
        cmd = new OleDbCommand(query, conn);

        reader = cmd.ExecuteReader();
        //Int16 a=(int)cmd.ExecuteScalar();

        List<Int16> id = new List<Int16>();

        int counter = 0;
        while (reader.Read())
        {
            counter++;
            id.Add(reader.GetInt16(0));
        }
        foreach (Int16 a in id)
        {
            Console.WriteLine(a);
        }
        conn.Close();
        return id.ToArray();

Answer 3

Your array id is length 1. You should rewrite it a bit to make things more dynamic.

Try using a List instead of the array. That would make things a lot cleaner.

Answer 4

Your id array is only 1 element long. Use a List instead.

http://msdn.microsoft.com/en-us/library/6sh2ey19(v=vs.110).aspx

List<int16> id = new List<int16>();

In your while loop:

id.Add(reader.GetInt16(0));