topic Re: Connect to SQL or Microsoft Access from iLogic in Inventor Programming Forum

Connect to SQL or Microsoft Access from iLogic

jparks_79 — Tue, 18 Jul 2017 15:34:57 GMT

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)

Re: Connect to SQL or Microsoft Access from iLogic

MechMachineMan — Tue, 18 Jul 2017 19:03:49 GMT

Google is a wonderful thing:

https://knowledge.autodesk.com/support/inventor-products/learn-explore/caas/CloudHelp/cloudhelp/2015/ENU/Inventor-Help/files/GUID-8DF6F761-1634-4D26-B13A-58AF275FD6F8-htm.html

Re: Connect to SQL or Microsoft Access from iLogic

chandra.shekar.g — Thu, 20 Jul 2017 06:05:26 GMT

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,

Re: Connect to SQL or Microsoft Access from iLogic

jparks_79 — Wed, 26 Jul 2017 15:22:27 GMT

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.

Re: Connect to SQL or Microsoft Access from iLogic

chandra.shekar.g — Thu, 27 Jul 2017 05:13:38 GMT

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,

Re: Connect to SQL or Microsoft Access from iLogic

jparks_79 — Thu, 27 Jul 2017 12:26:57 GMT

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

Re: Connect to SQL or Microsoft Access from iLogic

chandra.shekar.g — Thu, 27 Jul 2017 12:42:10 GMT

Hi @jparks_79,

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

Thanks and regards,

Re: Connect to SQL or Microsoft Access from iLogic

jparks_79 — Thu, 27 Jul 2017 13:18:04 GMT

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

Re: Connect to SQL or Microsoft Access from iLogic

chandra.shekar.g — Fri, 28 Jul 2017 09:07:57 GMT

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,

Re: Connect to SQL or Microsoft Access from iLogic

Anonymous — Fri, 28 Jul 2017 10:10:49 GMT

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

Re: Connect to SQL or Microsoft Access from iLogic

chandra.shekar.g — Tue, 01 Aug 2017 05:30:02 GMT

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,

Re: Connect to SQL or Microsoft Access from iLogic

Anonymous — Tue, 01 Aug 2017 08:15:29 GMT

works well thanks!

Re: Connect to SQL or Microsoft Access from iLogic

jparks_79 — Fri, 04 Aug 2017 18:56:50 GMT

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?

Re: Connect to SQL or Microsoft Access from iLogic

chandra.shekar.g — Mon, 07 Aug 2017 04:22:14 GMT

Hi @jparks_79,

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

Thanks and regards,

Re: Connect to SQL or Microsoft Access from iLogic

Anonymous — Fri, 02 Nov 2018 13:00:06 GMT

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?

Re: Connect to SQL or Microsoft Access from iLogic

jparks_79 — Fri, 02 Nov 2018 14:16:11 GMT

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

Re: Connect to SQL or Microsoft Access from iLogic

Anonymous — Fri, 14 Dec 2018 11:03:18 GMT

Is it possible to export data from Inventor to Access?

Re: Connect to SQL or Microsoft Access from iLogic

chandra.shekar.g — Fri, 14 Dec 2018 11:08:25 GMT

@Anonymous,

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

Thanks and regards,

Re: Connect to SQL or Microsoft Access from iLogic

Anonymous — Fri, 14 Dec 2018 11:27:06 GMT

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

Re: Connect to SQL or Microsoft Access from iLogic

chandra.shekar.g — Fri, 14 Dec 2018 11:29:40 GMT

@Anonymous,

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

Thanks and regards,