Skip to content
+1-888-319-3663

COMMUNITY FORUM

Query SQL Data via Calculated Field

Manny asked 5 years ago
Hello,
I need to return a value from a SQL query (not from my source, but using values fro the source to query data on a separate SQL server) and use that value in a calculated field.  Since I cannot reference an MS SQL Lookup in a Calculated field, I’m trying to use a Calculated field, to query SQL, return the value, then use that value in another Calculated field.  When I try to run the integration, it gives me this error:
 keyword not supported ‘driver’ 
 Here’s my connection code:
 
Dim sqlQuery As String = “SELECT USERDEF1 FROM SMG..PM00200 WHERE VENDORID= ‘” & _VENDORNUM & “‘”
Dim mapInterface As Integer = 1
‘—————————————————————————————
Dim queryResult As String = “”
 
Try
 
 ‘Declare the connection string based on the configuration variables
 Dim conString As New String(_GODBC_vSQL2_SMG)
 ‘Declare the datareader
 Dim dr As System.Data.SqlClient.SqlDataReader
 
  ‘Define the SQL connection and open it
 Dim myConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
 myConn.Open()
 ‘Declare the SQL command as the query string and execute it
 Dim command As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sqlQuery, myConn)
 command.CommandType=System.Data.CommandType.Text
 dr = command.ExecuteReader()
 
 dr.Read()
 queryResult = dr(0).ToString()
 
  ‘Close the connection
 myConn.Close()
 
Catch ex As Exception
 If (mapInterface = 1) Then
  Messagebox.Show(ex.Message, “SQLQuerySelect Script Error”)
 End If
 Return “”
End Try
 
Return queryResult
Answers
Lorren Zemke Staff answered 5 years ago
Manny,
Based on your Connection name of _GODGC_vSQL2_SMG, you set up a default ODBC connection, which is fine except all of the .NET code is using SqlClient as the Namespace which is where the conflict is at.
You can go to the Maintenance tab and set up an MSSQL Default to use as your connection or change the .NET code to use the System.Data.Odbc.ODbcConnection, odbcDataReader, odbcCommand, etc.
 
Manny replied 5 years ago

I’m not familiar with setting up these connections…but I’ll try 🙂 thanks.

Manny replied 5 years ago

Lorren,

Just wanted to let you know that your suggestion worked. Thanks!!

Boris answered 9 months ago
Just wanted to say thanks to Manny, I was able to take your code and modify for my purposes to pull data for a field from a query that needed several table joins which wouldn’t otherwise be possible, and Lorren’s input about method of connection let it go off without a hitch. Thanks both.

If you would like to submit an answer or comment, please sign in to the eOne portal.