How-to get ilogic to record data, search and fetch data from a database

[Anonymous]

Hello interwebs,

As the title suggests the goal is to use Inventor forms and some ilogic to record data, search and fetch data from an access database.

This recording, searching and fetching data in a database can benefit users of this forum who're searching for that all-in-one solution to database integration in their models and drawings. With some modification to the code users can adjust the code to their needs.

The iLogic used in this example is sourced from the code developed by Chandra Gopal on the Manufactures Development Blog (source: https://adndevblog.typepad.com/manufacturing/2017/08/connecting-microsoft-access-via-inventor-ilogic...)

And using the database supplied EmployeeInfo.accdb

AddReference "System.Data"
AddReference "System.Core"
AddReference "System.Xml"

Imports System.Data.OleDb
Imports System.Data
Imports System.Xml


Sub Main()

Dim Table_ As String = "EmployeeInfo"
Dim query As String = "SELECT * FROM " & Table_
Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\EmployeeInfo.accdb;Persist Security Info=False;"
Dim ds As New DataSet
Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
cnn.Open()
Dim cmd As New OleDbCommand(query, cnn)
Dim da As New OleDbDataAdapter(cmd)
da.Fill(ds, Table_)
cnn.Close()
Dim t1 As DataTable = ds.Tables(Table_)
Dim row As DataRow
Dim Item(2) As String
For Each row In t1.Rows
MessageBox.Show("EID : " & row(0) & " and Employee Name : " & row(1)
Next
End Sub

I would like to use the supplied code by modifying it to perform the following functions:

Read and write to the database
Read and retrieve data from the database
Perform the above tasks using Forms in Inventor and some iLogic/VB

The setup so far...

I’ve created the following parameters in inventor:

EID
ENAME
DOB
Designation

I've also created a form for users to interact with the Database.

The save button uses the following iLogic code:

 Sub Main ()
	Dim Table_ As String = "EmployeeInfo"
	Dim query As String = "SELECT * FROM " & Table_
	Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo.accdb;Persist Security Info=False;")
	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo.accdb;Persist Security Info=False;"
	Dim ds As New DataSet
	Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
	con.Open()
	Dim cmd As New OleDbCommand(query, cnn)
	Dim adp As New OleDbDataAdapter(cmd)
    Dim ds12 As DataSet
    ds12 = New DataSet
	adp.Fill(ds12, "EmployeeInfo")
	
		'DETERMINE THE NEXT AVAILABLE ROW IN ACCESS
		If ds12.Tables("EmployeeInfo").Rows.Count = 0 Then
			i = i + 1
      	Else
		   query = "SELECT MAX(EID) from EmployeeInfo"
           cmd = New OleDbCommand(query, con)
           adp = New OleDbDataAdapter(cmd)
           ds12 = New DataSet
           adp.Fill(ds12, "EmployeeInfo")
           i = (ds12.Tables(0).Rows(0).Item(0)) + 1
           con.Close()
       End If
	   
	   'VERIFY THE ABOVE CODE
		'MessageBox.Show(i)
		
'		query = "SELECT i from EmployeeInfo"
		query = "UPDATE EmployeeInfo EID='@EID', ENAME='@ENAME', DOB='@DOB', Designation='@Designation'"
'		query = "INSERT Into EmployeeInfo ([EID], [ENAME], [DOB], [Designation])" & "VALUES " & "(@EID, @ENAME, @Anonymous, @Designation)"	
        cmd = New OleDbCommand(query, con)
'		cmd = New OleDb.OleDbCommand("UPDATE EmployeeInfo ([EID], [ENAME], [DOB], [Designation] VALUES (@EID, @ENAME, @Anonymous, @Designation)", con)
        adp = New OleDbDataAdapter(cmd)		
		ds01 = New DataSet
        adp.Fill(ds12, "EmployeeInfo")
		
        cmd.Parameters.AddWithValue("@EID", Parameter ("EID"))
        cmd.Parameters.AddWithValue("@ENAME", Parameter ("ENAME"))
        cmd.Parameters.AddWithValue("@DOB", Parameter ("DOB"))
        cmd.Parameters.AddWithValue("@Designation", Parameter ("Designation"))
            
            con.Open()
'            Try Catch
'                cmd.ExecuteNonQuery()
'                MsgBox("Record Appended", MsgBoxStyle.Information, "Successfully Added!")
'			End Try
   End Sub

And the search button uses the following code (also doesn't work):

AddReference "System.Data"
AddReference "System.Core"
AddReference "System.Xml"
Imports System.Data.OleDb
Imports System.Data
Imports System.Xml
Sub Main()

	Dim Table_ As String = "EmployeeInfo"
	Dim query As String = "SELECT * FROM " & Table_
	Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo.accdb;Persist Security Info=False;")
	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo.accdb;Persist Security Info=False;"
	Dim ds As New DataSet
	Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
	con.Open()
	Dim cmd As New OleDbCommand(query, cnn)
	Dim adp As New OleDbDataAdapter(cmd)
    Dim ds12 As DataSet
    ds12 = New DataSet
	adp.Fill(ds12, "EmployeeInfo")

'Function CreateSQLConnection() As ADODB.Connection

'Dim sProvider As String
'Dim sServer As String
'Dim sCatalog As String
'Dim sUser As String
'Dim sPassword As String

' Create a connection object.
'Dim cnDB As ADODB.Connection
'Set cnDB = New ADODB.Connection

'Dim rs As ADODB.Recordset
'Set rs = New ADODB.Recordset

' Provide the connection string.
'Dim strConn As String
'Set CreateSQLConnection = con


'*********************************************************************
'********************* Search Code to Follow ************************
'*********************************************************************

'Dim RecordTable As String 
Dim ColumntoSearch As String
Dim KeyValue As Integer
Dim ColumntoRead As String

 

RecordTable = "EmployeeInfo"                   'Name of Record Table 
ColumntoSearch = "EID"          'Column within the table you want to search for the key value
KeyValue = (Parameter("EID"))		'KeyValue to search the Column for
ColumntoRead = "ENAME"             'Actual Column you'd like to get the value you're looking for from
ColumntoRead = "DOB"             'Actual Column you'd like to get the value you're looking for from
ColumntoRead = "Designation"             'Actual Column you'd like to get the value you're looking for from


''Open _________ Record Table to Search
'ds.Open Source: = Table_, ActiveConnection:=con, CursorType:=adOpenKeyset, LockType:=adLockOptimistic, Options:=adCmdTableDirect

 

'Find Item in _____ Column with ____ Value 
ds.query ColumntoSearch & "=" & KeyValue, 0, adSearchForward, 0

 

'Display Value in ____ Column
MsgBox (ds.Fields(ColumntoRead).Value)

 

ds.Close
con.Close

 

Set rs = Nothing
Set cnDB = Nothing

 

End Function

 

 

-------------------------- End Of Code -------------------

End Sub

'Sub Main()

''	Dim Tabla As String = "CAD"
'	Dim query As String = "SELECT * FROM CAD WHERE PartNo =  '" & MT_NAMING & "'"
'	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DESKTOP-VDT88EI\database\Ingenieria.accdb;Persist Security Info=False;"
'	Dim ds As New DataSet
'	Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
'	cnn.Open()
'	Dim cmd As New OleDbCommand(query, cnn)
'	Dim da As New OleDbDataAdapter(cmd)
'	da.Fill(ds, "CAD")
'	cnn.Close()
'	Dim **** = ds.Tables("CAD").rows.count

'If **** > 0 Then
'	MessageBox.Show(**** & "This Part Number is already added to the database!", MT_NAMING )
'Else 
	

'Dim OPROJ As String
'Dim oCREAT As String
'Dim oDAT As Date
'Dim oCOMENT As String
'Dim oSTATUS As String
'oNAME = MT_NAMING
'OPROJ = iProperties.Value("Project", "Project")
'oDAT = iProperties.Value("Project", "Creation Date")
'oCREAT = ThisApplication.GeneralOptions.UserName
'oCOMENT = DESCRIPCION
'oSTATUS = STATUS_MASTER	

'    Dim Table_ As String = "CAD"
'    Dim query1 As String = "INSERT INTO " & Table_ & "(Proyect, PartNo, AutorDePieza, FechaCreacion, DescripcionDePieza,Estado) VALUES ('"&iProperties.Value("Project", "Project")&"','"& MT_NAMING &"', '"& oCREAT &"', '"& oDAT &"', '"& oCOMENT &"', '"& STATUS_MASTER &"')"
'    Dim MDBConnString_1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DESKTOP-VDT88EI\database\Ingenieria.accdb;Persist Security Info=False;"
'    Dim ds1 As New DataSet
'    Dim cnn1 As OleDbConnection = New OleDbConnection(MDBConnString_1)
'    cnn1.Open()
'    Dim cmd1 As New OleDbCommand
'    cmd.Connection = cnn1
'    cmd.CommandText = query1
'    cmd.ExecuteNonQuery()
'    cnn1.Close()
'End If
'End Sub

I've been stuck on solving the first button one (had it working before) but now I can't seem to get it working again.

I've begun the code part of the Search button but still couldn't get it to work.

Can someone in this forum chime in and get this up and running?

Please see attached files

[Anonymous]

Attached is the form I use to communicate with the database.

To use the attached xml files; you must copy all the xml files to the following directory:

C:\Users\Public\Documents\Autodesk\Inventor 2020\Design Data\iLogic\UI\

The above form will appear in Global Forms

Also attached with code and form I've used for the project is an empty assembly file under the name SAS-00XX-XX.iam

Inside the file you will find the ilogic code and form.

Russell

[Anonymous]

Trying something new using DataRow and Rows.Add

AddReference "System.Data"
AddReference "System.Core"
AddReference "System.Xml"
Imports System.Data.OleDb
Imports System.Data
Imports System.Xml
 Sub Main()
	Dim Table_ As String = "EmployeeInfo"
	Dim query As String = "SELECT * FROM " & Table_
	Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo (1)\EmployeeInfo.accdb;Persist Security Info=False;")
	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo (1)\EmployeeInfo.accdb;Persist Security Info=False;"
	Dim ds As New DataSet
	Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
	con.Open()
	Dim cmd As New OleDbCommand(query, cnn)
	Dim adp As New OleDbDataAdapter(cmd)
    Dim ds12 As DataSet
    ds12 = New DataSet
	adp.Fill(ds12, "EmployeeInfo")
	
		'DETERMINE THE NEXT AVAILABLE ROW IN ACCESS
		If ds12.Tables("EmployeeInfo").Rows.Count = 0 Then
			i = i + 1
      	Else
		query = "SELECT MAX(EID) from [EmployeeInfo]"
           cmd = New OleDbCommand(query, con)
           adp = New OleDbDataAdapter(cmd)
           ds12 = New DataSet
           adp.Fill(ds12, "EmployeeInfo")
           i = (ds12.Tables(0).Rows(0).Item(0)) + 2
           con.Close()
       End If
	   
	   'VERIFY THE ABOVE CODE
		MessageBox.Show(i)
		query = "SELECT MAX(EID) from [EmployeeInfo]"
'		query = "UPDATE EmployeeInfo SET EID='" & "i"  & "', ENAME='" & ENAME & "', DOB='" & DOB & "', Designation='" & Designation
'		query = "UPDATE EmployeeInfo SET EID='" & EID & "', ENAME='" & ENAME & "', DOB='" & DOB & "', Designation='" & Designation & "' WHERE EID=" & "'i'"
        cmd = New OleDbCommand(query, con)
		adp = New OleDbDataAdapter(cmd)
		ds01 = New DataSet
        adp.Fill(ds12, "EmployeeInfo")
		
		Dim newEmployeeRow As DataRow = ds01.Tables("EmployeeInfo").NewRow()
		newEmployeeRow = ds01.Tables("EmployeeInfo").NewRow()
		newEmployeeRow("EID") = Parameter ("EID")
		newEmployeeRow("ENAME") = Parameter ("ENAME")
		newEmployeeRow("DOB") = Parameter("DOB")
		newEmployeeRow("Designation") = Parameter("Designation")
		
		ds01.Tables("EmployeeInfo").Rows.Add(newEmployeeRow)

            con.Open()
'            Try Catch ex As Exception
'                cmd.ExecuteNonQuery()
                MsgBox("Record Appended", MsgBoxStyle.Information, "Successfully Added!")
'			End Try
   End Sub

I still doesn't work. Gonna try again tomorrow.

[Anonymous]

Finally figured out the iLogic code and it WORKS!

Data entered from an Inventor Form (or parameters) can now be entered into a database.

Below we enter the new employees name (Jona).

iLogic code - Retrieve inventor parameters and enters it into a database

AddReference "System.Data"
AddReference "System.Core"
AddReference "System.Xml"
Imports System.Data.OleDb
Imports System.Data
Imports System.Xml
 Sub Main ()
	Dim Table_ As String = "EmployeeInfo"
	Dim query As String = "INSERT INTO EmployeeInfo (EID, ENAME, DOB, Designation) VALUES ('" & EID & "','" & ENAME & "','" & DOB & "','" & Designation & "' );"
	Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo (1)\EmployeeInfo.accdb;Persist Security Info=False;")
	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo (1)\EmployeeInfo.accdb;Persist Security Info=False;"
	Dim ds As New DataSet
	Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
	con.Open()
	Dim cmd As New OleDbCommand(query, cnn)
	Dim adp As New OleDbDataAdapter(cmd)
    Dim ds12 As DataSet
    ds12 = New DataSet
	adp.Fill(ds12, "EmployeeInfo")
	
cmd.Parameters.AddWithValue("EID", Parameter ("EID"))
cmd.Parameters.AddWithValue("ENAME", Parameter ("ENAME"))
cmd.Parameters.AddWithValue("DOB", Parameter ("DOB"))
cmd.Parameters.AddWithValue("Designation", Parameter ("Designation"))

   End Sub

Access Table with new Employee (Jona)

Next we need to retrieve info from a database and update the parameters in Inventor...

[mcgyvr]

Just tagging along so I can "borrow" from your hard work once you get a full working CRUD example completed..

I'd love to be able to just do a quick pull and populate of iprops from our ERP system but haven't spent the time to site down and figure it all out nor do I want to pony up for the $$$ premade addins.. 

[Anonymous]

So far the search and retrieve from a database code looks like this:

AddReference "System.Data"
AddReference "System.Core"
AddReference "System.Xml"
Imports System.Data.OleDb
Imports System.Data
Imports System.Xml
Sub Main
	Dim Table_ As String = "EmployeeInfo"
	Dim query As String = "SELECT * FROM EmployeeInfo where EID= " & Parameter ("EID") & " "
	Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo (1)\EmployeeInfo.accdb;Persist Security Info=False;")
	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo (1)\EmployeeInfo.accdb;Persist Security Info=False;"
	Dim ds As New DataSet
	Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
	con.Open()
	Dim cmd As New OleDbCommand(query, cnn)
	Dim adp As New OleDbDataAdapter(cmd)
	Dim ds12 As DataSet
	ds12 = New DataSet
	adp.Fill(ds12, "EmployeeInfo")
	StartRow = 1

cmd.Parameters.AddWithValue(Parameter ("ENAME"), "ENAME")
cmd.Parameters.AddWithValue(Parameter ("DOB"), "DOB")
cmd.Parameters.AddWithValue(Parameter ("Designation"), "Designation")

End Sub

No errors... but its NOT updating the parameters. Why?

Above is the table in access with the list of employees...

Above is the Inventor Form that calls the database but it doesn't update the grey-out fields.... why? Gonna try to figure this out.

[Anonymous]

Update on the search code. 

AddReference "System.Data"
AddReference "System.Core"
AddReference "System.Xml"
Imports System.Data.OleDb
Imports System.Data
Imports System.Xml
Sub Main
	Dim Table_ As String = "EmployeeInfo"
	Dim query As String = "SELECT * FROM EmployeeInfo where EID= " & Parameter ("EID") & " "
	Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo (1)\EmployeeInfo.accdb;Persist Security Info=False;")
	Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo (1)\EmployeeInfo.accdb;Persist Security Info=False;"
	Dim ds As New DataSet
	Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
	con.Open()
	Dim cmd As New OleDbCommand(query, cnn)
	Dim adp As New OleDbDataAdapter(cmd)
	Dim ds12 As DataSet
	ds12 = New DataSet
	adp.Fill(ds12, "EmployeeInfo")
	StartRow = 1
	
Parameter("ENAME") = cmd.Parameters.Contains("@ENAME")
Parameter("DOB") = cmd.Parameters.Contains("DOB")
Parameter("Designation") = cmd.Parameters.Contains("@Designation")

End Sub

The above code doesn't show errors and it kind of works see Inventor forum below; but it still not showing the desired results. Instead of showing employee information it compares the parameters returning with the results shown below.

So it's almost there. Gonna try again tomorrow.

[chandra.shekar.g]

@Anonymous,

That's really nice to know that you have succeeded in this project.

Thanks and regards,

[Anonymous]

It's important to note that the code above works but requires much improvement before being published in a work environment. I'm planning on using this thread to build upon the code as I continue my pursuit of learning more about VB and communication with a database.

The version of inventor that I was working on was 2020 but the IT has since upgraded to 2021. The above code no longer works and requires a possible fresh install of inventor 2020 with backups of the original 2020 projects, ilogic code and forms to get it running again.

I've exhausted the internet looking for solutions and have tried the following:

Changing the version from...

MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0

to...

MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.16.0

...works but the data no longer pushes data to access and the search feature no longer pulls data from access.

Not all is lost...

Other apps I wrote in iLogic do work but global forms that I've made need to be manually be placed into its 2021 folder to appear.

Saving iLogic projects in 2021 don't appear in 2020 (with update) and iLogic codes developed in 2020 and saved in 2021 no longer appears in the 2020 version when opened.

With that said, iLogic codes developed in 2020 that reference and use Excel, Word and Outlook do work in 2021.

So a short term solution to the above problem would be using excel as a platform of pushing and pulling data from a database and inventor (instead of a direct path from inventor to a database).

[WCrihfield]

Hindsight is usually 20/20, I know, but in the future, I highly recommend using folders outside of the Inventor install path, and outside of the Users AppData path, such as on a network drive, or in your MyDocuments structure.  Then backing them up onto external storage from time to time.  There's a lot of stuff to prepare, export, and save off before upgrading to a newer version of Inventor, and a lot of stuff to set back up, import, and load again afterwords.  It is sometimes hard to keep it all straight.  I went through the upgrade process several times before I was able to remember what things I had to do (or not do) to avoid tons of reconstructive set-up work afterwards.  One thing I used to do was leave the old version installed until after I had the new version up and running smoothly, before uninstalling it, just so I could reference it for whatever reason.

There's a bunch of documentation on the internet (not much within this forum) about working with MS Access from it's VBA Editor environment, so hopefully you can use some of that for some pointers on patching your connection with it up.  I have used similar forum type postings about Excel to learn more about working with it from Inventor.