dynamic SQL query

[Kenneth]

Here is what I want to do:

I have a big form that have many controls but at the top I have a text box
for user to enter employee ID. A "search" button. and then a few text box to
display first name, last name, phone number and email etc.

When user enter a number and select "search", I want to pass the number to a
SQL server, and get back the user information, populate the text box for
first name, phone number, email etc.

I know I can make it work using web service, but I want to know if I can
make it work using a data connection. Tried to create a data source to a SQL
database. Two questions here:

1. How to I pass the dynamic employee ID to the data source connection?

2. How do I trigger the data source query when "search" button is clicked?

Thanks in advance.

 

[Scott L. Heim [MSFT]]

Hi Kenneth,

Did you create your InfoPath solution directly from your SQL Server
database? If not, this is what you will want to do as the default behavior
is you will get both "queryFields" and "dataFields." The query fields
provide the exact functionality you desire: enter a valid value into the
query field(s) and click Run Query - the results will automatically be
populated into your "data" fields.

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Kenneth]

Thank you Scott. I did not create the template based on the SQL database
because I need to connect to several different databases and the most of my
form is not based on data from SQL server. For the employee ID part, I want
to retrive just a small part of my whole XML form.

In my form, there are several places that I want to the same thing. After
user enter a string, I want to go to a database to get back some data to
populate some fields, either by a form action or by code inside a event
handler. That is why I put it as dynamic SQL query.

This is a common pattern but it seem to be that I have to use web services.

Ken

 

[Scott L. Heim [MSFT]]

Hi Ken,

See if these steps work for you:

- Create a new, blank InfoPath form
- Add a text box named: txtEmpID
- Add a text box named: txtLastName
- Add a button
- Add a secondary data connection to the Employees table in either the
Access or SQL Server database but do NOT enable the option to automatically
retrieve data when the form is opened
- Right-click on txtLastName and choose Properties
- Click the "fx" button for the Default Value field
- Click the Insert Field or Group button
- From the Data source box choose Employees
- Drill down, highlight LastName and click OK
- Click OK 2 times to get back to your form
- From the Tools menu choose Form Options, select the Advanced tab and be
sure the Programming Language is set to VBScript. (If you have already
began programming in JScipt, let me know and I can convert this if you need
me to.)
- Right-click on the button and choose Properties
- Click the Edit Form Code button...you should see something like this:

Sub CTRL3_5_OnClick(eventObj)

End Sub

- Add this code to the procedure:

Dim objEmpDS
Dim strSQL
Dim objEmpID

'Get a reference to the Employees data connection
Set objEmpDS = XDocument.DataAdapters("Employees")

'Get the initial SQL command
strSQL = objEmpDS.Command

'Get a reference to the txtEmpID field on the form
Set objEmpID = XDocument.DOM.selectSingleNode("//my:myFields/my:txtEmpID")

'Reset the command of the data connection to include a WHERE clause
objEmpDS.Command = strSQL & " WHERE EmployeeID = " & objEmpID.text

'Now query the connection
objEmpDS.Query

'Reset the connection to the original SQL command so we are ready for a
different query
objEmpDS.Command = strSQL

- The entire code procedure should now appears as follows:

Sub CTRL3_5_OnClick(eventObj)
Dim objEmpDS
Dim strSQL
Dim objEmpID

'Get a reference to the Employees data connection
Set objEmpDS = XDocument.DataAdapters("Employees")

'Get the initial SQL command
strSQL = objEmpDS.Command

'Get a reference to the txtEmpID field on the form
Set objEmpID = XDocument.DOM.selectSingleNode("//my:myFields/my:txtEmpID")

'Reset the command of the data connection to include a WHERE clause
objEmpDS.Command = strSQL & " WHERE EmployeeID = " & objEmpID.text

'Now query the connection
objEmpDS.Query

'Reset the connection to the original SQL command so we are ready for a
different query
objEmpDS.Command = strSQL
End Sub

- Save the code and close the Script Editor
- Preview the form
- Enter a 1 in the txtEmpID box and click the button - you should get
"Davolio" as the last name!

Does this work as you need?

Thanks,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Kenneth]

Hi Scott,

I want to say a big thank you before I try your instruction. Greatly
appreciate it. So you are not at Seattle, Washington? It is early morning
there now. Or you still work when you get home?

 

[Scott L. Heim [MSFT]]

Hi Ken,

I am in NC and typically try to start a little early...just to enjoy the
quiet!

Do let me know if those steps work for you.

Take care,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Kenneth]

Hi Scott,

I just tried and it works great. It is not that difficult now, but only
after you showed me how.

I followed the all of the 16 labs and most all of the technical articles at
MSDN infopath dev site, but still could not figiured out for my scenario
because all of them shows just how to pull data to a repeating table or a
drop-down list. In real life, how ever, that is too simple. A serious
application will always try to connect to multiple database dynamically.

Scott, do you have plan to write a book on InfoPath? I have a title for you.
"101 tasks of InfoPath". You just need to put all of your answers in the
book. It will help users, help Microsoft too. I would also suggest you claim
copyright for your answers.

Also I want to say, I would likely not choose InfoPath for my project if
there is no people like you to answer questions here.

Thanks you again.

Ken

 

[Scott L. Heim [MSFT]]

Hi Ken,

Thank you for the update - I am certainly glad to know you were able to get
this working!

I will make sure to relay your comments to our content team so we can look
at providing more in-depth samples.

Take care Ken and thank you for your kind words!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter]

Hi Scott

Please excuse me for jumping on someone elses posting.

Would there be any chance on this earth that you could convert the code to
JScript as you say your happy to in your posting. I didnt know you could
choose and I have tried to change mine to VB but it wont allow me to?

I have tried your code and it does what I want it to do, even though this is
not my posting?

Kind Regards

Peter

 

[Scott L. Heim [MSFT]]

Hi Peter,

No problem! Here is my entire button "OnClick" code, converted to JScript,
for the same functionality in this thread:

function CTRL3_5::OnClick(eventObj)
{
//Get a reference to the Employees data connection
var objEmpDS = XDocument.DataAdapters("Employees");

//Get the initial SQL command
var strSQL = objEmpDS.Command;

//Get a reference to the txtEmpID field on the form
var objEmpID = XDocument.DOM.selectSingleNode("//my:myFields/my:txtEmpID");

//Reset the command of the data connection to include a WHERE clause
objEmpDS.Command = strSQL + " WHERE EmployeeID = " + objEmpID.text;

//Now query the connection
objEmpDS.Query();

//Reset the connection to the original SQL command so we are ready for a
different query
objEmpDS.Command = strSQL;
}

I hope this helps!

Best Regards,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter]

Hi Scott

Thanks for your converted code, however I have had to change a lot of the
document so I started again and set it for VB.

Wonder if you are able to troubleshoot my error?

I run the Vb script and I get the following error:

A run-time error has occurred.
Do you want to debug?

The following error occurred:

Object required: 'objEmpID'
File:script.vbs
Line:34

Here is the code:

' This file contains functions for data validation and form-level events.
' Because the functions are referenced in the form definition (.xsf) file,
' it is recommended that you do not modify the name of the function,
' or the name and number of arguments.

' The following line is created by Microsoft Office InfoPath to define the
prefixes
' for all the known namespaces in the main XML data file.
' Any modification to the form files made outside of InfoPath
' will not be automatically updated.
'<namespacesDefinition>
XDocument.DOM.setProperty "SelectionNamespaces",
"xmlns:my=""http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-09-13T09:38:31"""
'</namespacesDefinition>


'=======
' The following function handler is created by Microsoft Office InfoPath.
' Do not modify the name of the function, or the name and number of arguments.
'=======
Sub CTRL14_OnClick(eventObj)
Dim objEmpDS
Dim strSQL
Dim objEmpID

'Get a reference to the Employee data connection
Set objEmpDS = XDocument.DataAdapters("Products")

'Get the initial SQL command
strSQL = objEmpDS.Command

'Get a reference to the txtEmpID field on the form
Set objEmpID = XDocument.DOM.selectSingleNode("//my:myFields/my:txtEmpID")

'Reset the command of the data connection to include a WHERE clause
objEmpDS.Command = strSQL & " WHERE Code = "& objEmpID.text

'Now query the connection <BR>
objEmpDS.Query
'Reset the connection to the original SQL command so we are ready for a
different query
objEmpDS.Command = strSQL
End Sub

Line 34 refers to this line ( I think?)

objEmpDS.Command = strSQL & " WHERE Code = "& objEmpID.text

Any help would be greatfully received.

Kindest Regards

Peter

 

[Peter]

Hi Scott

OK - I think I know what it is.

My data source looks like this:

-myFields
DateRequested
RequestersName
group1
-UseSimilarProductCode (this is an optional section)
txtEmpID
Desc
Adesc1.....

The code has this:

Set objEmpID = XDocument.DOM.selectSingleNode("//my:myFields/my:txtEmpID")
but perhaps its should be somelike this (except I dont know the syntax?

Set objEmpID =
XDocument.DOM.selectSingleNode("//my:myFields/UseSimliarProductCode/my:txtEmpID")

Kindest Regards

Peter