Visual basic SQL server Version 3.0 December 20th, 2010 SelfServing

Trying to duplicate this query.

SQL_Str = "UPDATE InvSlave INNER JOIN [CSA.MDB]." & _
                 "InvMaster ON InvSlave.InvCorpItem = " & _
                 "InvMaster.InvCorpItem SET " & _
                 "InvSlave.InvStockFact = " & facto & _
                 ", InvSlave.DateChanged = #" & _
                 Now & "#, InvSlave.ChangedBy = " & _
                 MAINfrmtag & " WHERE (((InvSlave" & _
                 ".InvSellFact)<>" & facto & ") AND " & _
                 "((InvSlave.InvOrdUnit)= ((InvMaster.InvSellUnit));"

The InvSlave table is in a different database and LLBL project then the InvMaster table.

I tried the following:

Dim expression As New PredicateExpression()
       expression.Add(InvSlaveFields.InvSellFact <> facto)
       expression.AddWithAnd(InvSlaveFields.InvOrdUnit = InvMasterFields.InvSellUnit)

Dim relations As New HOProfBaseDAL.RelationClasses.DynamicRelation                 (HOProfBaseDAL.EntityType.InvSlaveEntity, _
JoinHint.Inner, HOcsaDAL.EntityType.InvMasterEntity, "Slave", "Master",                                             InvSlaveFields.InvCorpItem = InvMasterFields.InvCorpItem)

slaveUpdate.InvSellFact = facto
slaveUpdate.DateChanged = Now
slaveUpdate.ChangedBy = MAINfrmtag
ProfTransManager.Add(slaveUpdate)
InvSlaveCol.UpdateMulti(slaveUpdate, expression, relations)

I get the following:

rightOperand can't be null if the left operand isn't a DerivedTableDefinition Parameter name: rightOperand

Am I doing something wrong? Am I way off base? Or can't this be done?

Any help would be greatly appreciated.

Why are you using DynamicRelation when it seems you can use just an EntityRelation?

Dim expression As New PredicateExpression()
     expression.Add(InvSlaveFields.InvSellFact <> facto)
     expression.AddWithAnd(InvSlaveFields.InvOrdUnit = InvMasterFields.InvSellUnit)

Dim theRelation As New EntityRelation(HOProfBaseDAL.EntityType.InvSlaveFields.InvCorpItem, InvMasterFields.InvCorpItem, JoinHint.Inner)

Dim relations as RelationCollection()
relations.Add(theRelation)

slaveUpdate.InvSellFact = facto
slaveUpdate.DateChanged = Now
slaveUpdate.ChangedBy = MAINfrmtag
ProfTransManager.Add(slaveUpdate)

InvSlaveCol.UpdateMulti(slaveUpdate, expression, relations)

I guess I do not know why I went the way I did but I thank you for your help. Based on you suggestion I tried the following:

Dim expression As New PredicateExpression()
                        expression.Add(InvSlaveFields.InvSellFact <> facto)
                        expression.AddWithAnd(InvSlaveFields.InvOrdUnit = InvMasterFields.InvSellUnit)

                        Dim theRelation As New EntityRelation(InvSlaveFields.InvCorpItem, InvMasterFields.InvCorpItem, RelationType.OneToOne)

                        Dim relations As New RelationCollection()
                        relations.Add(theRelation, JoinHint.Inner)

                        slaveUpdate.InvSellFact = facto
                        slaveUpdate.DateChanged = Now
                        slaveUpdate.ChangedBy = MAINfrmtag
                        ProfTransManager.Add(slaveUpdate)

                        InvSlaveCol.UpdateMulti(slaveUpdate, expression, relations)

When I run it I get the following:

{"An exception was caught during the execution of an action query: Invalid object name 'dbo.InvMaster'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}
    SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: {"An exception was caught during the execution of an action query: Invalid object name 'dbo.InvMaster'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}
    Data: {System.Collections.ListDictionaryInternal}
    HelpLink: Nothing
    InnerException: {"Invalid object name 'dbo.InvMaster'."}
    Message: "An exception was caught during the execution of an action query: Invalid object name 'dbo.InvMaster'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."
    Source: "SD.LLBLGen.Pro.ORMSupportClasses.NET20"
    StackTrace: "   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\ActionQuery.cs:line 261 at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteActionQuery(IActionQuery queryToExecute, ITransaction containingTransaction) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:line 1416  at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.UpdateMulti(IEntity entityWithNewValues, ITransaction containingTransaction, IPredicate updateFilter, IRelationCollection relations) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:line 1269 at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.UpdateMulti(IEntity entityWithNewValues, IPredicate updateFilter, IRelationCollection relations) in c:\Myprojects\VS.NET Projects\LLBL
Gen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\EntityCollectionBase.cs:line 603   at HOIN.IN_UnitEdit.SaveButton_Click(Object sender, EventArgs e) in C:\HorizonSQL\HomeOffice\HO_IN\IN_UnitEdit.vb:line 231"
    TargetSite: {System.Reflection.RuntimeMethodInfo}

Again, any help would be greatly appreciated.

You say that invMaster and invSlave are in different databases - how are both of the tables mapped in the LLBLGen project - ie are they added from seperate catalogs, or is one of them setup as a linked table in the other database and added from the same catalog...?

Matt

InvSlave and InvMaster are in seperate databases and also in seperate LLBLGen projects. They are added from seperate catalogs.

I have HOcsaDAL which is a LLBLGen project created from the HOcsa database. I then have HOprofbaseDAL which is another LLBLGen project created from the ProfileBase database.

I will try to explain why it is setup like that. One of our customers corporation may have 25 stores and the stores are grouped into user defined PROFILES. When a profile is created, multiple databases are created, one of which is the ProfBase database. If I have three profiles I would have three ProfBase databases. If my profiles were called CaliforniaStores, WisconsinStores and FloridaStores I would have three databases called CaliforniaStores.mdf, WisconsinStores.mdf and FloridaStores.mdf. Each one of the three databases has a table called InvSlave which contains information about an inventory item that is unique to that profile. ie how an item is sold, the vendor that the item is purchased from etc. In the HOcsa.mdf there is a table called InvMaster that contains the information that is universal to the corporation. ie description, UPC, etc. So if I want to get information about sales for a given item, I would get the description from the HOcsa.mdf and the sales data from the profbase database that I am interested in.

The scema is the same for all of the individual ProfBase databases, that is why a sepearte LLBLGen project was created for it, and the catalog name is changed when accessing each.

I hope that I explained my situation clear enough. Maybe there is a better way.

Hi Bishman,

That clarifies the situation. And that is not supported (create run-time relations of objects from different databases mapped on different LLBLGen projects). If you would have mapped the two databases on the same LLBLGen project that would be possible. It's not possible in your situation. You have to find a workaround. For instance: collect all the InvMasterIds and then pass them into a FieldCompareSet predicate in the other project.

Thank you very much for your help.

I have one very basic (maybe dumb) question for you.

In my case, with the multiple ProfBase databases, can I create one LLBLGen project with the a catalog for the HOcsa database and a catalog for the generic ProfBase scema? And if so, how would I point to the correct ProfBase database (CaliforniaStores, FloridaStores) while coding? Currently with seperate projects for the ProfBase database I change the InitialCatalog in the ActualConectionString.

In my case, with the multiple ProfBase databases, can I create one LLBLGen project with the a catalog for the HOcsa database and a catalog for the generic ProfBase scema? And if so, how would I point to the correct ProfBase database (CaliforniaStores, FloridaStores) while coding? Currently with seperate projects for the ProfBase database I change the InitialCatalog in the ActualConectionString.

So you have one Main schema and one Sub-Schema which can be mapped to different catalogs at runtime according to the store in question. All are within the same server.

You can use more than one catalog in the same LLBLGen Project. And so you can map entities from the Main and sub schemas to the same project file.

Then you should use CatalogName overwrites as follows, to set an "empty string" for the SubCatalog name:

<configSections>
    <section name="sqlServerCatalogNameOverwrites" type="System.Configuration.NameValueSectionHandler" />
</configSections>

<sqlServerCatalogNameOverwrites>
    <add key="MainCatalog" value="MainCatalog" />
    <add key="SubCatalog" value="" />
</sqlServerCatalogNameOverwrites>

This way the catalog name passed in the connectionString will be used instead. So you can change this dynamically.

Please note that in SelfServicing the ConncationString is globaly used, while in the Adapter model, this can be set per instance of the DataAccessAdapter class.

Again thank you very much.

To make sure that I understand. The MainCatalog and SubCatalog are the two catalogs defined with the designer. What if I have multiple SubCatalogs that need to be changed dynamically like the ProfBase catalog? Is this possible?

So I have : HOcsa - not changed Fuel - not changed ProfBase - changed depending on profile selected ProfSales - changed depending on profile selected StoreSales - changed depending on the store selected

So I have : HOcsa - not changed Fuel - not changed ProfBase - changed depending on profile selected ProfSales - changed depending on profile selected StoreSales - changed depending on the store selected

I don't quite understand which ones of these are in the same LLBLGen project. Anyway, the thing is this: Selfservicing isn't designed to be dynamically using multiple databases at runtime. So you can add multiple catalogs but you can't switch them separately at runtime, because the persistence info have these information (catalog names) in there. The only way is via config file, or via DBUtils.ActualConnectionString, however this will change the whole connection string info and not the both catalog names at runtime.

If you want to make your life easier, and you can (i.e. you have the time and opportunity to migrate) I would recommend you to migrate to Adapter template set.