:dbDataTable Connection State Open

[WHassan]

Hi,

I am trying to run queries on an SQL database on various tables of my database.

Various Child designs are requiring a connection to their respective Tables.

The problem I am encoutering a problem that when I use multiple :dbConnection Child then I get

--->   "No such method 'CloseConnection'

And when I use a single :dbConnection Child, then I get

--->   "Not allowed to change the 'ConnectionString' property. The connection's current state is open"

The goal is that I want to be able fetch values from the databse columns into Rules by using column names; for example

(in the Design unitLayout )

Rule energyWheelNomenclature As String = rsUnitSpecs.getFieldValue (0, "EnergyRecoveryWheel")

(in the Child enthalpyWheel )

Rule energyRecoveryWheelDiam As Number = rsEnergyRecoveryWheel.getFieldValue (0, "Diameter")

I tried to use :dbQueryCommand, but the result set is a list.

Thanks for your help.

Wajih

Here is layout of my code (when I use a single :dbConnection Child)

Design databaseConnection : Methods

   'Create DB connection.

   Child ETOdbConnection As (If root? Then :dbConnection Else :nullDesign)

             dataProvider = :SQLSERVER

      connectionString = "Data Source=" & DataSourceName & ";Initial Catalog=" & InitialCatalogName & ";Integrated Security=True"

      onNullReturn = False

      showInModelBrowser? = True

   End Child

End Design

____________________________________________

Design unitLayout : VHC72_IntentModelRoot databaseConnection IvAssemblyDocument

   Parameter Rule SalesOrderNo As String

      SalesOrderNo = "02731904"

   End Rule

   Child rsUnitSpecs As :dbDatatable

           connection = root.ETOdbConnection

     TableName = "[VHC_OrderGuide]"

     SelectCommandText = "Select * From [VHC_OrderGuide] Where [SalesOrder] = '" & SalesOrderNo & "'"

     showInModelBrowser? = True

   End Child

   Child enthalpyWheel As :M00_EnthalpyWheel

   End Child

End Design

____________________________________________

Design M00_EnthalpyWheel : M00_UnitLayout IvAssemblyDocument

   Parameter Rule energyRecoveryWheel As String = "A"

   Child rsEnergyRecoveryWheel As :dbDataTable

            connection = root.ETOdbConnection

      TableName = "[VHC72SQL].[dbo].[Wheel]"

      SelectCommandText = "Select * From [VHC72SQL].[dbo].[Wheel] WHERE [VHC72SQL].[dbo].[Wheel].[Nomenclature] = '" & energyRecoveryWheel & "'"

      showInModelBrowser? = True

   End Child

End Design

____________________________________________

[wayne.brill]

Hi,

There is this Ticket logged with engineering that seems related to the behavior you are getting:
"ADO.Net Designs assume the dbDataTable Parts should be children of dbConnection"

Also this is documented on this help topic:

http://help.autodesk.com/view/ETOCOMP/2015/ENU/?guid=GUID-ABDAD4BB-ABCB-41B0-92DE-12E4CCB75E8D

Here is a reply I had to an previous case with a similar issue:

>> >>

In my tests if the dbDataTable is a Child of my dbConnection the error does not occur.

<< <<

If you think a different problem is occurring please attach a small dataset that will allow me to recreate the behavior. (you can email to wayne.brill@autodesk.com if you do not want to post it here)

Thanks,

Wayne

[WHassan]

Hi Wayne,

I have attached a sample dataset for two tables (i.e. i. VHC_OrderGuide and ii. Wheel) for you you test. They are on an SQL database named VHC72SQL (as you see in the SQL statemements in my previous posts).

Here are some things I want to ask you;

1. Can you elaborate on the "dbDataTable Parts should be children of dbConnection" ? What I tried to do it to put the following Children in the body of the same design (i.e. 'databaseConnection')

      a)  Child :dbConnection

      b)  Child :dataTable (for table = VHC_OrderGuide)

      c)  Child :dataTable (for table = Wheel)

      So as I was adding these children one after the other (and also calling other children which reference these tables using method GetFieldValue( ) ). They worked fine, until I close the Inventor and loaded all the Assembly/Design. Then I got the same message "Not allowed to change the 'ConnectionString' property. The connection's current state is open."

2. What I am trying to get is a result of a query along with the Column Names.

3. I tried to create a two-dimensional array/list containing the Column names and the field values by running the following

     a)   Child :dbQueryCommand (see the entire child below)

     b)   Rule ETOdbConnection.GetColumnNames("VHC_OrderGuide")

     But I can't because, the list of the column names is in a different sequence than the sequence of the field values retrieved by the dbQueryCommand (see below for more) (notice that the columns 'ServiceLights', 'ServiceLightsPowerSouce' and more are not in the sequence of the the results of the query). Could you hint/explain what could be behind this?

Thanks,

Wajih

Here is the snippet for the dbQueryCommand (please refer to my previous post for the same Child for :dbConnection )

______________________________________

Child qryUnitSpecs As :dbQueryCommand

            connection = ETOdbConnection

           commandType = :Statement

     CommandText = "Select * From [VHC72SQL].[dbo].[VHC_OrderGuide] Where [VHC72SQL].[dbo].[VHC_OrderGuide].[SalesOrder] = '" & "13" & "'"

     showInModelBrowser? = True

End Child

ETO Immediate Window Results

Intent >qryUnitSpecs.ResultSet

--> {{784.0, "13", 8000.0, 8000.0, 1.0, 8000.0, 1.0, 0.0, 8000.0, False, "P", "P", 0.0, "1", False, "F", "Y", "F", "R", "O", "X", "X", "O", "G", "S", "E", "X", "H", "S", "F", "F", "R", "V", False, "ATLI 18-13 T2", "D", "1", "P", "ANPA25", "B", "2", "B", "X", "B", "B", "X", "X", "X", "X", "O", "X", "R", "X", "1", "1", "B", "B", "X", "600", "2", "F", "G", "4", "2", "B", "E", "B", "E", "B", "I", "B", "E", "B", "J", "P", "X", "S", 100.0, "P", "X", "X", "X", "X", "X", "T", False, "X", "2", "C", "X", "X", "X", "X", "X", "1", "2", "3", "254T", "184", False, False, False, False, False}}

Intent >ETOdbConnection.GetColumnNames("VHC_OrderGuide")

--> {"ID", "SalesOrder", "ServiceLights", "ServiceLightPowerSource", "OutsideAirDamper", "ExhaustAirDamper", "Heating", "ElectricPostheatCapacity", "ElectricPostHeatPowerConnect", "IndirectGasCapacity", "IndirectGasType", "IndirectGasTurndown", "IndirectGasTubeMaterial", "HotWaterRows", "HotWaterConnections", "HotWaterLocation", "SteamConnections", "SteamLocation", "Cooling", "ChilledWaterRows", "ChilledWaterConnections", "IntegratedCoolingType", "IntegratedCoolingTonnages", "IntegratedCoolingCompressors", "HotGasBypassModulation", "SuctionAndDischargeValveKit", "FlowRateGPM", "Integrated_HotGasReheat", "HeadPressureControl", "WSHPWiseCoil", "OnOffControlValve", "WaterBalancingValve", "StrainerWithBlowDownValve", "WSHPFreezeProtection", "WSHPFluidConnections", "SplitDxCoilConnection", "SplitHotGasReheat", "ControlPanel", "AdditionalVFDControl", "MorningWarmUp", "NightSetback", "ControlPanelHeater", "GFIOutlet", "GFIPowerSource", "CommunicationProtocol", "BaudRate", "supplyMotorFrame", "exhaustMotorFrame", "supplyMotorHP", "exhaustMotorHP", "ExhaustFanRPM", "RowProcessed", "Insulation_Qty", "TotalSupplyAir", "OAAirFlow", "OA_ESP", "TotalReturnAir", "RA_ESP", "MixedAir", "ExhaustAir", "Elevation", "Voltage", "Defrost", "ElectricPreheatCapacity", "ElectricPreHeatPowerConnect", "UnoccupiedRecirc", "EnergyRecoveryWheel", "EnergyRecoveryPurge", "DirtyFilterContacts", "WheelRotationSensor", "WaterOverflowSwitch", "DemandControlVentilation", "SupplyExhaustMotorCurrentSensor", "UnitEnvironment", "ExternalFinish", "UnderFloorLiner", "OutsideAirIntake", "SideOutsideAirLocation", "OutsideAirConnection", "ExhaustDischarge", "SideExhaustAirLocation", "ExhaustBlowerType", "ExhaustBlowerIsolation", "ExhaustFanControl", "ExhaustAirConnection", "ExhaustBlowerModel", "SupplyDischarge", "SASafetyGrating", "SupplyBlowerType", "SupplyBlowerModel", "ReturnAir", "RASafetyGrating", "SideReturnAirLocation", "MixedAirDamper", "SupplyFiltration", "ExhaustFiltration", "MixedAirFiltration", "AdditionalSupplyFiltration", "EconomizerEnergyRecoveryWheel", "SupplyExhaustAirBypass", "OutsideAirHumiditySensor", "SupplyAirHumiditySensor", "ReturnAirHumiditySensor", "ExhaustAirHumiditySensor"}

Intent >