How-to get ilogic to record data, search and fetch data from a database
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