Modify SQL query with infopath field value

[Gabriele Fabbri]

Hi all, I would like to know how can I personalize the main connection
query using a syntax like:

SELECT ... FROM .... WHERE name='infopath field value'

Is it possible? I know WHERE I can do this in Infopath but I can't
understand what syntax I have to use to point at the correct module
field! I think I have to use an XPath structure... right?
Someone can explain me with a little example?

Thanks,

Gabriele Fabbri.

 

[Halil Kara]

You can try this code behind button:

'First declare something and get the value of the control you want to use in
your Where clause
Dim GetSomething
GetSomething=XDocument.DOM.selectSingleNode("//my:txtSomething").text

'Now create data adapter to your data connection
Dim objADOAdapter
Set objADOAdapter =
XDocument.DataObjects("YourDataConnectionName").QueryAdapter

'Create variables to the data connection and it's query
Dim oConn
Dim originalSQLQuery
originalSQLQuery =
XDocument.DataObjects("YourDataConnectionName").QueryAdapter.Command
set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = objADOAdapter.Connection
oConn.Open()

'Here you create the Where clause
dim strSQL
strSQL = " WHERE condition'"& GetSomething &"'"

'Now you run the full query with your Where clause
XDocument.DataObjects("YourDataConnectionName").QueryAdapter.Command=originalSQLQuery
+ strsql
XDocument.DataObjects("YourDataConnectionName").Query()

This should work and can be modified to jscript easily

All the best