InfoPath Dev

Hello everybody,

I have made a form (new from Data Connection)with a data connection SQL database. I have created a data source without any table, and an SQL statement, the following:

SELECT DISTINCT t.Ticket
,t.Updated_By AS Ticket_Updated_By
,a.Updated_By AS Activity_Updated_By
,t.Summary
,t.Status
,count(*) as Num_Activities
FROM HD_Tickets t
JOIN HD_Ticket_Activity a
on t.ID = a.Ticket_ID
WHERE a.ID IN (SELECT a.ID
FROM HD_Ticket_Activity a
WHERE a.Ticket_ID = t.ID
AND a.Updated_By IN ('juan justo')
-- AND cast(floor(cast(a.Submit_Date as float)) as datetime)
-- BETWEEN CONVERT(datetime,'2006-01-23',120)
-- AND CONVERT(datetime,'2006-01-23',120)
)
GROUP BY t.Ticket
,t.Updated_By
,t.Summary
,a.Updated_By
,t.Status

The form is working properly, but I have a question, I would like Infopath to ask me for some data before doing the 'query', for instance he 'Updated_By' and Dates that are shown in the SQL statement, I really don't know how to integrate them,

any advise will be really appreciated,
The best regards,
Carlos

Hi Carlos,
I think you will need to write code to do this. I'll ask Agni to respond.

Patrick Halstead [InfoPath MVP]
InfoPathDev

Hi Carlos,
Welcome to the Forum!
You can have fields in the UI to take input for 'Updated_By' and Dates, and use these value in the query. Here is the way to do it once you have the input value in the fields.

string updatedBy = "abc"; //Get Value from the UI field
string date1 = "2006-01-23"; //Get Value from the UI field
string date2 = "2006-01-23"; //Get Value from the UI field

//Take sql query in a string
string sqlCommand = "SELECT DISTINCT t.Ticket"
+",t.Updated_By AS Ticket_Updated_By"
+",a.Updated_By AS Activity_Updated_By"
+",t.Summary"
+",t.Status"
+",count(*) as Num_Activities "
+"FROM HD_Tickets t"
+"JOIN HD_Ticket_Activity a"
+"on t.ID = a.Ticket_ID"
+"WHERE a.ID IN (SELECT a.ID "
+"FROM HD_Ticket_Activity a"
+"WHERE a.Ticket_ID = t.ID"
+"AND a.Updated_By IN ('{0}')"
+"-- AND cast(floor(cast(a.Submit_Date as float)) as datetime) "
+" BETWEEN CONVERT(datetime,'{1}',120) "
+" AND CONVERT(datetime,'{2}',120)"
+")"
+"GROUP BY t.Ticket"
+",t.Updated_By"
+",t.Summary "
+",a.Updated_By "
+",t.Status";
//Add user inputs to you query string
sqlCommand = string.Format(sqlCommand, updatedBy, date1, date2);
//Get SQL dataconnection adapter here
ADOAdapter2 dbConnection = (ADOAdapter2)thisXDocument.DataAdapters["DBConnectioName"];
//Assign you new sql query to the connection adapter
dbConnection.Command = sqlCommand;
//Query the dataconnection
dbConnection.Query();



Agni
InfoPath Dev India

Hello, thanks Patrick and Agni, I dind't suppose that programming was neccesary, I'm a newbie in infopath (also in programming) and I thought this funcionality was possible in another way.

I will try with my colleagues here, maybe they can arrange this,

Anyway thanks very much and the best regards,
Carlos.

Hi again Carlos,
You can do this kind of query without code if you install our Database Accelerator product (which is a Web service running on a SQL database). Check out the Downloads section. We have a free evaluation version of Database Accelerator and you can do this kind of query using XPath indexing (as opposed to code).

Patrick Halstead [InfoPath MVP]
InfoPathDev