Solved

Binding a datareader to a gridview

Posted on 2010-08-25
9
476 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:SirReadAlot
  • 5
  • 3
9 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 33519796
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
 

Author Comment

by:SirReadAlot
ID: 33519821
>>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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33519838
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
 

Author Comment

by:SirReadAlot
ID: 33519839
sorry...

trying to bind all retrieved data to a gridview
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:SirReadAlot
ID: 33519856
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
 
LVL 1

Expert Comment

by:zaping
ID: 33519872
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
 

Author Comment

by:SirReadAlot
ID: 33519894
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33519961
Yep, that's fine. Pluse populating a DataTable from a reader is slightly quicker than using a DataAdapter :)
0
 

Author Comment

by:SirReadAlot
ID: 33520008
cool
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now