Combine and Bind two sql queries in one gridview

Question

3.00/5 (2 votes)

See more:

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 22-Nov-13 1:31am

iratus7

Updated 11-Jan-18 22:06pm

Add a Solution

4 solutions

Solution 2

if its a same column name ,you can use UNION

UNION

Posted 22-Nov-13 1:42am

King Fisher

Solution 4

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

Posted 11-Jan-18 22:06pm

Member 13619556

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!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Peter Leow · Accepted Answer · 2013-11-22T02:03:00

Solution 3

This following article should help:

http://blogs.msdn.com/b/spike/archive/2009/08/20/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-closed-first-explained.aspx[^]

Posted 22-Nov-13 2:03am

Peter Leow

Comments

iratus7 23-Nov-13 11:48am

this seems that is the best solution for performance

♥…ЯҠ…♥ · Accepted Answer · 2013-11-22T01:38:00

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