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)

[lah29-bertrand]

you can do this with a reader, 

I do that for a table in drawing, see the code

the code run in the 2 directions

I take the data from the SQL

or I write in the SQL

It's in french, if you don't understand, I'll translate

SyntaxEditor Code Snippet

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

'Imports System.Data.OleDb
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient

 SyntaxEditor Code Snippet

'++++++++++++++++EXTRACTION N° DE PROJET++++++++++++++
Dim NomFi As String
Dim NumProj As String
Dim DecFi As Object
NomFi = ThisDoc.FileName(False)
DecFi = Split(NomFi, "-")
NumProj = DecFi(0)
MessageBox.Show(NumProj, "Title")
'+++++++++++++++++++++LIEN SQL+++++++++++++++++++++
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReader As SqlDataReader
Dim results As String
Dim Req As String
Dim ReqInsert As String

'Definition de la connexion
myConn = New SqlConnection("Data Source = SRV-SQL ;Initial Catalog = SqlBase1 ;User Id = sa ;Password = 123 ; ")
'requete d'interrogation
Req = "SELECT Codelancement,DateCreationLct FROM HART_PLAN_LCT LIMIT1 WHERE DesignationLct4='" + NumProj + "' ORDER BY DateCreationLct DESC"
myCmd = myConn.CreateCommand
myCmd.CommandText = Req
'Ouverture de la connexion
myConn.Open()
myReader = myCmd.ExecuteReader()
'Controle de la presence de donnees dans le reader
'If myReader.HasRows = False Then
'MessageBox.Show("faux", "Title")
'Else
'Inscrire le resultat de la requete dans une chaine de caracteres
'Do While 
'myReader.Read()
'results = myReader.GetString(0)
' Loop
'Affichage du resultat.
'MsgBox(results)

'++++++++++++++Definition des points d'insertion++++++++++++++++
Dim oDrawDoc As DrawingDocument = ThisApplication.ActiveDocument
Dim oSheet As Sheet = oDrawDoc.Sheets.Item(1)
'Dim oTG As TransientGeometry
'oSheet.Size=9993  A0
'oSheet.Size=9994  A1
'oSheet.Size=9995  A2
'oSheet.Size=9996  A3
'oSheet.Size=9997  A4
'oSheet.Orientation=10243  portrait
'oSheet.Orientation=10242  paysage

'A0portrait
If oSheet.Size = 9993 And oSheet.Orientation = 10243 Then
	Xpt3 = oSheet.Width - 9.84
	Xpt4 = oSheet.Height - (oSheet.Height - 6.3045)
	'A0paysage
ElseIf oSheet.Size = 9993 And oSheet.Orientation = 10242 Then
	Xpt3 = oSheet.Width - 9.84
	Xpt4 = oSheet.Height - (oSheet.Height - 6.3045)
	'A1portrait
ElseIf oSheet.Size = 9994 And oSheet.Orientation = 10243 Then
	Xpt3 = oSheet.Width - 9.84
	Xpt4 = oSheet.Height - (oSheet.Height - 6.3045)
	'A1paysage
ElseIf oSheet.Size = 9994 And oSheet.Orientation = 10242 Then
	Xpt3 = oSheet.Width - 9.84
	Xpt4 = oSheet.Height - (oSheet.Height - 6.3045)
	'A2portrait
ElseIf oSheet.Size = 9995 And oSheet.Orientation = 10243 Then
	Xpt3 = oSheet.Width - 8.84
	Xpt4 = oSheet.Height - (oSheet.Height - 5.3045)
	'A2paysage
ElseIf oSheet.Size = 9995 And oSheet.Orientation = 10242 Then
	Xpt3 = oSheet.Width - 8.84
	Xpt4 = oSheet.Height - (oSheet.Height - 5.3045)
	'A3portrait
ElseIf oSheet.Size = 9996 And oSheet.Orientation = 10243 Then
	Xpt3 = oSheet.Width - 8.84
	Xpt4 = oSheet.Height - (oSheet.Height - 5.3045)
	'A3paysage
ElseIf oSheet.Size = 9996 And oSheet.Orientation = 10242 Then
	Xpt3 = oSheet.Width - 8.84
	Xpt4 = oSheet.Height - (oSheet.Height - 5.3045)
	'A4portrait
ElseIf oSheet.Size = 9997 And oSheet.Orientation = 10243 Then
	Xpt3 = oSheet.Width - 8.84
	Xpt4 = oSheet.Height - (oSheet.Height - 5.3045)
	'A4paysage
ElseIf oSheet.Size = 9997 And oSheet.Orientation = 10242 Then
	Xpt3 = oSheet.Width - 8.84
	Xpt4 = oSheet.Height - (oSheet.Height - 5.3045)
End If

'++++++++++Definition et insertion de la table de revision+++++++++++++
If oDrawDoc.ActiveSheet.RevisionTables.Count = 0
	'Creation du point d'insertion
	Dim oPoint2 As Point2d
	oPoint2 = ThisApplication.TransientGeometry.CreatePoint2d(Xpt3, Xpt4)
	'Definition table de revision
	Dim oRTBs As RevisionTables
	oRTBs = oDrawDoc.ActiveSheet.RevisionTables
	'Insertion table de revision
	Dim oRTB As RevisionTable
	oRTB = oRTBs.Add(oPoint2)
End If

'+++++++++++++Ecriture dans la table de revision+++++++++++++++++
Dim oRevTable As RevisionTable = oDrawDoc.ActiveSheet.RevisionTables.Item(1)
' Choix de la derniere rangee
Dim oRow As RevisionTableRow
oRow = oRevTable.RevisionTableRows.Item(oRevTable.RevisionTableRows.Count)
Dim oCell As RevisionTableCell

'Controle de la presence de donnees dans le reader
If myReader.HasRows = False Then
	myReader.Close()
	' Verification range active dans tableau
	If oRow.IsActiveRow Then
		' Go through all columns in that row
		For i = 1 To oRevTable.RevisionTableColumns.Count
			oCell = oRow.Item(i)
			If i = 2 '2nd colonne de la table de revision
				oCell.Text = InputBox("Entrer le N° d'OF", "Historique et OF", "XX-XXXXX")
'++++++++++++++++++INSERTION N° DE PLAN DANS BASE SQL++++++++++++++++++++++++++++
				Question = MessageBox.Show("VOULEZ VOUS AFFECTER LE N° DE PLAN A L'OF DANS LA BASE SILOG (Ecrire le N° saisi dans la base de données)", "PLAN NON AFFECTE", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
				If Question = vbYes Then
					ReqInsert = "UPDATE LCTE Set DesignationLct4 ='" + NumProj + "'  Where CodeLancement ='" + oCell.Text + "'"
					Dim myCmd2 As New SqlCommand(ReqInsert, myConn)
					myCmd2.ExecuteNonQuery()
				End If
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
			ElseIf i = 3 '3eme colonne de la table de revision
				oCell.Text = InputBox("Quantité à Produire", "Historique et OF", "1")
			ElseIf i = 4 '4eme colonne de la table de revision
				oCell.Text = InputBox("Modification", "Historique et OF", "")
			End If
			oCell.Text = oCell.Text   'defini la cellule en statique
		Next
	End If

Else
	'Inscrire le resultat de la requete dans une chaine de caracteres 
	myReader.Read()
	results = myReader.GetString(0)
	' Make sure we have the active row
	If oRow.IsActiveRow Then
		' Go through all columns in that row
		For i = 1 To oRevTable.RevisionTableColumns.Count
			oCell = oRow.Item(i)
			If i = 2 '2nd colonne de la table de revision
				oCell.Text = InputBox("Entrer le N° d'OF", "Historique et OF", results)
			ElseIf i = 3 '3eme colonne de la table de revision
				oCell.Text = InputBox("Quantité à Produire", "Historique et OF", "1")
			ElseIf i = 4 '4eme colonne de la table de revision
				oCell.Text = InputBox("Modification", "Historique et OF", "")
			End If
			oCell.Text = oCell.Text   'defini la cellule en statique
		Next
	End If
End If
'fermeture du reader et de la connexion
myReader.Close()
myConn.Close()

[Anonymous]

I have a new thought about this issue I'm trying to solve.

There has to be a way to search on a unique number (in my case it would be in USER1) and then import all the fields associated with that record into Custom iProperties... the tricky bit would be looking in the other table for the Pinlist entries (if they exist)

[Andrew1307]

Just wanted to write and say thanks for the example, it got me going in the right direction.

However, for others looking at this should know calling your variables directly in SQL commands is bad practice. For more information just google bobby tables or why you should sanitize your inputs.

Here is a modified example that uses parameters in SQL queries properly.

Sub Main()
	
	'SQL LINK
	Dim myConn As SqlConnection
	Dim myCmd As SqlCommand
	Dim myReader As SqlDataReader
	Dim results As String
	Dim Req As String
	Dim ReqInsert As String

	'Get previously entered StockNumber
	Dim PartNumber As String
	PartNumber = iProperties.Value("Project", "Part Number")
		
	'Definition of connection
	myConn = New SqlConnection("Data Source = SQLSERVER ;Initial Catalog = DBNAME ;User Id = USER ;Password = PASSWORD ; ")
	'query request using  as the SQL Parameter @Anonymous to pass the Inventor Paramter into the SQL query correctly
	Req = "SELECT StockCode,Description,LongDesc,ProductClass FROM InvMaster WHERE StockCode = @Anonymous"
	myCmd = myConn.CreateCommand
	myCmd.CommandText = Req
	
        'Create the SQL Parameter  and assign the Inventor Parameter PartNumber value to @Anonymous. This sanitizes the input.
	MyCmd.Parameters.Add("@PartNumber", SqlDbType.VarChar, 30).Value = PartNumber
	
	'Connection opening
	myConn.Open()
	myReader = myCmd.ExecuteReader()
	
	'Read data from SQL Query and assign it to Inventor Parameters as needed. 
	If myReader.HasRows Then
		While myReader.Read()
			iProperties.Value("Project", "Description") = myReader.GetString(1)
			iProperties.Value("Custom", "LongDesc") = myReader.GetString(2)
			iProperties.Value("Custom", "ProductClass") = myReader.GetString(3)
		End While
	Else
		MessageBox.Show("Reader has no rows.", "Blah")
	End If
	
	'closing the reader and connection
	myReader.Close()
	myConn.Close()
		
End Sub

[freesbee]

I'm sorry, I am quite new to attempting to read an SQL database from within inventor VBA.

I am trying any solution suggested, but probably I am missing something.

My very simple macro

Public Sub connectToSql()
AddReference "System.Data"
End Sub

fails as soon as I press F8 with the message:

Sub or Function not defined

If I try with

Public Sub connectToSql()
dim myConn = New OleDb.OleDbConnection("Data Source=SERVERNAME\instance;Initial Catalog=Database_NAME;User ID=USERNAME;Password=PASSWORD")
End Sub

then VBA editor highlights the syntax as "offending" immediately.

I suspect I am missing something, but what am I exactly missing?

[ngnam1988]

Dear @chandra.shekar.g 

Your code works perfectly to create the new database file. Please help me the update query for updating the database file. Thanks,