asked on

Binding a datareader to a gridview

Hi Experts,

Does anyone know why the gridview isn't populated.

please see code.

thanks

OracleConnection con = new OracleConnection();
        con = new OracleConnection("Data Source=***; User ID=***;Password=****");
        OracleCommand cmd = new OracleCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;

        cmd.CommandText = "HE_GETGRADES";
        cmd.Parameters.Add("grades_cursor", OracleType.Cursor);
        cmd.Parameters["grades_cursor"].Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add("vp_id", OracleType.Number).Value = "15000028435389";

        con.Open();

        OracleDataReader reader;
        bool haveRows = false;

        reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine("=========================");
            Console.WriteLine("Column1 has: " + (Convert.IsDBNull(reader.GetValue(0)) ? "null" : reader.GetString(0).ToString()));
            Console.WriteLine("Column2 has: " + (Convert.IsDBNull(reader.GetValue(1)) ? "null" : reader.GetString(1).ToString()));
            Console.WriteLine("Column3 has: " + (Convert.IsDBNull(reader.GetValue(2)) ? "null" : reader.GetDecimal(2).ToString()));
            Console.WriteLine("Column4 has: " + (Convert.IsDBNull(reader.GetValue(3)) ? "null" : reader.GetDecimal(3).ToString()));
            //Console.WriteLine("Column5 has: " + (Convert.IsDBNull(reader.GetValue(4)) ? "null" : reader.GetOracleDecimal(4).ToString()));
            // Console.WriteLine("Column6 has: " + (Convert.IsDBNull(reader.GetValue(5)) ? "null" : reader.GetOracleDecimal(5).ToString()));
            Console.WriteLine("Column7 has: " + (Convert.IsDBNull(reader.GetValue(6)) ? "null" : reader.GetString(6).ToString()));
            Console.WriteLine("Column8 has: " + (Convert.IsDBNull(reader.GetValue(7)) ? "null" : reader.GetString(7).ToString()));
            Console.WriteLine("Column9 has: " + (Convert.IsDBNull(reader.GetValue(8)) ? "null" : reader.GetString(8).ToString()));
            Console.WriteLine("Column10 has: " + (Convert.IsDBNull(reader.GetValue(9)) ? "null" : reader.GetDecimal(9).ToString()));
            Console.WriteLine("Column11 has: " + (Convert.IsDBNull(reader.GetValue(10)) ? "null" : reader.GetDateTime(10).ToString()));
            Console.WriteLine("Column12 has: " + (Convert.IsDBNull(reader.GetValue(11)) ? "null" : reader.GetDecimal(11).ToString()));
            Console.WriteLine("Column13 has: " + (Convert.IsDBNull(reader.GetValue(12)) ? "null" : reader.GetDecimal(12).ToString()));
            Console.WriteLine("Column14 has: " + (Convert.IsDBNull(reader.GetValue(13)) ? "null" : reader.GetDecimal(13).ToString()));
            Console.WriteLine("Column15 has: " + (Convert.IsDBNull(reader.GetValue(14)) ? "null" : reader.GetString(14).ToString()));
            Console.WriteLine("Column16 has: " + (Convert.IsDBNull(reader.GetValue(15)) ? "null" : reader.GetString(15).ToString()));
            haveRows = true;
        }
        {
            reader.NextResult();
            //Console.ReadLine();

              GridView1.DataSource = reader;
              GridView1.DataBind();
        }


        if (!haveRows)
        {
            Console.WriteLine("No Rows Found.");
        }

        con.Close();
        con.Dispose();
    }

Open in new window

ASKER CERTIFIED SOLUTION

Carl Tawn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SirReadAlot

ASKER

>>Is this the case or are you trying to bind to the first set?

yes

>>The DataReader is forward-only, so once you have read through it you can't read through it again in order to bind.

so use a dataset??

Carl Tawn

That depends. Are you writing everything out to a console window purely for debug information? If so then you can simply take that code out and bind to the reader as normal.

But, if you do need to access the data multiple times, then a DataSet/DataTable is the way to go.

SirReadAlot

ASKER

sorry...

trying to bind all retrieved data to a gridview

SirReadAlot

ASKER

I first tried to see if i could retrieve data so i used a console app.

forgot to get rid of the evidence.

>>But, if you do need to access the data multiple times, then a DataSet/DataTable is the way to go.

do you have code examples?

Vitor Silva

You must first fill the reader variable with the values and only after finishing the retrieve of values, you do the binding. Neither way you should be getting at least the first line appearing in the gridview.

SirReadAlot

ASKER

Hi I have managed to fill the grid.

would this be the best way??

OracleConnection con = new OracleConnection();
        con = new OracleConnection("Data Source=**; User ID=***;Password=****");
        OracleCommand cmd = new OracleCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;

        cmd.CommandText = "HE_GETGRADES";
        cmd.Parameters.Add("grades_cursor", OracleType.Cursor);
        cmd.Parameters["grades_cursor"].Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add("vp_id", OracleType.Number).Value = "15000028435389";

        con.Open();

        OracleDataReader reader;
        bool haveRows = false;

        reader = cmd.ExecuteReader();


        DataTable dt = new DataTable();
        dt.Load(reader);
        GridView1.DataSource = dt;
        GridView1.DataBind();
        haveRows = true;


        if (!haveRows)
        {
            Console.WriteLine("No Rows Found.");
        }

Open in new window

Carl Tawn

Yep, that's fine. Pluse populating a DataTable from a reader is slightly quicker than using a DataAdapter :)

SirReadAlot

ASKER

cool