SQL Database Integration

Re: SQL where

Mik — Thu, 25 Aug 2005 00:33:33 GMT

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

Re: SQL where

Mik — Wed, 24 Aug 2005 02:44:40 GMT

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?

Re: SQL where

Mik — Tue, 23 Aug 2005 23:33:06 GMT

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?

Re: SQL where

Patrick Halstead — Tue, 23 Aug 2005 05:36:01 GMT

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

SQL where

Mik — Tue, 23 Aug 2005 04:10:49 GMT

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