Datareader only works once

Question

1.00/5 (1 vote)

See more:

Error a DataReader already is open. Close the reader.
DataReader works fine first time, but not the second time
Tried closing the dr but same error

I want to refine the search based on the first and subsequent searches

What I have tried:

public static void readSqlData(String sqlCommand)
{
cmd.CommandText = sqlCommand;
dr.Close();
dr = cmd.ExecuteReader();

if (dr.Read())
{

Main Window displays a DataViewGrid (The Calling Source)

string selStr = "select piID, piDescription, piDetails, piStarted, " +
   "piCompleted, Customer, Mobile, Email, Town, Organiser, " +
   "Designer, ptProjectType, ptDetailProjectType " +
   "from v_ProjectCard where " +
   "piCompleted = convert(nVarChar(20), '1900-01-01', 112);\r\n";

db.readSqlData(selStr);

Posted 11-Jun-24 19:01pm

ejm Coder

Add a Solution

Comments

ejm Coder 13-Jun-24 6:37am

Thanks I understand and it is the advice I needed.

2 solutions

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)

OriginalGriff · Accepted Answer · 2024-06-11T20:00:00

Your ordign is wrong, and you are trying to reuse the same connection across multiple readers.
If you open a DataReader on a DataConnection, you cannot reuse the same connection object until the reader has been closed because teh SQL Engine will reject the request as it doesn't "know" that you are finished with the previous action.

For that reason (and a few others) it is a very good idea to use using blocks around all DB activities to ensure that itmes are clodes and dis0posed when you are finished with them:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT Age, Description FROM myTable WHERE ID = @ID", con))
        {
        cmd.Parameters.AddWithValue("@ID", myTextBox.Text);
        using (SqlDataReader reader = cmd.ExecuteReader())
            {
            while (reader.Read())
                {
                int age = (int) reader["Age"];
                string desc = (string) reader["Description"];
                Console.WriteLine($"{age}\n{desc}");
                }
            }
        }
    }

In this case, I'd suggest using a DataAdapter instead of a DataReader:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlDataAdapter da = new SqlDataAdapter("SELECT MyColumn1, MyColumn2 FROM myTable WHERE mySearchColumn = @SEARCH", con))
        {
        da.SelectCommand.Parameters.AddWithValue("@SEARCH", myTextBox.Text);
        DataTable dt = new DataTable();
        da.Fill(dt);
        myDataGridView.DataSource = dt;
        }
    }

User 15590104 · Accepted Answer · 2024-06-11T19:48:00

you use the .close function too early , where is the logical ?
by closing the datareader you'll have to re-open it.

Récupération de données à l'aide d'un DataReader - ADO.NET | Microsoft Learn[^]

C#

// here is a sample from Microsoft : 

static void HasRows(SqlConnection connection)
{
    using (connection)
    {
        SqlCommand command = new(
          "SELECT CategoryID, CategoryName FROM Categories;",
          connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
                    reader.GetString(1));
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

Datareader only works once

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0