InfoPath Dev

Hi,

I am developing a form which will retrieve the users information from a table based on their username. I can get their username, but how do I query the database conditionally?
I have set it up as a secondary data source (will be submitting to another database)

I understand you can add additional commands to the query in the script (currently using vbscript) but I cant find an example.

Also, there is a place to add a 'where' condition in the data connection itself, but to use this, I would have to grab a fields value first to query against, which would require a script anyway..

Can you help me in developing these somewhat simple where statements? I am lost.

Thanks in advance,
Mik

Hi Mik,
When you created the form did you do so from a SQL connection? Was the main data source created from the database schema, or is it something you created? In general, you will have to set a query field and programmatically (via rules) call the SQL query adapter. This assumes that you use a main data source which was created from the DB. If you have your own data source, then you will have to write code to do this.

Patrick Halstead [InfoPath MVP]
InfoPathDev

I didnt create it from an existing SQL table because one didnt exist (infact the functionality of it wasnt really decided at that point)

I understand that it can be done programmically by specifying what data connection you want to use, then adding to its default query (as a string) where i can use a variable in the WHERE condition, then execute the connection.

I have seen code to do this before, but at that point I was looking for an insert command to a secondary data source, and so i didnt bookmark it, or copy it down.

I found something similar perhaps in the developers reference of infopath help..
var objADOAdapter;
objADOAdapter = XDocument.DataObjects("CityDropDownList").QueryAdapter;
XDocument.DataObjects("CityDropDownList").Query();
objADOAdapter = null;

But i would need to modify the query itself
Any ideas?

Ok I think I have the first bit worked out:

'get username
dim objWscript
set objWscript = CreateObject("WScript.Network")
usr = objWscript.UserName

'create query
dim objDataAdapter
set objDataAdapter = XDocument.DataObjects("ad_details").QueryAdapter
objDataAdapter.Command = objDataAdapter.Command + " where Windows_Logon = '" + usr + "'"

'query database
objDataAdapter.Query()

But I thought that the results would automatically populate a drop down list box so that I could run another query based on that.
Any ideas how to transfer this off into the secondary data source, or any ideas why this isnt working?

Me replying to my own post again.
It seems somethings wrong with the database end of this.

The table 'ad_details' is actually a view of our Active Directory user information.
Apparently not all of the rows/users are populating and so some queries dont work.
I might be back with furthur queries depending on what happens.

Thanks,
Mik