SQL Database Integration

Re: Using Visual Basic code to execute stored procedures in SQL

ZhenYuan — Mon, 10 Jan 2011 00:55:57 GMT

Hi Josera,

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

Re: Using Visual Basic code to execute stored procedures in SQL

josera — Sat, 08 Jan 2011 09:33:11 GMT

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

Re: Using Visual Basic code to execute stored procedures in SQL

ZhenYuan — Fri, 07 Jan 2011 11:27:33 GMT

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

Re: Using Visual Basic code to execute stored procedures in SQL

josera — Fri, 07 Jan 2011 11:12:20 GMT

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

Using Visual Basic code to execute stored procedures in SQL

ZhenYuan — Thu, 04 Nov 2010 10:23:40 GMT

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:

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

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

myAdoQueryConnection.Execute()

Before going into the details of this coding, can I check how I should configure my DataConnection? Right now, its a Receive data connection to SQL Database where I use Windows Authentication as login method. I randomly chose a table in my DB and is able to draw data from that table when my form is in preview mode. However, once I load the form into my server and run the form from DBXL, I am not able to retrieve any data from my table. Beats me why...

That aside and assuming I have a solution for that, can I then use the code above to run my stored procedure SP_Delete_Folder?

Thanks in advance!