COMMUNITY FORUM

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