SQL Database Integration

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

Jimmy — Thu, 11 Nov 2010 03:50:30 GMT

Most likely, any SQL statements that make changes to your data are going to be affected by the issue I referenced here:

Re: Inserting multi selection to database via DBXL

and I think the workaround there will work for just about any situation you would want.

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

ZhenYuan — Thu, 11 Nov 2010 01:07:39 GMT

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!

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

ZhenYuan — Thu, 11 Nov 2010 00:54:52 GMT

This is my full button code:

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

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

myAdoQueryConnection.Execute()I'm not sure why the double quotes came in my post but my code is using single codes so should be ok. If I run the command in sql, it updates fine.

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

Hilary Stoupa — Wed, 10 Nov 2010 20:00:19 GMT

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.

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

ZhenYuan — Wed, 10 Nov 2010 03:14:53 GMT

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

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

Hilary Stoupa — Tue, 09 Nov 2010 19:42:16 GMT

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?

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

ZhenYuan — Mon, 08 Nov 2010 02:14:04 GMT

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"

Me.CreateNavigator().SelectSingleNode("/my:myFields/my:DeleteGroup/my:test", Me.NamespaceManager).SetValue("test")

myAdoQueryConnection.Execute()

We put in the "Test" field and were able to populate that field with "test" but the myAdoQueryConnection.Execute() did not seem to execute our update statement. Did we do it wrongly?

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

ZhenYuan — Sun, 07 Nov 2010 06:45:30 GMT

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.

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

Hilary Stoupa — Fri, 05 Nov 2010 15:43:35 GMT

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.

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

ZhenYuan — Thu, 04 Nov 2010 09:14:18 GMT

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!

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

ZhenYuan — Thu, 04 Nov 2010 08:30:54 GMT

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!

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

Hilary Stoupa — Fri, 05 Feb 2010 16:16:19 GMT

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.

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

Hilary Stoupa — Fri, 05 Feb 2010 16:14:40 GMT

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.

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

blaher — Thu, 04 Feb 2010 01:39:03 GMT

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.

Re: Infopath 2003 Stored Procedures in Visual Basic .NET

Hilary Stoupa — Wed, 03 Feb 2010 23:51:09 GMT

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

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