iLogic linked to SQL but only last item is imported to database

[justinparfitt]

Hello,

Could you please help me out with additional code to pick up all occurrences from BOM to be linked to SQL database.

At the moment only last component is imported to SQL table.

Thanks. 

AddReference "System.Data"
AddReference "System.Core"
AddReference "System.Xml"
Imports System.Data.OleDb
Imports System.Data
Imports System.Xml



Sub main
' set a reference to the assembly component definintion.
' This assumes an assembly document is open.
'----------change BOM normal to phantom for parts with description NOT ON BOM
Dim oAsmCompDef As AssemblyComponentDefinition
oAsmCompDef = ThisApplication.ActiveDocument.ComponentDefinition

'Iterate through all of the occurrences

Dim CompOcc As ComponentOccurrence
For Each CompOcc In oAsmCompDef.Occurrences

If CompOcc.Definition.Document.PropertySets.Item("Design Tracking Properties").Item("Description").Value = "NOT ON BOM" Then
CompOcc.Definition.BOMStructure = 51971 'Phantom
Else
End If
Next


oDoc = ThisApplication.ActiveDocument
Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM
'==========================================================================================
'You can change the output path by editing oPATH below
oPATH = PROJECT_PATH & JOB_NO & " - " & CLIENT & " - " & SITE & "\" 'If you change this, remember to keep a \ at the end

'==========================================================================================
'STRUCTURED BoM ===========================================================================
' the structured view to 'all levels'
oBOM.StructuredViewFirstLevelOnly = False
' Make sure that the structured view is enabled.
oBOM.StructuredViewEnabled = True
Dim oStructuredBOMView As BOMView
oStructuredBOMView = oBOM.BOMViews.Item("Structured")
' Export the BOM view to an Excel file
oStructuredBOMView.Export(oPATH + ThisDoc.FileName(False) + ".xls", kMicrosoftExcelFormat)

'==========================================================================================
'PARTS ONLY BoM ===========================================================================
'' Make sure that the parts only view is enabled.
'oBOM.PartsOnlyViewEnabled = True
'Dim oPartsOnlyBOMView As BOMView
'oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")
'' Export the BOM view to an Excel file
'oPartsOnlyBOMView.Export (oPATH + "BOM-PartsOnly.xls", kMicrosoftExcelFormat)
'==========================================================================================
'BOM to SQL
Dim row As Integer = 5
row = 5
Dim oBOMView As BOMView
oBOMView = oBOM.BOMViews.Item("Structured")

Dim PartNumber, Qty, StockNumber, Description As String
'Dim bRow As bomRow
bRows = oBOMView.BOMRows
For Each bRow In bRows

Dim rDoc As Document
rDoc = bRow.ComponentDefinitions.Item(1).Document

Dim docPropertySet As PropertySet
docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")

StockNumber = bRow.ItemNumber
Qty = bRow.ItemQuantity
Description = docPropertySet.Item("Description").Value
PartNumber = docPropertySet.Item("Part Number").Value
row = row + 1
Next

Dim sConn As String = String.Empty
sConn="Provider=SQLOLEDB;Data Source=XXXXX;Initial Catalog=XXXXX;User ID=XXXXXXX;Password=XXXXXX;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"

'Enter Tablename and values to insert
'If there are less values than columns in your table, you need to specify wich value corresponds to which column
Dim sql As String = String.Empty
sql = "INSERT INTO InventorBOM1 VALUES ('"& JOB_NO &"','"& PROJECT_ID &"','"& PartNumber &"','"& Qty &"','"& Description &"','"& StockNumber &"')"
If ExtSql(sConn, sql) Then
MsgBox("Command executed", MsgBoxStyle.Information, "iLogic")
End If
End Sub


Function ExtSql(ByVal sConn As String, ByVal sql As String) As Boolean
Dim cnn As OleDbConnection
Dim cmd As OleDbCommand
cnn = New OleDbConnection(sConn)
Try
cnn.Open()
cmd = New OleDbCommand
cmd.Connection = cnn
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = sql
cmd.ExecuteNonQuery()
cnn.Close()
cmd.Dispose()
Catch ex As Exception
cnn.Close()
Return False
End Try
Return True
End Function

[A.Acheson]

The for loop retrieving the iproperty values does not contain the function call for the SQL database. Move all items relating to the function "ExtSql(sConn, sql)" 

For Each bRow In bRows
    ' ..... iProperties
    ExtSql(sConn, sql)
Next

[justinparfitt]

Hi,

Thank you for the pointing me in the correct direction.

I have moved the ExtSql(sConn, sql) to the loop as code below, and it is importing correct qty of components(on BOM showed 3 items, and sql is showing 3 items (job no and door ref), but there is no description, qty, stocknumber and partnumber. So, it looks like it is not picking up the string.  Any ideas on this one? Thanks...

'BOM to SQL
Dim PartNumber, Qty, StockNumber, Description As String
Dim sConn As String = String.Empty
	sConn="Provider=SQLOLEDB;Data Source=XXX;Initial Catalog=XXX;User ID=XXX;Password=XXX;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"

	'Enter Tablename and values to insert
	'If there are less values than columns in your table, you need to specify wich value corresponds to which column
	Dim sql As String = String.Empty
	sql = "INSERT INTO InventorBOM1 (JOB_NO,PROJECT_ID,ITEM,Qty,Description,StockNumber) VALUES ('"& JOB_NO &"','"& PROJECT_ID &"','"& PartNumber &"','"& Qty &"','"& Description &"','"& StockNumber &"')"
	
Dim row As Integer = 5
row = 5
Dim oBOMView As BOMView
oBOMView = oBOM.BOMViews.Item("Structured")


Dim bRow As BOMRow
bRows = oBOMView.BOMRows
'For each row in the Structured BOM, do the following
For Each bRow In bRows
'Set a reference to the primary ComponentDefinition of the row
Dim rDoc As Document
rDoc = bRow.ComponentDefinitions.Item(1).Document

Dim docPropertySet As PropertySet
docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")

StockNumber = bRow.ItemNumber
Qty = bRow.ItemQuantity
Description = docPropertySet.Item("Description").Value
PartNumber = docPropertySet.Item("Part Number").Value


row = row + 1
ExtSql(sConn, sql)
Next
MsgBox("Command executed", MsgBoxStyle.Information, "iLogic")
	

[A.Acheson · Accepted solution]

It might be good practice to place "Option Explicit On" line in the header section of the code. This will help identify more issues. The sql string variable is being set before you have any string iproperty placed into it.  That should be located after the iproperty strings are created and before it is passed into the sql function. 

[justinparfitt · Accepted solution]

Thank you very much for your help. Below is the final code.

Sub main
' set a reference to the assembly component definintion.
' This assumes an assembly document is open.
'----------change BOM normal to phantom for parts with description NOT ON BOM  
Dim oAsmCompDef As AssemblyComponentDefinition
oAsmCompDef = ThisApplication.ActiveDocument.ComponentDefinition

'Iterate through all of the occurrences

Dim CompOcc As ComponentOccurrence
For Each CompOcc In oAsmCompDef.Occurrences

If CompOcc.Definition.Document.PropertySets.Item("Design Tracking Properties").Item("Description").Value = "NOT ON BOM" Then
CompOcc.Definition.BOMStructure = 51971 'Phantom
Else
End If
Next


oDoc = ThisApplication.ActiveDocument
Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM
'==========================================================================================
'You can change the output path by editing oPATH below
oPATH = PROJECT_PATH & JOB_NO & " - " & CLIENT & " - " & SITE & "\" 'If you change this, remember to keep a \ at the end

'==========================================================================================
'STRUCTURED BoM ===========================================================================
' the structured view to 'all levels'
oBOM.StructuredViewFirstLevelOnly = False
' Make sure that the structured view is enabled.
oBOM.StructuredViewEnabled = True
Dim oStructuredBOMView As BOMView
oStructuredBOMView = oBOM.BOMViews.Item("Structured")
' Export the BOM view to an Excel file
oStructuredBOMView.Export(oPATH + ThisDoc.FileName(False) + ".xls", kMicrosoftExcelFormat)






'==========================================================================================
'PARTS ONLY BoM ===========================================================================
'' Make sure that the parts only view is enabled.
'oBOM.PartsOnlyViewEnabled = True
'Dim oPartsOnlyBOMView As BOMView
'oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")
'' Export the BOM view to an Excel file
'oPartsOnlyBOMView.Export (oPATH + "BOM-PartsOnly.xls", kMicrosoftExcelFormat)
'==========================================================================================
'BOM to SQL
Dim row As Integer = 5
row = 5
Dim oBOMView As BOMView
oBOMView = oBOM.BOMViews.Item("Structured")

Dim PartNumber, Qty, StockNumber, Description As String
Dim bRow As BOMRow
bRows = oBOMView.BOMRows
	For Each bRow In bRows
	
	Dim rDoc As Document
	rDoc = bRow.ComponentDefinitions.Item(1).Document
	
	Dim docPropertySet As PropertySet
	docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")
	
	StockNumber = bRow.ItemNumber
	Qty = bRow.ItemQuantity
	Description = docPropertySet.Item("Description").Value
	PartNumber = docPropertySet.Item("Part Number").Value
	row = row + 1
	Dim sConn As String = String.Empty
	sConn="Provider=SQLOLEDB;Data Source=xxxxx;Initial Catalog=xxxx;User ID=xxxxx;Password=xxxx;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"

	'Enter Tablename and values to insert
	'If there are less values than columns in your table, you need to specify wich value corresponds to which column
	Dim sql As String = String.Empty
	sql = "INSERT INTO InventorBOM1 (JOB_NO,PROJECT_ID,ITEM,Qty,Description,StockNumber) VALUES ('"& JOB_NO &"','"& PROJECT_ID &"','"& PartNumber &"','"& Qty &"','"& Description &"','"& StockNumber &"')"
	
	ExtSql(sConn, sql) 
		MsgBox("Command executed", MsgBoxStyle.Information, "iLogic")
	Next

	
	
	
End Sub


Function ExtSql(ByVal sConn As String, ByVal sql As String) As Boolean
    Dim cnn As OleDbConnection
    Dim cmd As OleDbCommand
    cnn = New OleDbConnection(sConn)
    Try
        cnn.Open()
        cmd = New OleDbCommand
        cmd.Connection = cnn
        cmd.CommandType = System.Data.CommandType.Text
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        cnn.Close()
        cmd.Dispose()
    Catch ex As Exception
        cnn.Close()
        Return False
    End Try
    Return True
End Function