InfoPath Dev

Hi,

I've been searching the whole day for this but all I have found are stuff on C# or Infopath 2007 and 2010.  I'm limited to Infopath 2003 and Visual Basic .NET, therefore those are my only options.

My question is I'm trying to make a data connection to my database and then executing a stored procedure with an parameter.

I was able to grab the parameters from the infopath form, but I have no idea how to query the database.

I was thinking it would be

this.XDocument.QueryAdapter.Command = querySQL

(where querySQL is my stored procedure, but it didn't seem to work).

Thanks in advance!

I think more along these lines, for VB...

Dim myDataObject As ADOAdapterObject = thisXDocument.DataObjects("Name of Data Source")
myDataObject.Command = querySQL

Thanks Hilary,

For the "Name of the Data Source", doesn't that have to be a predefined Data connection in Infopath?  What I find is that these data connections all require a table to be added.  However for my form, I only want to execute a store procedure that already queries a table.

I tried using the conventional VB programming where:

            Dim conn As New ADODB.Connection
            Dim cmd As New ADODB.Command

            conn.ConnectionString = "driver={SQL Server};server=****;uid=****;pwd=****;database=database"
            conn.Open()
            cmd.ActiveConnection = conn

            Dim startDateXMLNode As IXMLDOMNode = thisXDocument.DOM.selectSingleNode("//my:txt_StartDate")
            Dim startDateString As String = startDateXMLNode.text.Replace("-", "")
            Dim querySQL As String = "EXEC database.dbo.Export " + startDateString

            Dim ResultSet As New ADODB.Recordset
            cmd.CommandText = querySQL
            cmd.Execute()

However using this method I get an error saying:

System.Security.SecurityException
That assembly does not allow partially trusted callers.

   at Timberline_Timesheet_Export.Timberline_Timesheet_Export.btn_Export_OnClick(DocActionEvent e)
   at Microsoft.Office.Interop.InfoPath.SemiTrust._ButtonEventSink_SinkHelper.OnClick(DocActionEvent pEvent)

A little confused with that, so I moved away and used your code.  Except it needs a data source which I'm not sure how to create.

You create a data source in InfoPath by adding a secondary data connection to your form. You can find links to help with activity here.

Oh, and I should have been more clear -- when you create a connection to SQL, you can actually edit the SQL to something like "exec dbo.mystoredprocedure etc" and then change the command in code to pass in your parameters from your form. But creating a data source this way prevents you from having to make your form full trust.

Hi Hilary,

 I have the same question here. When we need to have a data connection for this case, what you are saying is we can just create a Database data connection, select a database and its table and while this makes the data connection limited to the table, we can use code to run select statements to another table? I'm also have the intention to run a store procedure to logically insert/update values to several tables so the ability to run a "exec SP_Test" will be great!

Last question, when we do the data connection to database using data connection wizard, we are prompted to input the Log on credentials. Can we use Windows Authentication? My concern is that when we roll out the forms to users, their credentials do not have access to the SQL tables. If this is the case, I should use a system username and password right?

Thanks!

I'm am also having a weird output here when I use my database data connection. On preview mode, I can draw data from my database data connection based on the table it is tagged to.

However once I launch my form from server (go to DBXL and use the link to open form), I can no longer draw any data from my database DC. It seems I cannot even access the database. I am using Windows Authentication as my log in method for this DC.

Any clues on this mystery? Thanks!

Hi:

Yes, I'm saying to create a database connection and then use code to modify the command the connection uses -- this prevents having to make your form full trust, because you are using an IP data connection. In general, I agree with you that it would be best practice to use Windows Authentication when you create the data connection. I'm using IP 2010, and it has that as an option when you create the connection. But this does mean you have to give your form users read access to the DB.

As to your question with DBXL -- I've not published a form with a DB connection to DAT before, so I gave it a try. My form opened from DAT with data. I'll email our support alias and see if there is anyone who has an idea why this isn't working for you.

 Hi Hilary,

Thanks for your reply! Opening up our DB with read access to all users might not be such a good idea, in any case, I'll give it a try with an account which has read access.

Will we be able to use code to run stored procedures if we use a web service connection to our database? Because we are able to use web service to pull data from our DB. Was wondering if its because the web service uses an DB Admin service account while if we use a DB connection, its using another account, probably the user account, to try to draw data and thus causing the failure.

Hi again,

Just tested and somehow we are able to connect to the database table which we configured the DB data connection (we gave rights to the DBXL service account to our database directly. Using web service calls, this was not required). However, we are not able to execute any Adoconnection.command. Our code is below:

myAdoQueryConnection.Command = "update formdb..Group set IDCode = '1111' where IDCode = "3435-235"

Are there any errors when you execute? I'm guessing that the reason you you are setting a field value is simply to verify your code is running? Did you run in debug so you can see what is going on?

There were no errors on execute. But the table column values were not updated. Is my ADOQueryconnection coding correct?

You've not put all your code in the thread. But given what you have posted, I'd assume that the SQL query would fail:

myAdoQueryConnection.Command =

"update formdb..Group set IDCode = '1111' where IDCode = "3435-235"

The IDCode seems to have double quotes around it, which I'd expect SQL not to like.

What happens if you run that update statement in SQL Server Management Studio? Is "formdb..Group" your table name?

EDIT:

I see that Jimmy Rishe has answered this -- it has to do with the way IP is handling the Update command.

This is my full button code:

Dim myAdoQueryConnection As AdoQueryConnection = Me.DataConnections("ETS_Group_Engagement")

Hi Hilary,

Just read Jimmy's reply and yes formdb..group is my table name.

I guess I'll use a table trigger to do my updates, but nonetheless, I'm keen to find out what statements can I replace my command with? Insert statements? If I didn't remember wrongly, I did try to run a stored proc before but similarly, it did not execute. Could it be because my SP has an update statement?

Thanks!