InfoPath Dev

Hi,

We are using Infopath 2007 and I'm having difficulty trying to code my form to run some stored procedures from my SQL server.

What I have in my button code is:

myAdoQueryConnection.Command = "exec [formdb].[dbo].[SP_Delete_Folder]"

Hi ZhenYuan,

I use VBScript instead of Visual Basic but perhaps this can help you because of the similarities. This is what works for me to run a stored procedure from Infopath 2007 in SQL Server 2008:

' Run the stored procedure [UpdateModifiedDay] in the DB
    dim cn
    set cn = XDocument.QueryAdapter
    dim originalCommand
    originalCommand = cn.Command                ' Save current DB access command
    cn.Command = "EXECUTE UpdateModifiedDay"
    cn.Query
    cn.Command = originalCommand                ' Restore previous DB access command
    set cn = nothing
 

In the stored procedure you have to add this instruction so it works from Infopath:

 SET implicit_transactions OFF;

That's all it takes. Hope it helps.

Josera

Hi Josera,

Thanks for your reply! We have since took another approach for our forms but I will keep your advise in mind the next time I design another form. Being able to run stored procedures from the form is a very useful feature indeed.

Just curious, is it a must to restore previous DB access command and set cn = nothing? Otherwise what will happen?

Thanks,

Zhen Yuan

 Hi Zhen Yuan,

the previous DB access command is the one that you defined when you selected Database as the main data source. It is used by your form to retrieve and store data from/to the main source database. If you don't need to do any further retrieve or restore operation after you run the stored procedure, then you do not need to restore the previous DB access command.

cn is an object variable so when you are done with it, the instruction cn = nothing destroys the object and frees up the memory it was taking up.  It also avoids memory errors that may show up keeping object variables longer than necessary.

Josera

Hi Josera,

Thanks, will try that out for my next form. Thanks!