Query SQL Data via Calculated Field
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
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
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.
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.
I’m not familiar with setting up these connections…but I’ll try 🙂 thanks.
Lorren,
Just wanted to let you know that your suggestion worked. Thanks!!
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.