Home » C# ProgrammingRSS

GridView

Dear All,

 

I have a typical problem. Please consider the following tables:

 

mtblEoI

EoINo                  ProjectTitle                      FundingAgency

E01                      ABC                                 X1
E02                      DEF                                 M1
E03                      GHI                                 P1

 

mtblProposal

ProposalNo         ProjectTitle                      FundingAgency

P01                      123                                 X1
P02                      435                                 D2
P03                      XXX                                 X1

I have a mastertable for FundingAgency called mtblFUndingAgency

FundAgency

X1
M1
P1
D2

 

Ok the problematic part. I have a dropdown List wherein i will bind all the FundingAgency from the mtblFundingAgency. The user would select a FundingAgency from the list and the corresponding EoI /Proposal information would be bound to a GridView, something like this...

GridView

EoINo / Proposalni                  ProjectTitle                      FundingAgency

EoI
E01                                         ABC                                 X1

Proposal

P01                                        123                                 X1
P03                                        XXX                                 X1

Mind you, the funding agency is not required to be in both the tables (Eoi and Proposal), it mite there in one and not necessary be there in the other table (M1, P1 in the Eoi table is not there in the Proposal table, likewise D2 which is there in Proposal table is not there in the EoI table). Depending on the choice of FundingAgency, the details should be bound in the Grid

Would appreciate if logic cud be explained.

this can be done with the use of 2 Grids, but was wondering if the same could be done with the use of 1 Grid.

Rgds / Sajit

 

 

 

21 Answers Found

 

Answer 1

to achieve this functionlaity in one grid you need to add some codebehind logic

1:fetch teh data from both the table

2:create on resultant datatable which contain both the tables

add the rows in the resultant table .before adding rows from the select  tables.please check the if records are present  or not .

if record present then add row in the table ie.the (EoI or proposal  ) .

bind the final resultant datatable to grid view.

 

Note .you can manipulate this logic on the either of the select table also

 

Answer 2

do you can to show the heading of  tablename EoI and Proposal in Gridview . If not then you can use like

get the both Tables value and stored single dataset

and bind  your Gridview with same dataset.

you can use like as well

sqlQuery= "select * from EoI where FundingAgency ='"+ ddl.SelectedValue +"'; select  * from Proposal where FundingAgency ='"+ ddl.SelectedValue +"'"

and use command object and return dataset from this query.

 

 

Answer 3

Dear Ramesh,

 

Thanks for the reply. can u help me in writin the code to bind  the GridView

 

sajit  

 

Answer 4

I would want the headings also....

 

sajit  

 

Answer 5

 Dear Ramesh,

For this to happen I think i need to write a stored procedure. How do i exec a SP from a web form. Please fwd some links

 

sajit

 

Answer 6

Guys,

 

In continuation to the problem  stated above, this is the progress i have made.. I have made a Stored Procedure called  test. The SP takes a parameter called FAgency.

USE [pmscomp]
GO
/****** Object:  StoredProcedure [dbo].[test]    Script Date: 08/27/2010 16:08:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[test] (@FAgency varchar (1000))
AS
DEclare @N int, @intFlag INT, @Tno varchar (max), @EOIno varchar (max), @Ptitle varchar (max), @FundAgency varchar (max), @PropNo varchar (max)
create table tempjj1 ( TNo VARCHAR (1000), Ptitle VARCHAR (1000), FundAgency VARCHAR (1000))
INSERT INTO tempjj1 (TNo, Ptitle, FundAgency) 
select Eoino As Tno, ProjectTitle, FundingAgency from mtblEoi where FundingAgency = @FAgency and ExternalStatus = 'Awaiting Response' union select  Proposalno As Tno, ProjectTitle, FundingAgency from mtblProposal where FundingAgency = @FAgency and ExternalStatus = 'Awaiting Response';
SELECT @N=COUNT(*) FROM tempjj1
SET @intFlag = 1
WHILE (@intFlag <=@N)
BEGIN
Select @Tno=TNo,@Ptitle = Ptitle, @FundAgency = FundAgency
From (select Tno, Ptitle, FundAgency, (Row_Number() Over (Order By TNo)) As Rownum From tempjj1) P
Where P.Rownum =@intFlag
SET @intFlag = @intFlag + 1
end 
drop table tempjj1
 
In the aspx form, I have a drop down list  called ddlFundingAgency where i bind  the list of FundingAgency. the dropdown  list is bounded on Form load. The code is appended below: 
protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind_FundingAgency();
            
        }
    }


protected void Bind_FundingAgency()
    {
        con.Open();
        string StrSql = "select 0 as Sno, '- Select-' as FundingAgency from mtblFundingAgency union select Sno, FundingAgency from mtblFundingAgency";
        SqlCommand com = new SqlCommand(StrSql, con);

        SqlDataAdapter da = new SqlDataAdapter(com);
        DataTable dt = new DataTable();
        da.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            ddlFundAgency.DataSource = dt;
            ddlFundAgency.DataBind();
        }
        com.Connection.Close();
    }
Depending on the choice of FundingAgency selected by the user, the ddlfundAgency.selectedItem.Text is passed to the SP and accordingly the GridView1 is bound. The problem is the GridView is not binding records. It seems either there is a problem where i am calling the SP. the code is appended below: 
 
protected void Bind_grid()
    {

        SqlDataReader rdr = null;

        SqlCommand cmd = new SqlCommand("test", con);
        cmd.Parameters.Add("@FAgency", SqlDbType.NVarChar, 1000).Value = ddlFundAgency.SelectedItem.Text;
        cmd.CommandType = CommandType.StoredProcedure;
        

        con.Open();
       // cmd.ExecuteNonQuery();
       rdr= cmd.ExecuteReader();

        //while (rdr.Read())
        //{
     
            
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            WebMsgBox.Show("sp executed");
            if (dt.Rows.Count > 0)
            {
               
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
            else
            {
                Response.Write("sorry");
            }
        //}

            con.Close();
       
        
    }
    protected void ddlFundAgency_SelectedIndexChanged(object sender, EventArgs e)
    {
        
       Bind_grid();
    }
AsPX.code
<head runat="server">
    <title>Untitled Page</title>
</head>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form id="form1" runat="server">  
    <div>  
        <table border="0" cellpadding="0" cellspacing="0">
            <tr>
                <td style="width: 100px; height: 19px">
                </td>
                <td style="width: 100px; height: 19px">
                </td>
                <td style="width: 100px; height: 19px">
                </td>
            </tr>
            <tr>
                <td style="width: 100px">
                    <asp:DropDownList ID="ddlFundAgency" runat="server" AutoPostBack="True" DataTextField="FundingAgency"
                        DataValueField="Sno" OnSelectedIndexChanged="ddlFundAgency_SelectedIndexChanged">
                    </asp:DropDownList></td>
                <td style="width: 100px">
                </td>
                <td style="width: 100px">
                </td>
            </tr>
            <tr>
                <td style="width: 100px">
                </td>
                <td style="width: 100px">
                </td>
                <td style="width: 100px">
                </td>
            </tr>
            <tr>
                <td colspan="3">
                    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
                        <Columns>
                            <asp:BoundField DataField="Tno" HeaderText="Tno" />
                            <asp:BoundField DataField="ProjectTitle" HeaderText="Title" />
                            <asp:BoundField DataField="FundAgency" HeaderText="Agency" />
                        </Columns>
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td style="width: 100px">
                </td>
                <td style="width: 100px">
                </td>
                <td style="width: 100px">
                </td>
            </tr>
            <tr>
                <td style="width: 100px">
                </td>
                <td style="width: 100px">
                </td>
                <td style="width: 100px">
                </td>
            </tr>
        </table>
    </div>  
    </form>  
</body>




 

 

Answer 7

Dear Friends,

 

can somebody take a call on this.....this is urgent.....

 

Sajit 

 

Answer 8

Guys, can somebody help me on this....this is urgent....SOS 

 

Answer 9

Hi,

What result you are getting from your stored procedure??

 

Answer 10

You can achieve your requirement like this

<asp:GridView ID="grdv1" runat="server" AutoGenerateColumns="false"
                                                    EmptyDataText="No records found">

<Columns>
            <asp:TemplateField>
                <ItemTemplate>
                    <table width="100%">
                        <tr>
                            <td>
                                <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
                            </td>
                        </tr>
                        <tr>
                            <td>
                                <asp:GridView ID="grdv2" runat="server">
                                    <Columns>
                                        <asp:BoundField DataField="Tno" HeaderText="Tno" />  
                                        <asp:BoundField DataField="ProjectTitle" HeaderText="Title" />  
                                        <asp:BoundField DataField="FundAgency" HeaderText="Agency" />
                                    </Columns>
                                </asp:GridView>
                            </td>
                        </tr>
                    </table>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>

</asp:GridView>


Now, binding this grid

public void bindGridView()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("ID"));
            dt.Columns.Add(new DataColumn("Name"));

            DataRow dr = dt.NewRow();
            dr["ID"] = 1;
            dr["Name"] = "EOI";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["ID"] = 2;
            dr["Name"] = "Proposal";
            dt.Rows.Add(dr);

            grdv1.DataSource = dt;
            grdv1.DataBind();

        }


Then in RowDataBoundEvent 

if (e.Row.RowType == DataControlRowType.DataRow)
            {
                GridViewRow gvRow = e.Row;
                
                GridView grdv2= (GridView)e.Row.FindControl("grdv2");

                Label lblName = (Label) gvRow.FindControl("lblName");

                if (lblName .Text == "EOI")
                {
                    // Get Data of  EOI as per DropDown Selection
                    // and then bind  it to grdv2
                    grdv2.DataSource = dt;
                    grdv.DataBind();
                }
                else if (lblName.Text == "Proposal")
                {
                   // Get Data of  Proposal as per DropDown Selection
                    // and then bind it to grdv2
                    grdv2.DataSource = dt;
                    grdv.DataBind();
                }
            }


I think this logic will work for you...


 

Answer 11

 Dear Nirali,

It seems that u are not using a Stored Procedure. In my stored procedure, i am doing a Union of MtblEoi and MtblProposal based on the fundingAgency selected by the user. So depending where the FundingAgency is available in Eoi or proposal  table, the record has to be fetched. I dont have an option to fetch for Eoi or Proposal. I want to fetch the records for both Eoi and Proposal.

 

Hope i am able to explain to u my requirement,

 

sajit

 

Answer 12

ya that i understood from your stored procedure. But cant you use two seperate stored procedure to fetch EOI data initially and for the next row fetch data from second stored proc for Proposal.

 

Answer 13

Can we bind  the data fetched from EoI and Proposal table to the GridView at the same time? If yes, then wud be gr8 if u cud guide me. But wat u wrote in the RowDataBoundEvent of the GridView

 

if (e.Row.RowType == DataControlRowType.DataRow)
            {
                GridViewRow gvRow = e.Row;
                
                GridView grdv2= (GridView)e.Row.FindControl("grdv2");

                Label lblName = (Label) gvRow.FindControl("lblName");

                if (lblName .Text == "EOI")
                {
                    // Get Data of  EOI as per DropDown Selection
                    // and then bind it to grdv2
                    grdv2.DataSource = dt;
                    grdv.DataBind();
                }
                else if (lblName.Text == "Proposal")
                {
                   // Get Data of  Proposal as per DropDown Selection
                    // and then bind it to grdv2
                    grdv2.DataSource = dt;
                    grdv.DataBind();
                }
            }
 
with wat u wrote (pls see the highlighted code), this will execute either for EoI or Proposal. 
Can we do something which can fetch data from 2 SPs and bind the data to the GridView at the same time. 
Sajit 


 

 

My situation  

 

Answer 14

No, you dont understand my code it will bind  both EOI and Proposal at same time in one grid view.

What my logic is...

public void bindGridView()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("ID"));
            dt.Columns.Add(new DataColumn("Name"));

            DataRow dr = dt.NewRow();
            dr["ID"] = 1;
            dr["Name"] = "EOI";
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["ID"] = 2;
            dr["Name"] = "Proposal";
            dt.Rows.Add(dr);

            grdv1.DataSource = dt;
            grdv1.DataBind();

        }



I am binding the dataSet to grdv1 is having 2 rows 

1) for EOI and

2) row is for Proposal

now in row databound i have just written condition if for just checking if first row then got data for EOI and if second row is binding then got data from Proposal...

So finally it will bound  2 rows. one for EOI and another for Proposal. That if condition is for that purpose only.

You getting my point...

Or first you just try my code(which i have uploaded in previous post for testing only) it will work for sure. Because i have also done this similar thing in my project.

 

Answer 15

Dear Nirali,

How are u fetching the records...by stored Procedure or thru other means...?? 

 

Answer 16

Through storedProcedure only.. In rowdatabound event of grdv1 

if (e.Row.RowType == DataControlRowType.DataRow)
            {
                GridViewRow gvRow = e.Row;
                
                GridView grdv2= (GridView)e.Row.FindControl("grdv2");

                Label lblName = (Label) gvRow.FindControl("lblName");

               <strong> if (lblName .Text == "EOI")</strong>
                {
                    // Get Data of  EOI as per DropDown Selection
                    // and then bind  it to grdv2

                    conn = new SqlConnection(comman.GetDatabase);
                    cmd = new SqlCommand("Get_EOI_Data", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    // Set parameters of stored proc if any
                    adp = new SqlDataAdapter(cmd);
                    adp.Fill(dt);

                    grdv2.DataSource = dt;
                    grdv.DataBind();
                }
                <strong>else if (lblName.Text == "Proposal")</strong>
                {
                   // Get Data of  Proposal as per DropDown Selection
                    // and then bind it to grdv2

                    conn = new SqlConnection(comman.GetDatabase);
                    cmd = new SqlCommand("Get_Proposal_Data", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                     // Set parameters of stored proc if any
                    adp = new SqlDataAdapter(cmd);
                    adp.Fill(dt);

                    grdv2.DataSource = dt;
                    grdv.DataBind();
                }
            }


have you tried my code???

You getting any kind of error??? Or still you are confused with the logic of if else in rowdatabound event???

 

Answer 17

Dear Nirali,

Can i also have the code for the SPs u are writing...In my SP, i have transfered the data to a temp table. Are u also doing something like that...would be gr8 if you cud share the code.

 

sajit

 

Answer 18

Hmm...

No i am not using any temp table for that.

and I think as per your data structure you can directly get the result through simple join. no need for temp table.

1) In get_EOI_Data SP , query can be like

select EOINo as No,ProjectTitle as Title,FundingAgency from mtblEoI where FundingAgency = 'X1'

2) In get_Proposal_Data SP , query can be like

select ProposalNo as No,ProjectTitle as Title,FundingAgency from mtblProposal where FundingAgency = 'X1'

This will return datatable having three columns 1) No 2) Title and 3) FundingAgency

which are the boundfield of grdv2


 

Answer 19

 Dear Nirali,

 

thanks it is working. But there is an issue. It seems there are 2 grids wich is binding. Also just to confirm, the grid that is there in the form is named grdv1. In the rowDataBound event of grdv1 u are writing the follwing code: Is the second grid being bounded because of the lines highlighted: Please explain this section also.

GridView grdv2= (GridView)e.Row.FindControl("grdv2");

                Label lblName = (Label) gvRow.FindControl("lblName");

             if (lblName .Text == "EOI")
                {
                    // Get Data of  EOI as per DropDown Selection
                    // and then bind  it to grdv2

//                    conn = new SqlConnection(comman.GetDatabase);
                    SqlCommand cmd = new SqlCommand("Get_EOI_Data", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    // Set parameters of stored proc if any
                    cmd.Parameters.Add("@FAgency", SqlDbType.NVarChar, 1000).Value = "UNDP";
                 SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    adp.Fill(dt);

                    grdv2.DataSource = dt;
                    grdv.DataBind();
                }
             else if (lblName.Text == "Proposal")
                {
                   // Get Data of  Proposal as per DropDown Selection
                    // and then bind it to grdv2

                    //conn = new SqlConnection(comman.GetDatabase);
                    SqlCommand cmd = new SqlCommand("Get_Proposal_Data", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                     // Set parameters of stored proc if any
                    cmd.Parameters.Add("@FAgency", SqlDbType.NVarChar, 1000).Value = "UNDP";   
                 SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    adp.Fill(dt);

                    grdv2.DataSource = dt;
                    grdv.DataBind();
                }


 

 

Answer 20

Hi,

Its a concept of nested grid. and yes second child grid (nested grid) grdv2 is binding there only which you have made strong. The nested grid is being bound  in each rowdatabound event.

I hope its clear now.

 

Answer 21

Thanks Nirali,

Thanks for the explaination. Ok one more query....i have added a item template field (a command button) which will be clicked to change the status of the Eoi or Proposal.

 - Depending on wat EOI or Proposal the user  chooses, the EOI table needs to be updated. How do i fetch the Eoi no.  or the Proposal no so that i can update the respective table.

- how the application recognize whether the row selected is an Eoi or Proposal. 

 

How do i go abt this....

 

sajit

 
 
Search for  GridView
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter