Sql multiple queries [modified]
-
Hello, I want to use multiple queries (so, in the while-loop of a reader, i want to use another query + reader). The folowwing code will give an example:
SqlCommand myCommand = new SqlCommand("SELECT date, customerid FROM orders ORDER BY date ASC", connect); SqlDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { SqlCommand myCommand2 = new SqlCommand("SELECT customer FROM customers WHERE id = " + myReader["customerid"] + " ORDER BY date ASC", connect); SqlDataReader myReader2 = myCommand2.ExecuteReader(); while (myReader2.Read()) listBestellingen.Items.Add(myReader["date"] + " " + myReader2["customer"]); } myReader.Close();But he sais i allready have a reader open (which is the truth), and that i have to close it, but that's a no can do).. Can somebody help me? Thanks! -- modified at 16:20 Thursday 25th May, 2006 -
Hello, I want to use multiple queries (so, in the while-loop of a reader, i want to use another query + reader). The folowwing code will give an example:
SqlCommand myCommand = new SqlCommand("SELECT date, customerid FROM orders ORDER BY date ASC", connect); SqlDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { SqlCommand myCommand2 = new SqlCommand("SELECT customer FROM customers WHERE id = " + myReader["customerid"] + " ORDER BY date ASC", connect); SqlDataReader myReader2 = myCommand2.ExecuteReader(); while (myReader2.Read()) listBestellingen.Items.Add(myReader["date"] + " " + myReader2["customer"]); } myReader.Close();But he sais i allready have a reader open (which is the truth), and that i have to close it, but that's a no can do).. Can somebody help me? Thanks! -- modified at 16:20 Thursday 25th May, 2006A datareader keeps the connection open exclusively for the duration of its read operation. There are 2 ways to rectify this: 1. use another connection for the second datareader 2. Read the contents of first datareader into an arraylist, close the datareader and then use the same connection for the second datareader (recommended). With Regards Shane Sukul BSc Mcsd.Net Mcsd Mcad .Net Architect/Developer Ashlen Consulting Services P/L
-
Hello, I want to use multiple queries (so, in the while-loop of a reader, i want to use another query + reader). The folowwing code will give an example:
SqlCommand myCommand = new SqlCommand("SELECT date, customerid FROM orders ORDER BY date ASC", connect); SqlDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { SqlCommand myCommand2 = new SqlCommand("SELECT customer FROM customers WHERE id = " + myReader["customerid"] + " ORDER BY date ASC", connect); SqlDataReader myReader2 = myCommand2.ExecuteReader(); while (myReader2.Read()) listBestellingen.Items.Add(myReader["date"] + " " + myReader2["customer"]); } myReader.Close();But he sais i allready have a reader open (which is the truth), and that i have to close it, but that's a no can do).. Can somebody help me? Thanks! -- modified at 16:20 Thursday 25th May, 2006You can only have one active reader per connection. You must open a new connection to run two readers simultaneously on the same database. You also don't close your second reader which means it won't get closed until the garbage collector cleans it up - and that may not be for a long time. However, you can do all this with one SQL command:
SELECT orders.[date], cusomter.customer
FROM orders
INNER JOIN customer ON orders.customerid = customer.id
ORDER BY [date] ASCSo your code would look like this:
SqlCommand = myCommand = new SqlCommand("SELECT orders.[date], cusomter.customer"+
"FROM orders INNER JOIN customer ON orders.customerid = customer.id ORDER BY [date] ASC";
SqlDataReader myReader = myCommand.ExecuteReader();
while(myReader.Read())
{
listBestellingen.Items.Add(myReader["date"] + " " + myReader["customer"]);
}
myReader.Close();Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
A datareader keeps the connection open exclusively for the duration of its read operation. There are 2 ways to rectify this: 1. use another connection for the second datareader 2. Read the contents of first datareader into an arraylist, close the datareader and then use the same connection for the second datareader (recommended). With Regards Shane Sukul BSc Mcsd.Net Mcsd Mcad .Net Architect/Developer Ashlen Consulting Services P/L
Or a third option to produce a better SQL command that only requires one reader.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
Hello, I want to use multiple queries (so, in the while-loop of a reader, i want to use another query + reader). The folowwing code will give an example:
SqlCommand myCommand = new SqlCommand("SELECT date, customerid FROM orders ORDER BY date ASC", connect); SqlDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { SqlCommand myCommand2 = new SqlCommand("SELECT customer FROM customers WHERE id = " + myReader["customerid"] + " ORDER BY date ASC", connect); SqlDataReader myReader2 = myCommand2.ExecuteReader(); while (myReader2.Read()) listBestellingen.Items.Add(myReader["date"] + " " + myReader2["customer"]); } myReader.Close();But he sais i allready have a reader open (which is the truth), and that i have to close it, but that's a no can do).. Can somebody help me? Thanks! -- modified at 16:20 Thursday 25th May, 2006SQL Server 2005 supports having multiple readers open at the same time. (A feature called MARS). You need to add
MultipleActiveResultSets=Trueto the connectionstring to make this work for your connection.string connectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;IntegratedSecurity=SSPI;MultipleActiveResultSets=True";
-
You can only have one active reader per connection. You must open a new connection to run two readers simultaneously on the same database. You also don't close your second reader which means it won't get closed until the garbage collector cleans it up - and that may not be for a long time. However, you can do all this with one SQL command:
SELECT orders.[date], cusomter.customer
FROM orders
INNER JOIN customer ON orders.customerid = customer.id
ORDER BY [date] ASCSo your code would look like this:
SqlCommand = myCommand = new SqlCommand("SELECT orders.[date], cusomter.customer"+
"FROM orders INNER JOIN customer ON orders.customerid = customer.id ORDER BY [date] ASC";
SqlDataReader myReader = myCommand.ExecuteReader();
while(myReader.Read())
{
listBestellingen.Items.Add(myReader["date"] + " " + myReader["customer"]);
}
myReader.Close();Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog