C# - Already An Open DataReader Associated With This Command - When I'm Not Using Datareader
Jul 28, 2010
I am getting an error that an open DataReader associated with this Command, when I'm not using datareader(though probably executereader() is the same thing) how would I close this if I don't have a datareader present?
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand("spSelectAllTypes",conn);
cmd.CommandType = CommandType.StoredProcedure;
[code]...
I just want to be able to databind a bunch of dropdownlist in one open connection. (before I had multiple open and closes for each control)
My scenario:I am using a web service which access the sql server database to provide an XML output. this part is working fine. I am using a SqlDataAdapter and DataTable with Fill method to get the data.
Problem:When more than one person access this web service at the same time i get the error message which is given in the subject line.
I understand the problem is because of the sql connection which is still open by the first user. If the first user completes his operation before the second users request then both the user receives the correct response.
My Solution:What i did was i initiated 10 connection strings and will check which connection string is closed and i have used this connection for the second persons request if they both access at the same time.
I'm having a method that exports content from the database to excel files. The method taks as paramaters a DataReader param and a int param - the number of rows. For the number of rows i'm using a dataset, wich i fill using the same query as for the datareader. So I'm executing it twice... Is there a way I can avoid that? get the number of rows from the datareader?
I've got a static class containing a static field which makes reference to a wrapper object of a DataContext.
The DataContext is basically generated by Visual Studio when we created a dbml file & contains methods for each of the stored procedures we have in the DB.
Our class basically has a bunch of static methods that fire off each of these stored proc methods & then returns an array based on a LINQ query.
Example:
public static TwoFieldBarData[] GetAgesReportData(string pct) { return DataContext .BreakdownOfUsersByAge(Constants.USER_MEDICAL_PROFILE_KEY, pct) .Select(x => new TwoFieldBarData(x.DisplayName, x.LeftValue, x.RightValue, x.TotalCount)) .ToArray(); }
Every now and then, we get the following error:
There is already an open DataReader associated with this Command which must be closed firs
This is happening intermittently and I'm curious as to what is going on. My guess is that when there's some lag between one method executing and the next one firing, it's locking up the DataContext and throwing the error.
Could this be a case for wrapping each of the DataContext LINQ calls in a lock(){} to obtain exclusivity to that type and ensure other requests are queued?
I have a couple of queries that I need to run one to a linked server and one not like this
Dim InvestorLookup As String = "DECLARE @investor varchar(10), @linkedserver varchar(25), @sql varchar(1000) " InvestorLookup += "SELECT @investor = '" & investor & "', @linkedserver = '" & db & "', " InvestorLookup += "@sql = 'SELECT * FROM OPENQUERY(' +@linkedserver + ', ''SELECT * FROM db WHERE investor = ' + @investor + ' '')' EXEC(@sql)" Dim queryInvestorLookup As SqlCommand = New SqlCommand(InvestorLookup , conn) Dim BondNoDR As SqlDataReader = queryInvestorLookup.ExecuteReader() Dim PasswordCheck As String = "DECLARE @investor varchar(10), @password varchar(20), @linkedserver varchar(25), @sql varchar(1000) " PasswordCheck += "SELECT @investor = '" + investor + "', @password = '" + password + "', @server = '" + db2 + "', " PasswordCheck += "@sql = 'SELECT * FROM @server WHERE investor = @investor AND password = ' + @password + ' '' EXEC(@sql)" Dim queryPasswordCheck As SqlCommand = New SqlCommand(PasswordCheck, conn) Dim PasswordDR As SqlDataReader = queryPasswordCheck.ExecuteReader()
As far as I can tell from debugging the queries both run as they should but I get the error There is already an open DataReader associated with this Command which must be closed first. Is it possible to run two queries in two different DataReaders. I need to later reference each DataReader and select values from each.
i m trying to use the sql datareader to fetch a single row at a time i wanna know can we use it to fetch a single row at a time or not if yes i m unable to figure out what i m doing wrong here bcus its not returning any data
[Code]....
of course data is present in my table bcuz when i m using dataset i m able to the return the single row throuugh dataset
Why can I not call the global variables CatCode and CompanyName which are equated in the first procedure GetCompanyCatID And use their new values in the second procedure UpdateCompanyCounts as parameter values. When I hard code them code their values at the end of the GetCompany CatID procedure the following procedure updates properly.
string CatCode; - delcared global string CompanyName; - delcared global protected void GetCompanyCatID() { LinkID2 = Request.QueryString["linkid"].ToString(); string L_ID = LinkID2; SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString001"].ConnectionString); SqlCommand cmd = new SqlCommand("GetCID", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@L_ID", L_ID); try { con.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (reader["CG_ID"] != System.DBNull.Value) CatCode = (string)reader["CG_ID"]; CompanyName = (string)reader["CG_Name"]; } } } catch (SqlException exc) { } finally { con.Close(); } // ******* HARD CODED to test below update procedure, it works. When commented out the following update procedure does not update. CatCode = "55555" CompanyName = "Test"; UpdateCompanyCounts(); } protected void UpdateCompanyCounts() { LinkID2 = Request.QueryString["linkid"].ToString(); string L_ID = LinkID2; string CatCode2 = CatCode; string CompanyName2 = CompanyName; // string CatCode2 = "23456"; // string CompanyName2 = "TEST"; SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString001"].ConnectionString); SqlCommand cmd2 = new SqlCommand("UpdateCatCounter", con2); cmd2.CommandType = CommandType.StoredProcedure; cmd2.Parameters.AddWithValue("@L_ID", LinkID2); cmd2.Parameters.AddWithValue("@CG_ID", CatCode2 ); cmd2.Parameters.AddWithValue("@CG_Name", CompanyName2 ); try { con2.Open(); cmd2.ExecuteNonQuery(); } catch (SqlException exc) { // ErrorBox.Text += string.Format("Error: {0}<br/>", exc.Message); } finally { con2.Close(); } }
How the does the gridview populate all records?.As the reader is capable of reading one row per read,I thought only the last row is available for GridView to display.
I have an aspx page which allows a user to submit modified entries into the database, but when the user clicks Submit to fire the stored procedure I want to first run a check to see if a modified row with the same relationship exists.I am passing the results of the following query:
SELECT SwitchRoom.ModifiedID FROM SwitchRoom WHERE SwitchRoomID = @ChkSwitchRmID", constring;
into a DataReader to determine if that relationship exists.I need it to determine whether the reader returns NULL to allow the procedure to execute, if it doesn't, then don't allow the user to save the information.I've tried the following:
if (dbreader = NULL) { Fire Procedure } else { "Error Message" }
and I've even tried passing the reader into a datatable and running it against that without any luck.
I have put a SQLDataSource on my page and everything is adjusted and it works properly. I read an article here which was indicating that you can use a DataReader in order to get data from sqldatasource, but it doesn't say how, the only thing that is said is to use DataSourceMode property in order to adjust it for a DataReader.
public class ProductDetails { private string _productid; public string ProductID { get { return _productid; } } private string _description; public string Description { get { return _description; } } private string _image; public string Image { get { return _image; } } public ProductDetails(string productid, string description, string image) { _productid = productid; _description = description; _image = image; } } public class DataAccess { private static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; public ProductDetails GetProductDetails() { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("ProductDetails", con); cmd.CommandType = CommandType.StoredProcedure; string productid = HttpContext.Current.Request.QueryString["productid"]; cmd.Parameters.Add(new SqlParameter("productid", SqlDbType.NVarChar)).Value = productid; con.Open(); SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); ProductDetails pro = new ProductDetails( (string)reader["ProductID"], (string)reader["Description"], (string)reader["Image"]); reader.Close(); return pro; } } protected void PopulateControls() private DataAccess da = new DataAccess(); { ProductDetails product = da.GetProductDetails(); if (product.Description == String.Empty) { lblDescription.Visible = false; } else { lblDescription.Text = product.Description; } }
In the last piece i want to check if the Description field is null or empty in order to hide the label control but i get an error if i write: if (product.Description == DBNull.Value) and can only use ring.Empty. But i want to check for NULL values as well..
