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

SirReadAlotAsked:
Who is Participating?
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
Well, you use reader.NextResult() which would suggest you are retrieving multiple result sets from your SP and you are trying to bind to the second set. Is this the case or are you trying to bind to the first set?

If you are trying to bind to the first set then you can't read through all the records manually first. The DataReader is forward-only, so once you have read through it you can't read through it again in order to bind.
0
 
SirReadAlotAuthor Commented:
>>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??
0
 
Carl TawnSystems and Integration DeveloperCommented:
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.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
SirReadAlotAuthor Commented:
sorry...

trying to bind all retrieved data to a gridview
0
 
SirReadAlotAuthor Commented:
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?


0
 
Vitor SilvaInnovation directorCommented:
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.
0
 
SirReadAlotAuthor Commented:
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

0
 
Carl TawnSystems and Integration DeveloperCommented:
Yep, that's fine. Pluse populating a DataTable from a reader is slightly quicker than using a DataAdapter :)
0
 
SirReadAlotAuthor Commented:
cool
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.