Re: SQL returns value

Matt Faus — Sun, 11 Jun 2006 03:07:04 GMT

Actually, if your form's main DOM is the database connection, you can issue queries against it through the "queryFields" group.

However, when trying to make a secondary data connection to a DB you can't accomplish this in the IP designer, but you can through the use of code. Just add a regular database connection, and then write a little code that does something like this:


// Grab the field from the form
string sCurrentId = getNodeValue("/my:Timecard/my:Common/my:ResourceId");

// Construct the query
ADOAdapter dbConn = (ADOAdapter)thisXDocument.DataObjects["Project-WorkItems"].QueryAdapter;
dbConn.Command = ConstructResourceQueryString(sCurrentId);            
thisXDocument.DataObjects["Project-WorkItems"].Query();

private string ConstructResourceQueryString(string sResourceId)
{
    sResourceId = ConvertIPGuid(sResourceId);

    string[] lines = new string[11];
    lines[0] = "shape ";
    lines[1] = "{select \"ProjectID\",\"Name\",\"Code\",\"StartDate\",\"CustomerID\",\"RateOverhead\",\"Billable\" from \"dbo\".\"Project\" as \"Project\"} as \"Project\"";
    lines[2] = "append";
    lines[3] = "({select \"WorkItemID\",\"Description\",\"Hours\",\"Rate\",\"ResourceID\",\"Completed\",\"MilestoneID\",\"ProjectID\",\"StartDate\" from \"dbo\".\"WorkItem1\" as \"WorkItem1\" }";
    lines[4] = "relate \"ProjectID\" TO \"ProjectID\") as \"WorkItem1\", ";
    lines[5] = "({select \"WorkItemID\",\"Description\",\"Hours\",\"Rate\",\"ResourceID\",\"Completed\",\"MilestoneID\",\"ProjectID\",\"StartDate\" from \"dbo\".\"WorkItem2\" as \"WorkItem2\" where \"ResourceID\" = '" + sResourceId + "'}";
    lines[6] = "relate \"ProjectID\" TO \"ProjectID\") as \"WorkItem2\", ";
    lines[7] = "({select \"WorkItemID\",\"Description\",\"Hours\",\"Rate\",\"ResourceID\",\"Completed\",\"MilestoneID\",\"ProjectID\" from \"dbo\".\"WorkItem3\" as \"WorkItem3\" where \"ResourceID\" = '" + sResourceId + "'}";
    lines[8] = "relate \"ProjectID\" TO \"ProjectID\") as \"WorkItem3\", ";
    lines[9] = "({select \"WorkItemID\",\"Description\",\"Hours\",\"Rate\",\"ResourceID\",\"Completed\",\"MilestoneID\",\"ProjectID\" from \"dbo\".\"WorkItem4\" as \"WorkItem4\" where \"ResourceID\" = '" + sResourceId + "'}";
    lines[10] = "relate \"ProjectID\" TO \"ProjectID\") as \"WorkItem4\"";

    string query = String.Format("{0} {1} {2} {3} {4} {5} {6} {7} {8} {9} {10} ",
                        lines[0],
                        lines[1],
                        lines[2],
                        lines[3],
                        lines[4],
                        lines[5],
                        lines[6],
                        lines[7],
                        lines[8],
                        lines[9],
                        lines[10]);

    return query;
}

This example uses C#.NET, but should be able to convert to any other language that IP supports. I hope this helps!

Re: SQL returns value

Patrick Halstead — Sat, 10 Jun 2006 08:24:59 GMT

Hi Zar-man and welcome to our forum!
InfoPath doesn't support data field based queries using the built-in SQL data connection query string. Check out our Database Accelerator or write your own web service.

SQL returns value

Zar-man — Tue, 06 Jun 2006 16:32:30 GMT

I designed a form and opened it in VS2005. I added a secondary data connection(SQL 2003).

What I'm trying to do is this, when a person fills out this form they enter their SSN and then when they cliick a button, I want it to return their values like name, address, sex, ect. All those I have in a SQL table called social. Which I added as my secondary data source.

I'm new to coding and Infopath, so how would I go about doing this. Right now nothing happens, Is there alot of code writing to be able to do this?

SQL Database Integration

Re: SQL returns value

Re: SQL returns value

SQL returns value