SQL Reader Again

[Saurabh]

Hi,

I am getting the below mentioned error when I try to update a table


Exception: Unable to update Person records Exception : System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.



I have also set checked the time out of the server to unlimited. I am using the SQLDataReader in my applciation at quite a few palces and make it a point to close the object and set it to nothing.


Here is the snippet of the code.

SqlDataReader lobj_PersonTable = SqlHelper.ExecuteReader(GDFConnectionReader,CommandType.Text,lstr_SQL);

while(lobj_PersonTable.Read())
{

}

lobj_CompanyTable.Close();
lobj_CompanyTable = null;

But if I check the SQL server enterprise manager, I find a number of locks/Process Id under current activity. I feel that its these locks that might be preventing the update statement from executing. Am I missing something here?

Any help would be appreciated.

Thanks and Regards,
Saurabh

 

[=?iso-8859-1?Q?Jos=E9_Joye?=]

Looking at your code snippet, I'm not too sure where you are trying to update the table.
Is this within the while loop?
Do you use the same connection object as the one for the DataReader?

If so, here is the problem! Normally a connection currently in use by a dataReader is not available for any other activity...
You have either to use another one or close the dataReader first.

- José
"Saurabh" <[email protected]> a écrit dans le message de [email protected]...
Hi,

I am getting the below mentioned error when I try to update a table


Exception: Unable to update Person records Exception : System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.



I have also set checked the time out of the server to unlimited. I am using the SQLDataReader in my applciation at quite a few palces and make it a point to close the object and set it to nothing.


Here is the snippet of the code.

SqlDataReader lobj_PersonTable = SqlHelper.ExecuteReader(GDFConnectionReader,CommandType.Text,lstr_SQL);

while(lobj_PersonTable.Read())
{

}

lobj_CompanyTable.Close();
lobj_CompanyTable = null;

But if I check the SQL server enterprise manager, I find a number of locks/Process Id under current activity. I feel that its these locks that might be preventing the update statement from executing. Am I missing something here?

Any help would be appreciated.

Thanks and Regards,
Saurabh

 

[Kevin Spencer]

I can't figure out what you mean by "I have also set checked the time out of
the server to unlimited". By "the server" I assume you mean the SQL Server.
But "the time out of the server" means nothing. The SQL Server has no
timeout property. For example, if I have 10 clocks in my house, and I tell
you that I "set the time of my house," which of the 10 clocks did I set?

Can you be more specific?

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist

What You Seek Is What You Get.



Hi,

I am getting the below mentioned error when I try to update a table


Exception: Unable to update Person records Exception :
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.



I have also set checked the time out of the server to unlimited. I am using
the SQLDataReader in my applciation at quite a few palces and make it a
point to close the object and set it to nothing.


Here is the snippet of the code.

SqlDataReader lobj_PersonTable =
SqlHelper.ExecuteReader(GDFConnectionReader,CommandType.Text,lstr_SQL);

while(lobj_PersonTable.Read())
{

}

lobj_CompanyTable.Close();
lobj_CompanyTable = null;

But if I check the SQL server enterprise manager, I find a number of
locks/Process Id under current activity. I feel that its these locks that
might be preventing the update statement from executing. Am I missing
something here?

Any help would be appreciated.

Thanks and Regards,
Saurabh

 

[Saurabh Prabhu]

Hi,

Sorry I did not explain the issue correctly.

Here I am setting the SQLDataReader object to read from a table
SqlDataReader lobj_PersonTable = SqlHelper.ExecuteReader(GDFConnectionReader,CommandType.Text,lstr_SQL);

Now the next line of code is the while loop
while(lobj_PersonTable.Read())
{

}

Now it is withn this loop that I am executing an update statement using Command object. It is this loop that I am encountering a "time out exception" for every record in the loop. Here is the text
Exception: Unable to update Person records Exception : System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Please let me know If you need anything else.

Thanks and Regards,
Saurabh

Looking at your code snippet, I'm not too sure where you are trying to update the table.
Is this within the while loop?
Do you use the same connection object as the one for the DataReader?

If so, here is the problem! Normally a connection currently in use by a dataReader is not available for any other activity...
You have either to use another one or close the dataReader first.

- José
"Saurabh" <[email protected]> a écrit dans le message de [email protected]...
Hi,

I am getting the below mentioned error when I try to update a table


Exception: Unable to update Person records Exception : System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.



I have also set checked the time out of the server to unlimited. I am using the SQLDataReader in my applciation at quite a few palces and make it a point to close the object and set it to nothing.


Here is the snippet of the code.

SqlDataReader lobj_PersonTable = SqlHelper.ExecuteReader(GDFConnectionReader,CommandType.Text,lstr_SQL);

while(lobj_PersonTable.Read())
{

}

lobj_CompanyTable.Close();
lobj_CompanyTable = null;

But if I check the SQL server enterprise manager, I find a number of locks/Process Id under current activity. I feel that its these locks that might be preventing the update statement from executing. Am I missing something here?

Any help would be appreciated.

Thanks and Regards,
Saurabh

 

[Saurabh Prabhu]

Hi,

Sorry I did not explain the issue correctly.

Here I am setting the SQLDataReader object to read from a table
SqlDataReader lobj_PersonTable =
SqlHelper.ExecuteReader(GDFConnectionReader,CommandType.Text,lstr_SQL);

Now the next line of code is the while loop
while(lobj_PersonTable.Read())
{

}

Now it is withn this loop that I am executing an update statement using
Command object. It is this loop that I am encountering a "time out
exception" for every record in the loop. Here is the text
Exception: Unable to update Person records Exception :
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.

Please let me know If you need anything else.

Thanks and Regards,
Saurabh

 

[=?iso-8859-1?Q?Jos=E9_Joye?=]

So once again my question,

Which dB connection are you using within the while loop? Is it the same as the one used by the dataReader?
If yes, just use another connection.


- José
"Saurabh Prabhu" <[email protected]> a écrit dans le message de %[email protected]...
Hi,

Sorry I did not explain the issue correctly.

Here I am setting the SQLDataReader object to read from a table
SqlDataReader lobj_PersonTable = SqlHelper.ExecuteReader(GDFConnectionReader,CommandType.Text,lstr_SQL);

Now the next line of code is the while loop
while(lobj_PersonTable.Read())
{

}

Now it is withn this loop that I am executing an update statement using Command object. It is this loop that I am encountering a "time out exception" for every record in the loop. Here is the text
Exception: Unable to update Person records Exception : System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Please let me know If you need anything else.

Thanks and Regards,
Saurabh

Looking at your code snippet, I'm not too sure where you are trying to update the table.
Is this within the while loop?
Do you use the same connection object as the one for the DataReader?

If so, here is the problem! Normally a connection currently in use by a dataReader is not available for any other activity...
You have either to use another one or close the dataReader first.

- José
"Saurabh" <[email protected]> a écrit dans le message de [email protected]...
Hi,

I am getting the below mentioned error when I try to update a table


Exception: Unable to update Person records Exception : System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.



I have also set checked the time out of the server to unlimited. I am using the SQLDataReader in my applciation at quite a few palces and make it a point to close the object and set it to nothing.


Here is the snippet of the code.

SqlDataReader lobj_PersonTable = SqlHelper.ExecuteReader(GDFConnectionReader,CommandType.Text,lstr_SQL);

while(lobj_PersonTable.Read())
{

}

lobj_CompanyTable.Close();
lobj_CompanyTable = null;

But if I check the SQL server enterprise manager, I find a number of locks/Process Id under current activity. I feel that its these locks that might be preventing the update statement from executing. Am I missing something here?

Any help would be appreciated.

Thanks and Regards,
Saurabh

 

[Guest]

Saurabh,
I suggest that you simplify the connection issue by starting out with a
DataSet instead of a DataReader. Once your connection is closed, you can
iterate over the rows in the DataTable it contains and handle your updates
without having to be concerned about SqlConnection issues.
Peter

 

[Kevin Spencer]

Are you creating a new Connection inside the loop? That would be a mistake,
as a new Connection would be created with every iteration of the loop. And,
as others have mentioned, you cannot use the same Connection as the
DataReader.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist

What You Seek Is What You Get.

 

[Kevin Spencer]

Hi Saurabh,

Could you post the entire portion of the code, starting with the creation of
the Connection (and the Connection String) for the updates, the creation of
the Connection (and the Connection String) for the DataReader, and all the
code inside the loop?

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist

Sequence, Selection, Iteration.


Hi,

Thanks for your suggestions. But I am still stuck. Please have a look at my
answers for all your questions.

{José}Looking at your code snippet, I'm not too sure where you are trying to
update the table.
Is this within the while loop? Do you use the same connection object as the
one for the DataReader?

If so, here is the problem! Normally a connection currently in use by a
dataReader is not available for any other activity...
You have either to use another one or close the dataReader first.
Yes, I am executing the update query within the for loop and the connection
is different from the one used for the reader. I cannot close the data
reader because I will not be able to get the data from the data reader. So
in short there are 2 connections. One for data reader which is used only
once and other to update the records in the for loop. JFYI, the for loop is
iterated 200 times for as many as 6 times


{Kevin Spencer}Are you creating a new Connection inside the loop? That would
be a mistake, as a new Connection would be created with every iteration of
the loop. And, as others have mentioned, you cannot use the same Connection
as the DataReader.
Kevin, please have a look at the explanation provided above. Also I am not
creating a new connection in every loop. It is just opened once at the class
constructor and closed when the class terminates.

{Peter}I suggest that you simplify the connection issue by starting out with
a DataSet instead of a DataReader. Once your connection is closed, you can
iterate over the rows in the DataTable it contains and handle your updates
without having to be concerned about SqlConnection issues.
I cannot use the DataSet as it would result in getting the data stored in
memory, which is not advisible in my case. DataReader is fast and also since
I need to just read data it seems to be the right choice.

Another point that I failed to mention in my previous posts. This is not a
SQL server but an instance of a SQL server created. Please have a look at
the attached screen shot for more information.

Waiting for your replies.

Thanks and Regards,
Saurabh


--------------------------------------------------------------------------------

Hi,

I am getting the below mentioned error when I try to update a table


Exception: Unable to update Person records Exception :
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.



I have also set checked the time out of the server to unlimited. I am
using the SQLDataReader in my applciation at quite a few palces and make it
a point to close the object and set it to nothing.


Here is the snippet of the code.

SqlDataReader lobj_PersonTable =
SqlHelper.ExecuteReader(GDFConnectionReader,CommandType.Text,lstr_SQL);

while(lobj_PersonTable.Read())
{

}

lobj_CompanyTable.Close();
lobj_CompanyTable = null;

But if I check the SQL server enterprise manager, I find a number of
locks/Process Id under current activity. I feel that its these locks that
might be preventing the update statement from executing. Am I missing
something here?

Any help would be appreciated.

Thanks and Regards,
Saurabh