Connect to SQL or Microsoft Access from iLogic

[jparks_79]

Hello Everyone,

I am trying to query a SQL database from iLogic with Inventor 2016. I have written some code in VB.net which seems to work. I can create the connection and query a table. But copy and paste of this code doesn't work directly in iLogic.

Does any one have any recent experience? I found a couple links on the forum mentioning to add reference to Microsoft ActiveX Data Objects 2.8 Library, but I am not sure how this reference can be added in ilogic. Here is the link i am reffering to.Link to post

Here is what I have so far and the result error and warning. Any advice would be appreciated.

        Dim Connection As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=SQLNCLI11;Data Source=#####;Persist Security Info=True;User ID=###;PWD=###;Initial Catalog=###")
        Dim Command As New OleDb.OleDbCommand
        Dim ds As New DataSet
        Dim da As New OleDb.OleDbDataAdapter
        'Dim rowo As System.Data.DataRow
        'Dim Count As Integer
        Connection.Open()  'open up a connection to the database
        Command.Connection = Connection

		

        Dim partnum As String = "xxx-xxxxxxxx"
        Dim query As String = "Select SpringSAPPN FROM Springs        WHERE  SpringGSPN ='" + partnum + "' "
        da.SelectCommand = New OleDb.OleDbCommand(query, Connection)


        da.Fill(ds, "Table") 'Fill the dataset, ds, with the above SELECT statement
        'Count = ds.Tables("Table").Rows.Count

        MessageBox.Show(ds.Tables(0).Rows(0)(0).ToString)

[MechMachineMan]

Google is a wonderful thing:

https://knowledge.autodesk.com/support/inventor-products/learn-explore/caas/CloudHelp/cloudhelp/2015...

[chandra.shekar.g]

Hi @jparks_79,

Try to add following reference and Imports on top of iLogic rule.

AddReference "ADODB.dll"

Imports System.Core
Imports ADODB
Imports System.Linq
Imports System.Windows.Controls
Imports System.Windows.Data
Imports System.Windows.Documents
Imports System.Windows.Media
Imports System.Windows.Media.Imaging
Imports System.Windows.Shapes
Imports System.Windows.Navigation
Imports System.Data

Please feel free to contact if there is any doubt.

If solves problem, click on "Accept as solution" / give a "Kudo".

Thanks and regards,

[jparks_79]

Hi Chandra,

I tried your example and I get the following errors:

Here is the code block I have:

AddReference "ADODB.dll"

Imports System.Core
Imports ADODB
Imports System.Linq
Imports System.Windows.Controls
Imports System.Windows.Data
Imports System.Windows.Documents
Imports System.Windows.Media
Imports System.Windows.Media.Imaging
Imports System.Windows.Shapes
Imports System.Windows.Navigation
Imports System.Data

Sub Main()

	Dim Connection As OleDb.OleDbConnection = New OleDb.OleDbConnection("#######")
        Dim Command As New OleDb.OleDbCommand
        Dim ds As New DataSet
        Dim da As New OleDb.OleDbDataAdapter
        'Dim rowo As System.Data.DataRow
        'Dim Count As Integer
        Connection.Open()  'open up a connection to the database
        Command.Connection = Connection

		

        Dim partnum As String = "GSIN09301803500"
        Dim query As String = "Select SpringSAPPN FROM Springs        WHERE  SpringGSPN ='" + partnum + "' "
        da.SelectCommand = New OleDb.OleDbCommand(query, Connection)


        da.Fill(ds, "Table") 'Fill the dataset, ds, with the above SELECT statement
        'Count = ds.Tables("Table").Rows.Count

        MessageBox.Show(ds.Tables(0).Rows(0)(0).ToString)

End Sub

Sorry I am only a novice at this. So I really appreciate the support here.

[chandra.shekar.g]

Hi @jparks_79,

Try the following changes in iLogic rule.

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

Sub Main()

	Dim Connection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("#######")
        Dim Command As New System.Data.OleDb.OleDbCommand
        Dim ds As New System.Data.DataSet
        Dim da As New System.Data.OleDb.OleDbDataAdapter
        'Dim rowo As System.Data.DataRow
        'Dim Count As Integer
        Connection.Open()  'open up a connection to the database
        Command.Connection = Connection

		

        Dim partnum As String = "GSIN09301803500"
        Dim query As String = "Select SpringSAPPN FROM Springs        WHERE  SpringGSPN ='" + partnum + "' "
        da.SelectCommand = New System.Data.OleDb.OleDbCommand(query, Connection)


        da.Fill(ds, "Table") 'Fill the dataset, ds, with the above SELECT statement
        'Count = ds.Tables("Table").Rows.Count

        MessageBox.Show(ds.Tables(0).Rows(0)(0).ToString)

End Sub

Please feel free to contact if there is any doubt.

If solves problem, "Accept as solution" / give a "Kudo".

Thanks and regards,

[jparks_79]

Hi Chandra,

Thanks for the support. But I gave up yesterday and just made a dll of the code with vb.net. So I just reference that in iLogic. It seems to work well.

Thanks

Jeremy

[chandra.shekar.g]

Hi @jparks_79,

If it is working in iLogic, click on "Accept as solution".

Thanks and regards,

[jparks_79]

Hi Chandra,

Unfortunately, that code is not working either.

Errors on line 1 and 2: Declaration Expected

Line 4: statement is not valid in a namespace

[chandra.shekar.g · Accepted solution]

Hi @jparks_79,

Initially, a data connectivity driver is required for Database connectivity. So, need to download and install "2007 Office System Driver : Data Connectivity Components" from the following link.

https://www.microsoft.com/en-us/download/details.aspx?id=23734

Below iLogic rule is used to connect EmployeeInfo database attached with this post. Following iLogic rule requires to update path of database file

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))
		Dim NextListItem As New ListViewItem(Item)
		'ListView1.Items.Add(NextListItem)
	Next
End Sub

Please feel free to contact if there is any doubt.

If solves problem, click on "Accept as solution" / give a "Kudo".

Thanks and regards,

[Anonymous]

Chandra,

I tried your code and it works well 🙂

Do you have a line or two of code that would allow me to write an iproperty value to a database?

thanks in advance

[chandra.shekar.g]

Hi @Anonymous,

Try following sample iLogic code to write iProperty to database. This code is tested with database(EmployeeInfo.zip) which is attached in the previous post.

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 " & Table_ & "(EID, ENAME, DOB, Designation) VALUES ('6', '"&iProperties.Value("Summary", "Author")&"', #1987-08-03 00:00:00#, 'Technical Consultant')"
    Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Chandra\Autodesk Cases\Inventor\July-2017\13195068\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
    cmd.Connection = cnn
    cmd.CommandText = query
    cmd.ExecuteNonQuery()
    cnn.Close()

End Sub

Please feel free to contact if there is any doubt.

If solves problem, click on "Accept as solution" / give a "Kudo".

Thanks and regards,

[Anonymous]

works well thanks!

[jparks_79]

Hi Chandra,

Sorry for the late reply, but thanks so much for the help! The solution works very well! Do you know if this additional database package would need to be installed on each users workstation in order for the connection to work? 

[chandra.shekar.g]

Hi @jparks_79,

Yes, for every database connection has its own protocols to communicate. So, each database requires driver to connect.

Thanks and regards,

[Anonymous]

This code works great for reading and writing to an Access database (accdb or mdb).

Now I was wondering if anyone ever took this to the next level and used the default.mdb or a secondary.mdb database used in Catalog Browser?  I have a custom Catalog Browser database linked as my secondary database.  As long as I am not messing with the fields, tables and links of the existing categories, I think in theory it would work.

Has anyone ever tried this?

[jparks_79]

 Mark,

Are you referring to the electrical catalog browser or something else? I have not tried, but sounds interesting. The key is most likely the correct connection string

[Anonymous]

Is it possible to export data from Inventor to Access?

[chandra.shekar.g]

@Anonymous,

Some of Inventor data can be exported to MicrosoftAccess. Can you please specify Inventor data which needs to export?

Thanks and regards,

[Anonymous]

I would like to export parameters and custom properties in the drawing environment ...

[chandra.shekar.g]

@Anonymous,

Give me some time. I will let you know the feasibility.

Thanks and regards,