February 22, 2006 at 9:41 am
I have a developer that has databound datagrids as follows:
Datagrid.datasource= DataSet (yes, the compiler allows this)
The dataset is returned from stored procedures. Each of the sps return a single recordset, but periodically we are seeing some nondeterministic behavior where the code for the datasource above is causing a runtime error. More than one recordset is being returned for some reason.
Of course, the easiest way to fix this is to have the developer change code to
Datagrid.datasource = DataSet.Table("Some Table or Index"),
but is there are anything that can be done from a SQL Server standpoint, that will only return one recordset ?
TIA
Mathew J Kulangara
sqladventures.blogspot.com
February 22, 2006 at 11:06 am
Fix the stored proc to return only one record set. Check for all the unnecessary SELECT statements in the stored proc.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
February 22, 2006 at 11:30 am
Thanks..we already did that. Seems like somehow, despite this we are getting 2 record sets returned sometimes. I'm apt to think some session setting is off depending on the user.
Mathew J Kulangara
sqladventures.blogspot.com
February 22, 2006 at 5:46 pm
Another thing you can do is, instead of binding the dataset to the datagrid, try binding a datareader.
....
dr = sqlcommand.executereader()
datagrid.datasource = dr
....
Or Perhaps you can also set it to dataset.tables(0)
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
February 23, 2006 at 9:07 am
I have to agree with the previous poster. If you only ever want one table from the dataset, binding by default to the dataset is bad form. Not specifying the table he wants (dataset.tables(0)) is just not defensive programming. The previous poster is also correct that the datareader would be better in this situation. Its faster, and you avoid the multirecordset-causes-error issue, since you wouldnt be calling DataReader.NextRecordset.
While that fixes the apparent error in the app, it doesnt begin to address that if two or more recordsets are generated, which one is the correct one? Have you yet determined under what conditions a single SELECT statement would create multiple return sets? That's what I am curious about. If the names can be changed to protect the innocent, could you post the sproc in question?
February 23, 2006 at 9:08 am
I have to agree with the previous poster. If you only ever want one table from the dataset, binding by default to the dataset is bad form. Not specifying the table he wants (dataset.tables(0)) is just not defensive programming. The previous poster is also correct that the datareader would be better in this situation. Its faster, and you avoid the multirecordset-causes-error issue, since you wouldnt be calling DataReader.NextRecordset.
While that fixes the apparent error in the app, it doesnt begin to address that if two or more recordsets are generated, which one is the correct one? Have you yet determined under what conditions a single SELECT statement would create multiple return sets? That's what I am curious about. If the names can be changed to protect the innocent, could you post the sproc in question?
February 23, 2006 at 9:39 am
Thanks all...the issue was resolved, by turning warnings off.
What was peculiar about this issue was that we were only getting this error 10% of the time, and were never able to replicate it in our Dev environment. As it turns out, validation was not being done on the web form on parameters..and some values entered by users were creating warnings.
Mathew J Kulangara
sqladventures.blogspot.com
February 23, 2006 at 11:39 am
Its a good practice to SET NOCOUNT ON at the beginning of the stored proc and set it to OFF at the end so the warnings/messages can be suppressed.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
February 23, 2006 at 11:57 am
Actually in this case Set NoCount was off...but had to SET ANSI_WARNINGS OFF.
Mathew J Kulangara
sqladventures.blogspot.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply