Set two datasource for one datagridview ASP.NET C#

Question

I want to use two data sources to my single datagridview a

<asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound">
    <Columns>
        <asp:BoundField DataField="BasSaat" HeaderText="BasSaat" />
        <asp:BoundField DataField="Ad" HeaderText="Ad" />
    </Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:RemoteConnect %>" SelectCommand="SELECT [Name] FROM [Table1]"></asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RemoteConnect %>" SelectCommand="SELECT [Country] FROM [Table2]"></asp:SqlDataSource>`

and this is my code behind:

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            // Set the value of the first BoundField to use SqlDataSource1
            e.Row.Cells[0].Text = ((DataRowView)e.Row.DataItem)["Name"].ToString();

            // Set the value of the second BoundField to use SqlDataSource2
            e.Row.Cells[1].Text = ((DataRowView)e.Row.DataItem)["Country"].ToString();
        }
    }

But my grid view is empty. What should I do to have two different data sources?

Please don't tell me that I should use a stored procedure and use inner join or something.

My problem is different and a prefer to ask it simply like this

It is not possible at all to use one data source

And I have to choose my problem in Visual Studio not in SQL Server.

Thank you

Answer 1

You have a couple of things going on here, that are off.

First, you can only have one datasource associated with a datagrid. That's a hard requirement.

Second, you have to tell the DataGrid about the DataSource in order for the binding to behave. I typically use code on the code behind to do this, so I don't have to work with DataSource objects on the ASPX side, but that's outside the scope of the question. To get the DataGird to see the DataSource you have to set its DataSourceId property.

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
<Columns>
    <asp:BoundField DataField="Name" HeaderText="Name" />
    <asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>

Third, because you're using the BoundFields, you do not need the code behind function trying to set the values. The DataField attribute handles that. So you can drop the reference to the RowDataBound event handler (which I did in the code above)

Finally, you have to figure out how to join your two datasets into a single query. There should be a relationship between the [Table1] and [Table2] to know how they to one another. Since I don't know that, I used a CROSS JOIN (which joins every Name from Table1 to every Country in Table2) to create a single dataset in the below DataSource, which also is the implication of what you're wanting to see with the two separate DataSource objects.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RemoteConnect %>" SelectCommand="SELECT t1.[Name], t2.[Country] FROM [Table1] t1 CROSS JOIN [Table2] t2"></asp:SqlDataSource>