Community
Showing results for 
Search instead for 
Do you mean 
Reply

Using a SQL "select" statement as lookup criteria?

Accepted Solution Solved
New Member
Posts: 12
Country: Germany
Accepted Solution

Using a SQL "select" statement as lookup criteria?

Hello,

 

we try to figure out, how to use a complex sql statement (is selecting contactIDs) as a criteria for a lookup. We try it in the framework (should be the same as in UI).

 

Any ideas or samples?

 

Best regards

 

Mario Stuck

ACC from Berlin


Accepted Solutions
Solution
Accepted by topic author MarioStuck
‎09-25-2015 03:20 AM
Employee
Posts: 1,163
Country: USA

Re: Using a SQL "select" statement as lookup criteria?

Your select statement looks ok, you could probably get away with just "SELECT CONTACT, CONTACTID FROM CONTACT".

 

SelectColname should be SelectSomething. I copied this from something I had laying around and forgot to rename it.

Matthew Wood
Act! SDK Support
Community Moderator

View solution in original post


All Replies
Employee
Posts: 1,163
Country: USA

Re: Using a SQL "select" statement as lookup criteria?

You could use the OLE and do something like this.

 

OleDbConnection connection = new OleDbConnection(ActApp.ActFramework.CurrentACTOLEDB2);
OleDbCommand SelectSomething = new OleDbCommand("SELECT STATEMENT", connection);

connection.Open();
SelectColName.ExecuteNonQuery();
connection.Close();

Matthew Wood
Act! SDK Support
Community Moderator
New Member
Posts: 12
Country: Germany

Re: Using a SQL "select" statement as lookup criteria?

Thanks for that. But how to build a lookup with the returned contactIDs?

 

Me code looks like that (its VB.Net):

 

 

If isLoggedIn = False Then LoginToACTDatabase()Dim OleDbConn As New OleDb.OleDbConnection Dim OleDbComm As New OleDb.OleDbCommand "SELECT CONTACT, CONTACTID FROM ITDirekt_MS.dbo.CONTACT"

 

OleDbConn.ConnectionString = ActFwk.CurrentACTOLEDB2

OleDbComm.CommandText =

OleDbComm.Connection = OleDbConn

OleDbConn.Open()

OleDbComm.ExecuteNonQuery()

OleDbConn.Close()

 

 

I don't understand the variable "SelectColName" in your sample.

 

Best regards

 

Mario

Solution
Accepted by topic author MarioStuck
‎09-25-2015 03:20 AM
Employee
Posts: 1,163
Country: USA

Re: Using a SQL "select" statement as lookup criteria?

Your select statement looks ok, you could probably get away with just "SELECT CONTACT, CONTACTID FROM CONTACT".

 

SelectColname should be SelectSomething. I copied this from something I had laying around and forgot to rename it.

Matthew Wood
Act! SDK Support
Community Moderator
New Member
Posts: 12
Country: Germany

Re: Using a SQL "select" statement as lookup criteria?

Hi Metthew,

 

thanks, thats works fine. Do you know a way, do get direct access to the original tables to fire a select statement against these tables and return contactIDs (without the act reader tool).

 

Best Regards

 

Mario

Employee
Posts: 1,163
Country: USA

Re: Using a SQL "select" statement as lookup criteria?

The SDK doesn't require the reader, but even then I'd probably still use the OLEDB reporting provider to gather table data.

Matthew Wood
Act! SDK Support
Community Moderator
New Member
Posts: 12
Country: Germany

Re: Using a SQL "select" statement as lookup criteria?

But how do you access the native sql tables? Is this also possible thru actoledb2?

Employee
Posts: 1,163
Country: USA

Re: Using a SQL "select" statement as lookup criteria?

No, the only way to gain full read/write access is by purchasing the SA Password Utility.

 

This pdf covers all the data access methods to act and compares them and the 1.0 to the 2.0 provider at the bottom.

 

http://download.act.com/2011/wp/Reporting_and_Data_Access_Methods.pdf

Matthew Wood
Act! SDK Support
Community Moderator