I think I know the answer to this question but here goes anyway.

We have a vb 2008 project called "Home Office". As it's name implies, it contains the data from several stores within an organization. The stores can be grouped into "Profiles". For each profile there are four different databases (catalogs) which reside on the same sql server. So if I have a profile by the name of CaliforniaStores, I have the following four catalogs on the server: CaliforniaStores CaliforniaStoresSales CaliforniaStoresAdvanced CaliforniaStoresFleet Likewise if I have a profile called "WisconsinStores" I have the following: WisconsinStores WisconsinStoresSales WisconsinStoresAdvanced WisconsinStoresFleet There is also a catalog called "HOCSA", this catalog contains the non store specific information. For example, in HOCSA there is an ItemMaster table cantaining all of the inventory items for the corporation and a Vendor table for all of the vendors for the corporation.

Each profile database contains a table, InvVend, whose records show which vendor supply which inventory items. So if I want to show which vendor supplies a given item for each store, I need to look in the CaliforniaStores\InvVend table and the WisconsinStores\InvVend table.

All of the tables in the CaliforniaStores and WisconsinStores are identical. As are the tables in the CaliforniaStoresSales and WisconsinStoresSales catalogs.

Without creating a project for both the CaliforniaStores and WisconsinStores database, can I use SelfServing in this situation? I think that I need to be using Adaptor, correct?

Also, is it possible to do an inner join between the ItemMaster, Vendor and InvVend tables?

which llblgen pro version are you using?

sorry version 3.0 8/18/2010

It's possible to you to add multiple catalogs using SelfServicing, you also can create relationships between them. Please look at this to clarify what you can/can't do: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10622&StartAtMessage=0&#59291

I'm sorry but I am still having problems with this.

I will make another attempt at describing my problem.

In my VB2008 project I have an LLBLgenPro project called csaDAL, that was created using a database called HOCSA. I also have another LLBLgenPro project called ProfBaseDAL, that was created using a database called California Stores.

My data contains 4 different profiles (store groupings), therefore as mentioned before I have a database for each profile. They are: CaliforniaStores RenoStores WisconsinStores ParkwayStores

If I add a new store to the WisconsinStores profile I have to create a record in a table called InvSlave (which is found in the WisconsinStores database) for each inventory item that is found in the InvMaster table (which is found in the HOCSA database). I can only add these records if I find a record for the item in one of the profile databases. My code looks like this:

    ProfileDB = StripSpaces(ComboBox2.Text)
    StorID = TextBox8.Text

    str1 = " Default Item Setup"

    Dim imFilter As New PredicateExpression
    imFilter.Add(InvMasterFields.InvReject = False)
    imFilter.AddWithAnd(InvMasterFields.InvDesc <> str1)
    Dim itemMaster As New InvMasterCollection
    itemMaster.GetMulti(imFilter)
    If itemMaster.Count <> 0 Then
        For i = 0 To itemMaster.Count - 1

            'set the connection to the profile name selected
            ProfBaseDAL.DaoClasses.CommonDaoBase.ActualConnectionString = _
            "data source=DONS\DONSSQL2008;initial catalog=" & ProfileDB & ";" & _
            "integrated security=SSPI;persist security info=False;packet size=4096"

            Found_Slave = False
            'check the selected profile for invslave record for the item
            Dim slaveFilter As New PredicateExpression
            slaveFilter.Add(InvSlaveFields.InvCorpItem = itemMaster(i).InvCorpItem)
            Dim slave As New InvSlaveCollection
            slave.GetMulti(slaveFilter)
            If slave.Count = 0 Then
                Dim stPro As New PBStoreProfileCollection
                stPro.GetMulti(Nothing)
                If stPro.Count <> 0 Then
                    'check the other profiles for an invslave record for the item
                    For j = 0 To stPro.Count - 1
                        ProfileDB = StripSpaces(stPro(i).PrfName)
                        ProfBaseDAL.DaoClasses.CommonDaoBase.ActualConnectionString = _
                        "data source=DONS\DONSSQL2008;initial catalog=" & ProfileDB & ";" & _
                        "integrated security=SSPI;persist security info=False;packet size=4096"
                        slave.GetMulti(slaveFilter)
                        If slave.Count <> 0 Then
                            Found_Slave = True
                            Exit For
                        End If
                    Next
                Else
                    Found_Slave = False
                End If
            Else
                Found_Slave = True
            End If

            OrdItem = 0
            If Found_Slave Then
                OrdItem = itemMaster(i).InvCorpItem
                Dim newSlave As New InvSlaveEntity
                newSlave.InvCorpItem = slave(0).InvCorpItem
                newSlave.InvStoreId = TextBox8.Text
                newSlave.InvItem = 0
                newSlave.InvOrdUnit = slave(0).InvOrdUnit
                newSlave.InvCorpOrdItem = OrdItem
                newSlave.InvStockFact = slave(0).InvStockFact
                newSlave.InvSellFact = slave(0).InvSellFact
                newSlave.InvLinkItem = slave(0).InvLinkItem
                newSlave.InvPrice = slave(0).InvPrice
                newSlave.InvPriceType = slave(0).InvPriceType
                newSlave.InvRounding = slave(0).InvRounding
                newSlave.InvRounding2 = slave(0).InvRounding2
                newSlave.InvAssign = slave(0).InvAssign
                newSlave.DateChanged = Now
                newSlave.ChangedBy = MAINfrmtag
                newSlave.InvPage = slave(0).InvPage
                newSlave.InvSage = slave(0).InvSage
                newSlave.InvDeliLic = slave(0).InvDeliLic
                newSlave.InvProdCode = slave(0).InvProdCode
                newSlave.InvAvailable = True
                newSlave.InvRetailControl = "C"
                newSlave.InvReturn = slave(0).InvReturn
                newSlave.InvPartialSales = slave(0).InvPartialSales
                newSlave.InvSpecialDisc = slave(0).InvSpecialDisc
                newSlave.InvCommission = slave(0).InvCommission
                newSlave.InvDeliMemo = slave(0).InvDeliMemo
                newSlave.InvPossent = slave(0).InvPossent
                newSlave.InvPosstat = slave(0).InvPosstat
                newSlave.InvPosid = slave(0).InvPosid
                newSlave.InvModifier = 0
                newSlave.InvDeleted = False
                newSlave.NewToHo = False

                ProfileDB = StripSpaces(ComboBox2.Text)
                ProfBaseDAL.DaoClasses.CommonDaoBase.ActualConnectionString = _
                "data source=DONS\DONSSQL2008;initial catalog=" & ProfileDB & ";" & _
                "integrated security=SSPI;persist security info=False;packet size=4096"

                newSlave.Save()
            End If

My question is, should this work using SelfServing, which I am? If so, when I set the first actual connection string for the ProfBaseDal, I am doing something wrong because it keeps checking the InvSlave tabel for the CaliforniaStores database.

Should I be using Adapter?

All of these databases are on the same sql server. Is there a way to just change the catalog name and not the actual connection string?

Maybe I should only have one llblgenpro project with all of the databases. Would this make things easier?

I'm ok now. The above code worked fine once I put the catalog name overwrites in my config file