SqlDataReader-Ouput Parameter And SqlConnection
-
Hi, I am using a procedure which selects some rows and handles a output parameter. I am executing this procedure using SqlDataReader. Problem Is , If Connection is kept open after ExecuteReader(), Sql Reader is working fine and able to do data binding. But, output parameter is returing null value. If Connection is closed, I am able to get the correct output parameter value. But, SqlDataReader is not working. Any Ideas to make both working regardless of connection status. Thanks, Nagaraj
-
Hi, I am using a procedure which selects some rows and handles a output parameter. I am executing this procedure using SqlDataReader. Problem Is , If Connection is kept open after ExecuteReader(), Sql Reader is working fine and able to do data binding. But, output parameter is returing null value. If Connection is closed, I am able to get the correct output parameter value. But, SqlDataReader is not working. Any Ideas to make both working regardless of connection status. Thanks, Nagaraj
-
You can't. You only get the ouput parameter back when you close the connection. Its always been that way.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
Thanks Bob, My code is fetching the records for GridView Binding (for the selected PageIndex) and total results in single call to database. I am returing SqlReader from DAC to BusinessLayer and to Code file from there. Is there any better way to achive this? Thanks again Nagaraj
-
Thanks Bob, My code is fetching the records for GridView Binding (for the selected PageIndex) and total results in single call to database. I am returing SqlReader from DAC to BusinessLayer and to Code file from there. Is there any better way to achive this? Thanks again Nagaraj
DataReader needs open connection. Better you use DataSet, DataAdapter.
Coding C# www.excitetemplate.com
-
Thanks Bob, My code is fetching the records for GridView Binding (for the selected PageIndex) and total results in single call to database. I am returing SqlReader from DAC to BusinessLayer and to Code file from there. Is there any better way to achive this? Thanks again Nagaraj
You could use a dataset and fill that, I think you would then get your output parameter straight away - not 100% sure though, I haven't tried it. Alternatively, return 2 recordsets and use a dataadaptor to fill a dataset, you then should get what would have been the output param in its own table. Hope some of this helps
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
You could use a dataset and fill that, I think you would then get your output parameter straight away - not 100% sure though, I haven't tried it. Alternatively, return 2 recordsets and use a dataadaptor to fill a dataset, you then should get what would have been the output param in its own table. Hope some of this helps
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
Ashfield wrote:
think you would then get your output parameter straight away
Unfortunately not - you have to iterate over the lot before you get the output parameter back. This has caused me grief a few times.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
-
Ashfield wrote:
think you would then get your output parameter straight away
Unfortunately not - you have to iterate over the lot before you get the output parameter back. This has caused me grief a few times.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith
As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
Pete O'Hanlon wrote:
Unfortunately not - you have to iterate over the lot before you get the output parameter back
Ought to have guessed that really. :-O I never tried it, I generally don't bother with output parameters simply because you can't get them when you really want them, usually first! If I need something like that I often return 2 record sets, the 1st with what would be the output parameter and the 2nd with the real data - probably not ideal, but it works for me :)
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP