Query SQL datbase

[billco-mfg]

I would like to use the API query a SQL database and populate iProperty fileds in an Inventor file. Does anyone have sample code that I might use to figure out how to access the database?

[Anonymous]

There might be someone here that can help you but I think your more likely
to find help at a SQL specific site. Accessing the database doesn't have
anything to do with the Inventor API. You'll need to use other API's to
access and query the information you want from the database. Once you have
the values from the database then you can use the Inventor API's to populate
iProperty values.
--
Brian Ekins
Autodesk Inventor API Product Designer
http://blogs.autodesk.com/modthemachine

"wehnerk" wrote in message news:6257058@discussion.autodesk.com...
I would like to use the API query a SQL database and populate iProperty
fileds in an Inventor file. Does anyone have sample code that I might use to
figure out how to access the database?

[billco-mfg]

I'm not going to have access to the ERP system's API. I want to accomplish something very similar to what a co-worker was able to do in an Excel macro. Can I start Excel from Inventor, run an Excel macro and pass information back to Inventor?

[Anonymous]

I assume you're using Inventor's VBA. It's a standard VBA environment from
which you can connect to any application that supports a COM Automation
interface. That's how Excel exposes its API. Using this you can start up
or connect to Excel, open a spreadsheet and query out values. You do all of
that using Excel's API. Once you have the values then you can use
Inventor's API to populate the iProperty values. Let me know if my
assumptions about what you want to do are right and I can post some sample
code that demonstrates connecting to Excel. It's just important to remember
that it's not Inventor's API that's providing that functionality but that
you can use the Excel API from within Inventor's VBA environment.
--
Brian Ekins
Autodesk Inventor API Product Designer
http://blogs.autodesk.com/modthemachine


"wehnerk" wrote in message news:6257897@discussion.autodesk.com...
I'm not going to have access to the ERP system's API. I want to accomplish
something very similar to what a co-worker was able to do in an Excel macro.
Can I start Excel from Inventor, run an Excel macro and pass information
back to Inventor?

[billco-mfg]

I was originally hoping to bypass Excel in the process, but whatever works for now. Yes, those examples would be greatly appreciated. Thanks.

I read a post on another board that said VBA is being phased out by Microsoft. Would Visual Studio Express have the ability to query the database without using Excel? Can I download or purchase something that would provide the ability to conect to the database? I'm assuming that this program I want to write will be very slow if I have to wait on Excel to startup when it runs. Edited by: wehnerk on Sep 22, 2009 7:46 AM

[ludesroc]

Here is a simple example to query an Sql Database and pass the result to a property. You need to set a reference to Microsoft ActiveX DataObjects 2.8 Library.

CODE:

Option Explicit

Private MyRecordSet As ADODB.Recordset
Private MyConnection As New ADODB.Connection

Sub Test()

Dim RequeteSql As String
Dim ReponseSql As Variant

' Query.
RequeteSql = "SELECT Emplacement FROM RepertoireTravail WHERE Nom = 'EmplacementSolin' "

' Result from the query.
ReponseSql = GetSqlData(RequeteSql, "VentesProduction")

Dim oDoc As Document
Set oDoc = ThisApplication.ActiveDocument

Dim oPropSets As PropertySets
Set oPropSets = oDoc.PropertySets

Dim oPropSet As PropertySet
Set oPropSet = oPropSets.Item("Design Tracking Properties")

Dim oProp As Property
Set oProp = oPropSet.Item("Project")

' Set the property value.
oProp.Value = ReponseSql(0, 0)

oDoc.Save

End Sub

Public Function GetSqlData(ByRef Requete, ByRef sCatalog) As Variant

Set MyRecordSet = New ADODB.Recordset

MyConnection.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog= " + sCatalog + ";Data Source=ASTON-SVR1"
MyConnection.ConnectionTimeout = 500

MyConnection.Open

Dim sQueryType As String
sQueryType = UCase(Mid(Requete, 1, 6))

If sQueryType = "DELETE" Or sQueryType = "INSERT" Or sQueryType = "UPDATE" Then

MyConnection.Execute Requete

If Err = 0 Then
GetSqlData = True
Else
GetSqlData = False
End If

ElseIf sQueryType = "SELECT" Then

On Error Resume Next

MyRecordSet.Open Requete, MyConnection

Dim ListeRecord As Variant
ListeRecord = MyRecordSet.GetRows()

If VarType(ListeRecord) = vbEmpty Then
GetSqlData = Empty
Else
GetSqlData = ListeRecord
End If

Err.Clear

End If

End Function

[billco-mfg]

>You need to set a reference to Microsoft ActiveX DataObjects 2.8 Library.

How do I go about doing that?

Thanks for the code. Most of it makes sense. I modified the query and database name and tried to run it. I get a compile error: "User-defined type not defined". I assume that has something to do with not setting that reference?

[ludesroc]

In VBA...Go in Tools, References. Locate the reference and check the CheckBox.

[billco-mfg]

Oh. That was simple.

It's running now, but giving me an error when it tries to connect to the database. I should be able to figure it out from here. Thanks for your help.

[ludesroc]

Excellent! Good luck!

Ludesroc

[billco-mfg]

The macro is running great. Now that I can access our database like this, I just want to keep expanding the functionality.

But... I'm stuck on something simple again. How do I write the connection string so that it prompts the user for their credentials? Here is the form of the current connection string.

MyConnection.ConnectionString = "Provider=SQLOLEDB.1;Password=******;Persist Security Info=False;User ID=*****;Data Source=***********"

[Anonymous]

how to get frame IS 2062.

i am unable to find it please help.

@billco-mfg 

[CadUser46]

@billco-mfgi have quite a few macros that run queries to retrieve data and display them on the fly to the user.

If you want to pass their credentials VBA doesn't really have any/many native tools to do this, and handle them securely.

One option is to design your own form where the user types in their credentials, you capture them and pass them to your connection string as variables.  There is no security here and they would type it each time you run the macro.

Another is to write them to an xml or regkey, again questionable security.

No doubt there is some way to get them from active directory but i haven't tried that in VBA, and the application may not be using AD login anyway.

An alternative is to get the details of a service account, or have one created.  These accounts are typically used to allow servers to connect to the api and pass things between applications.  They dont tend to expire, this is what i do.

I also then password protect the vba.  It's certainly not foolproof but it gets the job done and is by far the least annoying to the user.  Also less maintenance for you to manage the ivb file.

[freesbee]

I am currently navigating a BOM, and for each virtual component row I might need to retrieve a specific information from an SQL database. The connection and the syntax work properly, but when trying to retrieve the second value I keep struggling against the infamous "3021 error: Either BOF or EOF is True...."

Runtime error 3021: Wither BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record

To my current understanding this happens because the variant where I am storing the sql output cannot be "updated, expanded, deleted and overwritten"... I have been struggling with this for hours already, but still not idea how to handle it. Does anyone have an idea?

Public Sub readFromSQL()
Dim MyConnection
Set MyConnection = CreateObject("ADODB.Connection")
MyConnection.Open "PROVIDER=SQLOLEDB;SERVER=SERVERNAME\instancename;DATABASE=DATABASENAME;user id=USER;password=PASSWORD"
Dim sqlRes As Variant

Dim oIAMDoc As AssemblyDocument
Set oIAMDoc = ThisApplication.ActiveDocument

Dim oBOM As BOM
Set oBOM = oIAMDoc.ComponentDefinition.BOM

Dim oBOMRowMember As BOMRow
Dim oBOMTreeView As BOMView
Set oBOMTreeView = oBOM.BOMViews(1)

For Each oBOMRowMember In oBOMTreeView.BOMRows
  Set oDef = oBOMRowMember.ComponentDefinitions(1)
  If oDef.Type = kVirtualComponentDefinitionObject Then
    sqlRes = MyConnection.Execute("SELECT [myNeededField] FROM [databasename].[dbo].[tablename] where [PartNumber] = '" & oDef.PropertySets(3)(2).Expression & "'")
    Debug.Print oDef.PropertySets(3)(2).Expression & " | " & sqlRes(0).Value
  end if
Next
End Sub

I have tried any idea that came to our mind: defining sqlRes as string (after all I need a string), as an array, as an object... no relevant difference.

The first iteration in the loop works fine, but when trying to store the value coming from the second query I always get into the 3021 error. Any help would be appreciated.

[freesbee]

...well actually this turned out to be a situation where the database does NOT return anything for the query.

A simple addition to check this has addressed the issue:

sqlRes = MyConnection.Execute("SELECT [myNeededField] FROM [databasename].[dbo].[tablename] where [PartNumber] = '" & oDef.PropertySets(3)(2).Expression & "'")
If sqlRes.BOF = False And sqlRes.EOF = False Then
  Debug.Print oDef.PropertySets(3)(2).Expression & " | " & sqlRes(0).Value
Else
  Debug.Print oDef.PropertySets(3)(2).Expression & ": no value found in database"
End If