asked on

Programmatically created GridView is not binding the data to the grid. Why?

What am I missing from this code snippet? I have ran my select statement in SQL and it returns the correct rows but here the gridview is not returning any rows.

The passed select statement
Session["selectStatement"] = "SELECT Degree.Degree_Program, Degree.Degree_ExpDegree, t.maxInst, t.maxDeg, t.Student_ID, Degree.Degree_ID, Institute.Institute_Name, "+
                                                    "Student.Student_FullName, Bond.Bond_StartDate, Bond.Bond_EndDate, Bond.Bond_Duration, Officer.Officer_Name, "+
                                                    "ApplicationStudeyLeave.Application_CostPerYr, Comment.Comment_TOComments "+
                                         " FROM         (SELECT     MAX(Institute.Institute_ID) AS maxInst, MAX(Degree.Degree_ID) AS maxDeg, MAX(Bond.Bond_ID) AS maxBond, Student.Student_ID,  "+
                                                                 "  MAX(ApplicationStudeyLeave.Application_ID) AS maxApp, MAX(Comment.Comment_ID) AS maxCom "+
                                                        "FROM          Student INNER JOIN "+
                                                                      "Degree ON Student.Student_ID = Degree.Student_ID INNER JOIN "+
                                                                      "Institute ON Student.Student_ID = Institute.Student_ID INNER JOIN "+
                                                                      "Bond ON Student.Student_ID = Bond.Student_ID INNER JOIN "+
                                                                      "ApplicationStudeyLeave ON Student.Student_ID = ApplicationStudeyLeave.Student_ID LEFT OUTER JOIN "+
                                                                      "Comment ON Student.Student_ID = Comment.Student_ID LEFT OUTER JOIN "+
                                                                      "Officer ON ApplicationStudeyLeave.Officer_ID = Officer.Officer_ID AND Comment.Officer_ID = Officer.Officer_ID "+
                                          "GROUP BY Student.Student_ID) t INNER JOIN "+
                                                  "Degree ON t.Student_ID = Degree.Student_ID AND "+
                                                  "t.maxDeg = Degree.Degree_ID INNER JOIN "+
                                                  "Institute ON t.Student_ID = Institute.Student_ID AND "+
                                                  "t.maxInst = Institute.Institute_ID INNER JOIN "+
                                                  "Student  ON Institute.Student_ID = Student.Student_ID INNER JOIN "+
                                                  "Bond  ON Student.Student_ID = Bond.Student_ID AND "+
                                                  "t.maxBond = Bond.Bond_ID LEFT OUTER JOIN "+
                                                  "ApplicationStudeyLeave ON t.Student_ID = ApplicationStudeyLeave.Student_ID AND  "+
                                                  "t.maxApp = ApplicationStudeyLeave.Application_ID LEFT OUTER JOIN "+
                                                  "Comment ON t.Student_ID = Comment.Student_ID AND t.maxCom = Comment.Comment_ID LEFT OUTER JOIN "+
                                                  "Officer ON ApplicationStudeyLeave.Officer_ID = Officer.Officer_ID AND Comment.Officer_ID = Officer.Officer_ID "+
                                          "WHERE (YEAR(Degree.Degree_ExpGradDate) >=  '2007') AND (YEAR(Degree.Degree_RetDate) >=  '2007') ";
 
 
 
string passedCountStatement, passedStatement;
  
    protected void Page_Load(object sender, EventArgs e)
    {
        passedCountStatement = Session["selectCountStatement"].ToString();
        passedStatement = Session["selectStatement"].ToString();
        Page.MaintainScrollPositionOnPostBack = true;
        
        if (!Page.IsPostBack)
        {
            //Connection String
            SqlConnection con = new SqlConnection(connection);
 
 
            //Use the passed select statement
            SqlCommand total = new SqlCommand(passedCountStatement, con);
                        con.Open();
 
            //Create the datasource
            SqlDataSource ds = new SqlDataSource(connection, passedStatement);
 
            gvStudentInfo.DataSource = ds;
            gvStudentInfo.DataBind();
          
            gvStudentInfo.AutoGenerateColumns = true;
            Label1.Text =gvStudentInfo.Columns.Count.ToString();
            int number = int.Parse(total.ExecuteScalar().ToString());
            
            con.Close();
        }
    }

Open in new window

chinu1310

Have your tried this.

gvStudentInfo.DataSource = ds.Tables[0];

Hope it helps.

VibertH

ASKER

'System.Web.UI.WebControls.SqlDataSource' does not contain a definition for 'Tables'

chinu1310

Have you put a break point where your dataset is getting filled by adapter. Is it giving the result in dataset ?
Let me know if you don't have idea how to do this.

Thanks
CK

chinu1310

Looks like you have not used Adapter to fill dataset. What you need to do is create and adapter and pass your select command to the adapter and than use adapter's fill method to fill dataset. finally you can bind this dataset to gridview.

Thanks

VibertH

ASKER

Still returning 0 rows

VibertH

ASKER

Still returning 0 rows

        if (!Page.IsPostBack)
        {
            //Connection String
            SqlConnection con = new SqlConnection(connection);
 
 
            //Use the passed select statement
            SqlCommand total = new SqlCommand(passedCountStatement, con);
            //SqlCommand rows = new SqlCommand(passedStatement,con);
            con.Open();
 
            //Create the datasource
            //SqlDataSource ds = new SqlDataSource(connection, passedStatement);
 
            // You can cache the DataTable for improving performance
 
            DataTable dt = GetData().Tables[0];
 
            DataView dv = new DataView(dt);
 
            gvStudentInfo.DataSource = dv;
            gvStudentInfo.DataBind();
 
            gvStudentInfo.AutoGenerateColumns = true;
            Label1.Text =gvStudentInfo.Rows.Count.ToString();
            int number = int.Parse(total.ExecuteScalar().ToString());
            //Label1.Text = number.ToString(); 
            con.Close();
        }
 
 
    private DataSet GetData()
    {
 
        SqlConnection con = new SqlConnection(connection);
 
        SqlDataAdapter daPassedStatement = new SqlDataAdapter(passedStatement, connection);
 
        DataSet dsPassedStatement = new DataSet();
 
        daPassedStatement.Fill(dsPassedStatement);
 
        return dsPassedStatement;
 
    }

Open in new window

chinu1310

Your function is returning DataSet right. Create a new dataset to hold dataset resulting from your GetData() function. And give it as a datasource to your grid.

Also make sure that your dataset is filled when your GetData() is return it back. I mean put a break point and check it.

Something like this.
DataSet ds = new DataSet();
ds = GetData();
gvStudentInfo.DataSource = ds;
gvStudentInfo.DataBind();

Thanks

chinu1310

Look this is the basic code for database operation.

string _query = "Select * from tbl";
SqlConnection _conn = new SqlConnection(_strConnection);
SqlDataAdapter _dap = new SqlDataAdapter(_query, _conn);
DataSet _ds = new DataSet();
_conn.Open();
_dap.Fill(_ds);
_conn.Close();

//Thats it. you have your data in your dataset.now you can bind it to anything. In your case bind it to gridview. You out debug point at this line [ _conn.Close(); ] Check the _ds which should be having data in that.

VibertH

ASKER

I have looked at this code over and over and cannot find a thing wrong with it.

When I step through it, daPassedStatement.Fill(dsPassedStatement) has 14 columns and one row returned, which is correct. But Label1.Text =gvStudentInfo.Rows.Count.ToString() returns zero rows. This leads me to think that the data is not binding to the gridview.

        if (!Page.IsPostBack)
        {
            //Connection String
            SqlConnection con = new SqlConnection(connection);
 
 
            //Use the passed select statement
            SqlCommand total = new SqlCommand(passedCountStatement, con);
            //SqlCommand rows = new SqlCommand(passedStatement,con);
            con.Open();
 
            //Create the datasource
            //SqlDataSource ds = new SqlDataSource(connection, passedStatement);
            DataSet ds = new DataSet();
            ds = GetData();
            if (ds.Tables.Count > 0)
             {
            
                
                gvStudentInfo.DataSource =ds;
                gvStudentInfo.DataBind();
 
                gvStudentInfo.AutoGenerateColumns = true;
            }
            else
            {
                Label1.Text = "Unable to connect to the database.";
            }
 
            Label1.Text =gvStudentInfo.Rows.Count.ToString();
            int number = int.Parse(total.ExecuteScalar().ToString());
            //Label1.Text = number.ToString(); 
            con.Close();
        }
 
        private DataSet GetData()
    {
 
        SqlConnection con = new SqlConnection(connection);
 
        SqlDataAdapter daPassedStatement = new SqlDataAdapter(passedStatement, connection);
 
        DataSet dsPassedStatement = new DataSet();
 
        daPassedStatement.Fill(dsPassedStatement);
 
        return dsPassedStatement;
    }

Open in new window

VibertH

ASKER

When I step through the fill, I see there was one row returned but where are the actual fields?

chinu1310

That means your data is not coming into dataset. Can you check by firing simple query first and bind to gridview ?

Thanks

ASKER CERTIFIED SOLUTION

Oliver Amaya

membership

This solution is only available to members.

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

Start Free Trial

VibertH

ASKER

Joex911,

THAT DID IT!!!! THANKS!!!!

chinu1310,

I'd tried your way several times before. My initial post were other attempts. The latter posts were me trying again.

chinu1310

Yeah. Looks like I miss read your code. Anyway happy to see your problem solved.

Regards
CK