Easy problem I hope....

[seidel1]

I am trying to write a routine that reads from 2 files and performs some analysis on the record, than writes the results to a seperate table. This should be very basic, but I am getting an error that there is already an open datareader associated with the command which must be closed.

here's my code

class Program
    {
        static void Main(string[] args)
        {
 
            SqlConnection conn = new SqlConnection("server=JXXX;database =database;user id=sa5; password=XXXX");
            SqlCommand comm = new SqlCommand("select site.siteid,RawDataInput.loc from site,RawDataInput where RawDataInput.CALC_SITEID=site.siteid", conn);
            comm.Connection.Open();
 
            SqlDataReader r = comm.ExecuteReader(CommandBehavior.CloseConnection);
            SqlCommand cmdnew = new SqlCommand("sudhir", conn);
 
            if (r.Read())
            {
                Console.WriteLine(r["siteid"].ToString());
 
                cmdnew.CommandText = "insert into sitecontroldata (siteid,location) values ('" + Function.ToInt(r["siteid"]) + "','" + r["loc"].ToString() + "')";
                cmdnew.ExecuteNonQuery();
 
                for (int i = 0; i <= 10; i++)
                {
                    cmdnew.CommandText = "insert into sitecontroldata (siteid,location) values ('" + Function.ToInt(r["siteid"]) + "','" + r["loc"].ToString() + "')";
                    cmdnew.ExecuteNonQuery();
                    Console.WriteLine("Record inserted" + r["siteid"].ToString());
                }
            }
        }
    }

any ideas?

[jmcilhinney]

Note that the error message would have said that there was a DataReader associated with the connection, not the command. It's very important that you read and report your error messages carefully or you, and thus we, may end up trying to solve the wrong problem. Why not just report exactly what the error message says instead of paraphrasing?

A database connection can only do one thing at a time. When you call ExecuteReader then the associated connection is occupied until you close the DataReader. If you want to execute another command, like calling ExecuteNonQuery, then you'll either have to wait until you've closed the DataReader or else use a different connection. You can open more than one connection to the same database if required, although I'd avoid it unless it is genuinely required.

[seidel1]

Thanks for the info it helps. But I am not paraphrasing... this is the actual error message:

"There is already an open DataReader associated with this Command which must be closed first."

And it is pointing to cmdnew.ExecuteNonQuery();

[jmcilhinney]

If that's true then I offer my humblest apologies but I don't see how it could be. The SqlDataReader was created by calling ExecuteReader on the SqlCommand referred to by the 'comm' variable. You're then calling ExecuteNonQuery on the SqlCommand referred to by the 'cmdnew' variable, which is a completely different SqlCommand object. They do, however, both use the same SqlConnection. Plus, as far as I'm aware, there is no relationship maintained between a DataReader and the Command it was created from. A DataReader has a Connection property but no Command property.

[seidel1]

Thanks again for the help. It is true, that is why I am posting the issue here. I am hoping someone can point me in the right direction.

All I am trying to do is the following

1) read a record from file A
2) find a matching ID in file B
3) Read the matched record from File B
4) Combine the records from A & B and write the combination to C (a different table)
5) loop back to 1 and get record n+1 in A and continue

is my read/write structure correct?

[jmcilhinney]

I would suggest creating the DataReader and reading the result set one record at a time. As you read each record you should add a new row (or rows) to a DataTable. Once you've read all rows you can close the DataReader and use a DataAdapter to insert all the new rows in a batch using the already open connection, which you then close.

[seidel1]

thanks I will try it

[popskie]

You can open only one SqlDataReader in in Connection. Close first all SqlDataReader before using the reader object.

[seidel1]

Thanks again for the help your link is a very cool resource. do you know if it is 100% compatible with ASP2 and SQL05?

[jmcilhinney]

Thanks again for the help your link is a very cool resource. do you know if it is 100% compatible with ASP2 and SQL05?

What link are we talking about?

[seidel1]

Its the link posted by popskie...

http://docs.msdnaa.net/ark_new3.0/c...pplications.htm

Thanks again for the help your link is a very cool resource. do you know if it is 100% compatible with ASP2 and SQL05?

[jmcilhinney]

Its the link posted by popskie...

http://docs.msdnaa.net/ark_new3.0/c...pplications.htm

Thanks again for the help your link is a very cool resource. do you know if it is 100% compatible with ASP2 and SQL05?

That's part of popskie's signature and it is one of seven links. You can see why I didn't know which you meant.