2 Gridview error on getting data with the same querry

Question

0.00/5 (No votes)

See more:

hi guys, ive got a problem here. I want to set the data of two gridview where the only difference between them is parameters. my problem is ive got error when i run it.
here is my code

<pre lang="vb">If (IsPostBack = False) Then
    Dim strSQL As String
    Dim strSQL2 As String
    Dim connection As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=ML0003135586;Initial Catalog=TestSQL;Integrated Security=True")
    strSQL = "SELECT [ID], [CourseTitle], [CourseCode], [Objectives], [Duration] FROM [tblTrainingPlan]WHERE ([Category] = @Category)"
    connection.Open()
    Dim myCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand(strSQL, connection)
    myCommand.Parameters.AddWithValue("@Category", lblGEE.Text)
    GridView1.DataSource = myCommand.ExecuteReader()
    GridView1.DataBind()
    myCommand.Dispose()
    strSQL2 = "SELECT [ID], [CourseTitle], [CourseCode], [Objectives], [Duration] FROM [tblTrainingPlan]WHERE ([Category] = @Category)"
    Dim myCommand2 As SqlClient.SqlCommand = New SqlClient.SqlCommand(strSQL2, connection)
    myCommand2.Parameters.AddWithValue("@Category", lblCross.Text)
    TPCross.DataSource = myCommand2.ExecuteReader()
    TPCross.DataBind()
    connection.Close()
    connection.Close()

Ive got an error saying like this one

<br />
<br />
There is already an open DataReader associated with this Command which must be closed first.

and it points out to

VB

TPCross.DataSource = myCommand2.ExecuteReader()

what is the error? do i have a logical error?
Plss help. thanks in advance

Posted 10-May-11 22:10pm

janwel

Add a Solution

2 solutions

Solution 1

Since this the same column you can use union all in this.

make it this way

SQL

strSQL = "SELECT [ID], [CourseTitle], [CourseCode], [Objectives], [Duration] FROM [tblTrainingPlan]WHERE ([Category] = @Category) union all SELECT [ID], [CourseTitle], [CourseCode], [Objectives], [Duration] FROM [tblTrainingPlan]WHERE ([Category] = @Category2) "

XML

<pre lang="vb">connection.Open()
Dim myCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand(strSQL, connection)
myCommand.Parameters.AddWithValue("@Category",lblGEE.Text)
myCommand.Parameters.AddWithValue("@Category2", lblCross.Text)
GridView1.DataSource = myCommand.ExecuteReader()
GridView1.DataBind()
myCommand.Dispose()</pre>

Posted 10-May-11 22:22pm

cuteband

Comments

janwel 11-May-11 4:38am

sir it says here my problem is executereader?

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)

Sandeep Mewara · Accepted Answer · 2011-05-10T22:26:00

Solution 2

Well, it's because DataReaders are connected-mode, forward only thing. As long as a reader is open, connection is active and working on it. Thus if you try to use another reader then, you get the error.

Have a look at these:
MSDN blog describing the same in much more detail[^]
Workaround suggested with implication[^]
Similar discussion[^]

If needed, look here[^] for more.

Posted 10-May-11 22:26pm

Sandeep Mewara

Comments

janwel 11-May-11 4:41am

Sir does this means ive got a syntax error? but the articles you shown has no solution(on my understanding) plss help me ill try to renew my program

Sandeep Mewara 11-May-11 4:43am

Yes, What you did is wrong and surely you did not got the meaning form the article.

You have executed two readers at one time for the same connection which is not allowed by default. One can pass on using a attribute mentioned in the workaround link.

janwel 11-May-11 4:52am

ok sir ill try to read it more fully(excuse my basic undstanding in english, im not used in speaking it)

Sandeep Mewara 11-May-11 5:10am

Thats ok.