SQL Database Integration

Re: How to Interate Forms with SQL

Patrick Halstead — Fri, 27 Jan 2006 18:28:49 GMT

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

Re: How to Interate Forms with SQL

Carlos.Carrillo — Fri, 27 Jan 2006 15:34:59 GMT

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.

Re: How to Interate Forms with SQL

Agni Jonnalagadda — Fri, 27 Jan 2006 10:26:44 GMT

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

Re: How to Interate Forms with SQL

Patrick Halstead — Fri, 27 Jan 2006 08:21:34 GMT

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

Patrick Halstead [InfoPath MVP]
InfoPathDev

How to Interate Forms with SQL

Carlos.Carrillo — Thu, 26 Jan 2006 07:21:16 GMT

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