Click here to Skip to main content
15,040,743 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
Hello there. I want to bind tow different sql datasources in one Gridview.
This is the behind c# code:

C#
protected void Page_Load(object sender, EventArgs e)
    {
        //Establishing the MySQL Connection
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BasketballConnectionString1"].ToString());
        string query, query2;
        SqlCommand SqlCommand, SqlCommand2;
        SqlDataReader reader, reader2;

        SqlDataAdapter adapter = new SqlDataAdapter();
        SqlDataAdapter adapter2 = new SqlDataAdapter();
        //Open the connection to db
        conn.Open();

        //Generating the query to fetch the contact details
        query = "SELECT blah1 blah1 blah1";
        query2 = "SELECT blah2 blah2 blah2";
        SqlCommand = new SqlCommand(query, conn);
        adapter.SelectCommand = new SqlCommand(query, conn);
        SqlCommand2 = new SqlCommand(query2, conn);
        adapter.SelectCommand = new SqlCommand(query2, conn);
        //execute the query
        reader = SqlCommand.ExecuteReader();
        reader2 = SqlCommand2.ExecuteReader();
        //Assign the results 
        GridView4.DataSource = reader;
        GridView4.DataSource = reader2;
        //Bind the data
        GridView4.DataBind();
        conn.Close();
        //------------------------------------------------------------
    }

and this is the error that i take:
"There is already an open DataReader associated with this Command which must be closed first."
Is there any suggestion how to use both two sql queries(query,query2)?
Posted
Updated 11-Jan-18 22:06pm

   
Comments
iratus7 23-Nov-13 11:48am
   
this seems that is the best solution for performance
Hi Iratus7,

Since you are pointing to same database, you can use combine two query into single query and fetch it and bind it to the datasource.
It will be easy for you.
Like select B1.blah1,B2.blah2 from blah1 B1,blah2 B2

Use the below code instead of yours

protected void Page_Load(object sender, EventArgs e)
    {
        //Establishing the MySQL Connection
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BasketballConnectionString1"].ToString());
        string query;
        SqlCommand SqlCommand;
        SqlDataReader reader;
 
        SqlDataAdapter adapter = new SqlDataAdapter();
        //SqlDataAdapter adapter2 = new SqlDataAdapter();
        //Open the connection to db
        conn.Open();
 
        //Generating the query to fetch the contact details
        query = "select B1.blah1,B2.blah2 from blah1 B1,blah2 B2";
        //query2 = "SELECT blah2 blah2 blah2";
        SqlCommand = new SqlCommand(query, conn);
        adapter.SelectCommand = new SqlCommand(query, conn);
        //SqlCommand2 = new SqlCommand(query2, conn);
        //adapter.SelectCommand = new SqlCommand(query2, conn);
        //execute the query
        reader = SqlCommand.ExecuteReader();
        //reader2 = SqlCommand2.ExecuteReader();
        //Assign the results 
        GridView4.DataSource = reader;
        //GridView4.DataSource = reader2;
        //Bind the data
        GridView4.DataBind();
        conn.Close();
        //------------------------------------------------------------
    }


Hope this would help you a bit.

Regards,
RK
   
Comments
iratus7 22-Nov-13 7:58am
   
It is a complicated database and there is a problem to combine these two queries
♥…ЯҠ…♥ 22-Nov-13 8:06am
   
its not that complicated as your C# code, try to optimize it. Try it and come back with your query... we'll help you on that too.
iratus7 23-Nov-13 11:47am
   
yes I combine the queries using three new Views in my database.thank you.I dont know if it is a problem with the performance
if its a same column name ,you can use UNION


UNION
   
There is already an open DataReader associated with this Command which must be closed first, for this error you have to insert multipleActiveResultSets=true in your connectionstring.
example:"Data Source=server name;Initial Catalog=database name;User ID=xx;Password=xx;multipleActiveResultSets=true"

hope this will help you
   
Comments
CHill60 12-Jan-18 8:35am
   
The question has not just 1 but two accepted solutions from over 4 years ago. Stick to answering new questions where the OP still needs help.
And if you are going to suggest things like MARS at least inform the OP of the possible consequences ... such as MARS not being thread-safe for example!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900