By Ben Kim - 12/7/2006
Hopefully this is my last question of the day! Sorry to be so active here today!This is not something I would do in production but... In my POC application, I changed the BrowseDialog1.MaximumReturnedRecords to 10,000. When I do a search with no criteria (bring back the first 10,000 entries), I receive the attached exception regardless which record I select for edit, upon return to the entry form. If I change the MaximumReturnedRecords back to 1,000 (the default), all is well. Can someone shed some light on this error? Thanks! Ben
|
By Trent L. Taylor - 12/7/2006
You would have to click the View detail and give me all of the exceptions to give you an exact answer. But your problem is directly related to size. More than likely a MemoryException. You should really never bring back that many records for a table with more than a couple of fields. I am not sure of your background, but you have to work in a disconnected environment and should only bring back small quantities of records. 10,000 is too many and I would recommend reworking your logic so that you work in smaller data sets. If you set the MaximumRecords to 1500 I would doubt that the error would occur.
|
By Ben Kim - 12/7/2006
As I stated in the post "not that I would do this in production"... Attached is the details expanded. I want to make sure to plug any holes in the framework (perhaps a more graceful message than this exception) to shield our developers (AND ultimately our end-users) from such exceptions.Ben
|
By Trent L. Taylor - 12/7/2006
I am not sure what the error is, but you can turn on data debugging to look at the SELECT statement and even run it manually through SQL Server Management Studio to see if the same error occurs. This is a SQLexpcetion so something happened on the SQL Server....which could be memory as well.To turn on data debugging do the following: In the AppMain.vb (or program.cs) file, go to the SetDataSourcesMethod. After the SetConnections method, add the following code: MicroFour.StrataFrame.Data.DataBasics.DataSources(0).SetDebugOn("C:\mydebug.html",True) Run your program and after the app closes the HTML doc will pop up (if it doesn't you can manually open it).
|
By Ben Kim - 12/7/2006
Well you may not like what I have to report. The first SELECT TOP 10000... works just fine. I assume when I click OK on the BrowseDialog the second SELECT statement is created and send to the backend. The string is huge (> 4meg) setting up params such as NameNo=Parm1 OR ... NameNo=Parm9999 OR NameNo=ParmXXXX.I attached the debug HTML document for your review. Is this how the select statement should be formed??? I would have sent the same SELECT TOP 10000... again personally  (again not that I would do that in production! LOL) Ben
|
By Ben Kim - 12/7/2006
Take a close look at the debug log. It is the child table that is causing the problem!NameLoca ---> AddrHist Shouldn't it just be selecting the child records for the "current" NameLoca record and not ALL based on the filtered return from the BrowseDialog form??? Ben
|
By Trent L. Taylor - 12/7/2006
Well, I am confident that it is not within the framework but more than likely the stop-gap between the data access layer and the ADO.NET data table. Deductive reasoning on this one will point to a size or memory issue. If you debug the 1000 statment and the 10000 statement, you will see one character different (a zero). We are just filling a data table with an adapter....and over time, when we have stopped and debugged this type of error thinking it may be something in the framework, we have found...everytime thus far....it is a memory issue because too much data is coming back. Hope this helps.
|
By Trent L. Taylor - 12/7/2006
The problem you are running into isn't the 10000 return results, if you look at the second SELECT statement, there is a query with more than 4096 parameters....this is a limitation of SQL Server. This table is selecting from AddrHist, I am not sure where this query is being generated, but it is definitely going to produce an SqlException because you have too many parameters being passed.
|
By Trent L. Taylor - 12/7/2006
Do you have code to load up the child records automatically (i.e. Navigated event, BusinessLayerLinkManager, etc.)? In other words when you click the OK button? If so, this is the problem because the 4096 parameter limit is being hit. THere is nothing we can do about this....it is just how SQL Server is.You will have to reduce your recordset size.
|
By Ben Kim - 12/7/2006
This is the only code I have which could be causing the issue I assume:Private Sub NameLocaBO1_Navigated(ByVal e As MicroFour.StrataFrame.Business.NavigatedEventArgs) Handles NameLocaBO1.Navigated
If NameLocaBO1.Count > 0 Then
Me.AddrHistBO1.FillByParent()
Me.ugAddressHistory.Refresh()
End If
End SubSo If I do FillByParentPrimaryKey how do I do it with a relationship that is multi-component? NameLoca
PersBuss (1st component of primary key)
NameNo (2nd component of primary key) AddrHist
PersBuss (1st component of primary key)
NameNo (2nd component of primary key)
HistDT (3rd component of primary key) AddrHist.PersBuss = NameLoca.PersBuss
AddrHist.NameNo = NameLoca.NameNo
Ben
|
By Trent L. Taylor - 12/7/2006
This is your problem....and there is no way around it due to the SQL Server limitations. The FillByParent is going to create a lot of parameters due to the nature of the relationship. In this example, FillByparentPrimaryKey is going to retrieve the exact same record set but will only have 1 parameter which will be much faster anyway since there is not nearly as much TDS (SQL Server Tabular Data Stream) data that will go over the network. Whether you are using SF or anything else, this is still not a good idea and you are going to have the same problems.However, at least using SF you can call the FillByParentPrimarykey and your current problem should go away.
|
By Trent L. Taylor - 12/7/2006
After looking at your previous post again, the relationship may not support the FillByParentPrimary key, but in any case, you are going to have to reduce the record size of the parent table....otherwise you are going to continue to have this problem....and there isn;t anything we can do about it....it isn't our limitation, it is SQL Servers....which honestly makes sense. The record set is too large.
|
By Ben Kim - 12/7/2006
Forget it. I think I found my answer in one of the overloaded methods of FillByParentKey!On the addresshistory business object the ParentRelationship is: NameLocaBO, (PersBuss,NameNo) <--> (PersBuss,NameNo) If I change my method call to: Me .AddrHistBO1.FillByParentPrimaryKey(NameLocaBO1.PersBuss, NameLocaBO1.NameNo)All is well! Thank you for such stellar support! Ben
|
By Trent L. Taylor - 12/7/2006
Good to hear you're going
|
By Trent L. Taylor - 12/7/2006
Wen you mentioned "multi-key" I presume you were referring to Compound Primary Keys....in this case, SF auto-detects and has full support for compound primary keys, which you discovered.
|