SQL Database Integration

Re: Query SQL DB to set field values

Hilary Stoupa — Wed, 18 Nov 2009 16:42:55 GMT

The code will be similar to what you are using to get your job_number field.

' Create XPathNavigator positioned at the root of the main data
' source.
Dim myPO As XPathNavigator = Me.MainDataSource.CreateNavigator()
Dim value As XPathNavigator = Me.DataSources("REQUEST").CreateNavigator.SelectSingleNode("xpathToTheNodeYouWant", Me.NamespaceManager)

' Select Job Number field and set value to JOB_NMBR from REQUEST Data Source.
myPO.SelectSingleNode("//my:job_number", Me.NamespaceManager). _
SetValue(value.Value)

Re: Query SQL DB to set field values

bblanke — Wed, 18 Nov 2009 15:36:45 GMT

I got the query to work using:

            ' Get the REQUEST connection from the
            ' DataConnections collection.
            Dim myREQUEST As AdoQueryConnection = _
               DirectCast(Me.DataConnections("REQUEST"), AdoQueryConnection)

            ' Get the Request Nuumber from the request_number field in
            ' the main data source.
            Dim myRQN As XPathNavigator = _
               CreateNavigator().SelectSingleNode("/my:myFields/my:request_number", _
               NamespaceManager)
            Dim request_number As String = myRQN.InnerXml

            ' Change the SQL command for REQUEST connection to retrieve
            ' the record of the Request Number entered by the user.
            myREQUEST.Command = _
               "select * from [REQUEST] where [REQ_NMBR] = " + request_number

            ' Execute the updated command against the data connection to
            ' refresh the data.
            myREQUEST.Execute()

Now how do I get a field from that query and set it in a field in my form? From searching the dev sites I found this code which I modified

            ' Create XPathNavigator positioned at the root of the main data
            ' source.
            Dim myPO As XPathNavigator = Me.MainDataSource.CreateNavigator()

            ' Select Job Number field and set value to JOB_NMBR from REQUEST Data Source.
            myPO.SelectSingleNode("//my:job_number", Me.NamespaceManager). _
               SetValue("")

But I'm unsure what I need to put in the SetValue to pull the data from the query.

Re: Query SQL DB to set field values

Hilary Stoupa — Fri, 13 Nov 2009 20:57:09 GMT

There's some sample code at MSDN that may help you with this. Take a look and post back if you have any troubles with it.

Query SQL DB to set field values

bblanke — Thu, 12 Nov 2009 19:05:54 GMT

I'm still very new to infopath and not really a programmer. Here is what I'm trying to do:

I have a text box that a Request Number is entered

Then I have a rule to query a secondary data connection SQL database

Then set the value of various other fields based of the returned values from the query using a filter. Filtering off the user entered Request Number.

This takes a long time as it has to copy down the entire db, rather than only searching the db for the matching record.

How do I adjust the SQL query statement in such so t reads 'where REQ_NMBR = [request_number]'

REQ_NMBR is a field in the db and [request_number] is the name of the field in my form.