Using Visual Basic code to execute stored procedures in SQL - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Using Visual Basic code to execute stored procedures in SQL

Last post 01-09-2011 04:55 PM by ZhenYuan. 4 replies.
Page 1 of 1 (5 items)
Sort Posts: Previous Next
  • 11-04-2010 03:23 AM

    Using Visual Basic code to execute stored procedures in SQL

    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!

  • 01-07-2011 03:12 AM In reply to

    • josera
    • Not Ranked
      Male
    • Joined on 07-28-2010
    • Sacheon (South Korea)
    • Posts 9

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

    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

  • 01-07-2011 03:27 AM In reply to

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

    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

  • 01-08-2011 01:33 AM In reply to

    • josera
    • Not Ranked
      Male
    • Joined on 07-28-2010
    • Sacheon (South Korea)
    • Posts 9

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

     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

  • 01-09-2011 04:55 PM In reply to

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

    Hi Josera,

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

Page 1 of 1 (5 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.