locked
Receive Data from SQL Server Database using InfoPath 2007 RRS feed

  • Question

  • I have created the InfoPath to receive the Data from SQL Server Database using InfoPath 2007. I have created two Data Connection [ One for my SQL Server DataBase Table and another for my MOSS Site Task List]

    Then i used few Text Box control and set the value as SQL DataBase Table Field , in that i used Filter Condition to Check a Unique ID vale of DataTable Field with the Task Field Value ( I am having the same values in both places).

     

    If i Check the Preview of the form its working fine and showing the proper details

    BUT when i used the Form in Workflow Task its not opeing with the data and showing the error msg. as "An error occurred accessing a data source" once i click 'Continue' its opeing the Form without the Data.

    can any one help me in this, as i need to solve this issue urgently

     

    Thanks in advance

    Regards,

     

    Tuesday, September 18, 2007 3:44 PM

All replies

  • The diagnostic log will provide more information. 

     

    http://paulgalvin.spaces.live.com/Blog/cns!1CC1EDB3DAA9B8AA!154.entry

     

    Tuesday, September 18, 2007 6:38 PM
  • Thanks a lot

    I went to LOG File , but i am unable to simulate much

    its giving some error like :

    The following query failed: ESS_TRANTB (User: SPECTRA-NR7RAJ7\milind, Form Name: LMSTask, IP: , Request: http://spectra-nr7raj7:30925/_layouts/WrkTaskIP.aspx?List=93ac1688-8975-495e-b32c-209eefc85a94&ID=288&Source=http://spectra-nr7raj7:30925/SiteDirectory/lmstest/Lists/Tasks/AllItems.aspx, Form ID: urnTongue Tiedchemas-microsoft-comSurpriseffice:infopath:LMSTask:-myXSD-2007-09-18T12-59-27, Type: DataAdapterException, Exception Message: Current configuration settings prohibit embedding username and password in the database connection string.)

     

    What configuration settings i have to do ?

     

    Regards

     

    Wednesday, September 19, 2007 9:12 AM
  • Ahh, the diagnostic log comes through again Smile

     

    Try going to:

    1. Central Admin

    2. Configure InfoPath Forms Services

     

    Check off "Allow embedded SQL authentication"

     

    That may do it.

     

    You may also want to research ".udcx" files.

     

     

     

    Wednesday, September 19, 2007 11:03 AM
  • Thanks a lot Paul for replying

    But "Allow embedded SQL authentication" is already in OFF mode at my end.

    I also tried to receive the Data using Web Service in InfoPath Form. Its not giving any error while opeing but not showing the actual / desired result

    for example : I have a web service, if i enter Employee Code it returns Employee Name.

    That Web Serivice i used in Data Connection in InfoPath Form. I also created one for Data Connection for Task List Item in the same InfoPath Form

    I used one Text Control to display the Employee Name. Then i set the Default Value under Properties of Text Box using Insert Field or Group Option. I selected the Web service varible name used to store the employee Name and in Filter Condition i checked the condtion to match the Web Service Employee Code with Task List Employee Code (thinking that on Task Click the related Employee Code system will read and that will be checked against the Employee Code of Web Service)

    But when the Task in created and i click on the task Its always showing same Employee Name in the InfoPath irrespective of the Different Employee Code in the Task List Items.

     

    Hope i am explaing what i am doing without much confusion !

     

     

    Wednesday, September 19, 2007 1:15 PM
  • I didn't type that very well earlier.  I meant to "check" the toggle box for allow embedded sql authentication.

     

    I'll try and response in greater detail later today.

     

    Good luck,

    Wednesday, September 19, 2007 1:34 PM
  • Thanks a lot Paul

    Yes, as you said i checked ON the option Embedded SQL Authentication and Authentication to data sources (user form templates) in CA. Now the error is not coming !!!

    But as i explained in my last post.. its not showing the relavent Data

    If only 1 Task is present in the Task List.. its reading the Employee Code and displyaing the Name in InfoPath Form

    But if more than One tasks are available, for any task selected, its showing the same Employee Name though in the Task List Employee Codes are different !

    how to overcome this problem. can u suggest something.

    it would be gr8 help for me

    thanks a lot again

    regards

     

    Thursday, September 20, 2007 6:00 AM
  • Dear Paul

    I am eagerly waiting for your reply

    actually i replied to your last message, i am able to view the InfoPath Form after doing the changes as u said in Central Admin.

    But i think the Values are not getting refreshed at the time of Form Load in InfoPath Task Form

    IF only one taks is available in Task List it shows the proper values.. but when tasks are more than one.. its alwasy showing the same values for all the Forms once the task is created

    Please guide me to overcome this problem

    regards

     

     

    Friday, September 21, 2007 10:23 AM
  • I think you are very close.  The next steps is to ensure that the filtering is set up.

     

    There are several dialog boxes you have to configure to do this right.

     

    Based on chain in this discussion, it seems like you may have already tried this, but maybe some small mistake was made?

     

    If have a drop-down list, I can specify its external data source.

     

    I then click on the button next to "entries".

     

    I navigate through the tree and and select the value I want to map.

     

    I close all that out.

     

    I then pull up the properties for the dropdown again.

     

    Click on Rules.

     

    Add a rule, "Set a field's properties"

     

    Click on f(x) next to Value.

     

    Click on insert field or group.

     

    From here I can select "Filter Data...".

     

    That's where you need to specify your filtering and that should solve your problem.

     

    HTH,

     

     

     

     

     

     

     

     

     

     

    Friday, September 21, 2007 12:24 PM
  • Thanks a lot Paul for your efforts and replying my post.

    But i am still unable to resolve the issue.. i did few things as per your advice.. let me write down in details what i am doing

     

    I created Infopath Form on one system and pubished to the NetworkLocation, fm there i copied this form in to my feature directory of the Workflow..

    I am using only TWO Text Box Controls in my InfoPath Form. and added TWO External Data Connection. (One for SQL server and another for Task list Item)

    In Text Box Property .. i mapped the SQL DataTable Field Name under 'Default Value' option.

    Default Value : Value = @TRA_EMPCODE (This is my SQL Server DataTable Column Name)

    Then I opened Rules Option - Add Action -> Action : Set a Field's value -> Field : @TRA_ID (This is my SQL DataTale Column Name which i want to Compare with the Task List Column Name) -> Value : @TRA_ID[. = @TRANID] (TRANID is my Site Task List Column Name which is having Unuqie Value which i am assin at the time of Task Create). I arrived Value Equition by clicking f(x) next to Value -> Insert Field or Group -> Then i selected TRA_ID SQL Data Source -> Filter Data -> Specify Filter COndition -> TRA_ID (From SQL Data SOurce) is equal to TRANID (From Task List Data Source)

     

    With this i have done with my InfoPath then i published the same on the Server Network Location.

     

    Then i created Two Tasks in my MOSS Site.. having TRANID values as 1 and 2. If the user clicks first taks my InfoPath Text Box should show the Employe CODE related to TRANID 1 (for ex. say 100) and if user clicks second task it shold display different employee Code (for ex. say 101).. but it always shows only first value for both the tasks

     

    Kindly advice what mistake i am doing. I need to finish this activity urgently.

     

    Thanks again

     

    WIth Regards

    Saturday, September 22, 2007 9:32 AM
  • Dear Paul

    With your inputs i am able to proceed upto some extent. but not 100%..

    presently i am able to MAP the Employee ID from Task List to DataBase Table and fetch the related Data/information.

    But Only one problem now.. that IF I am having more than ONE Task in the Task List Item.. its always showing the Information / Data related to 1st Task selected by the User. I think on Task Click the System is not reading the Active Task / Selected Task ID.. or Its nor refreshing .. may be.. i am not sure

    Can u please guide me to overcome this problem

    Thanks in advance

    Regards

     

    Tuesday, September 25, 2007 7:19 AM
  • One thing I would try is to re-query SQL via a rule.  Add a button or put an "on change" rule (whatever infopath lets you do given the form that you've designed) and re-run the query with updated parameters.

     

    Tuesday, September 25, 2007 1:31 PM
  • I lost you Paul

    Can you please explain me in details how i should add the Rule with "on change" condition for a textbox.

    I was unable to find any such option under "Rules"

    Kindly advice

    with regards

     

    Wednesday, September 26, 2007 5:58 AM
  • You're right, there is no rule for text boxes, so you need to add a button or fire the rule in response to a change to some other control.  What you do depends on the details of your form.  I would first try a button and see if that solves things for you by re-querying and then if that works, then decide if a button is the best thing, or if there's a natural place to do it via another control on the form that does support the necessary rule (such as a lookup).

    Wednesday, September 26, 2007 10:45 AM
  • Thanks Paul for the reply and for the efforts you are taking to solve my problem

    Actually i tried with different ways also..  For the TextBox (name : field1) i set Rule Condtion as field1="9" (here 9 is the Position Number of EMPID Column in TaskList) and set Action as field1=@EMPID (EMPID is the Column Name in Task List) But still i am not able to fetch the Exact value

    For my satisfaction, i will brefi below once again my requirement

    I have a Task List having Items like below

    Tile          Assigned To          Status                  EMPID   

    Task1      Milind                    Not Started             001

    Task2      XYZ                       Not Started             002

     

    Then I have a InfoPath Form with only ONE Text Box. which i mapped with the EMPID Field of the TaskList

     

    If User clicks Task1, the InfoPath Form Text box should display the value '001', and if user clicks Task2, the InfoPath Form Text box should display value '002. but its howing always '001' if any task is selected.

     

    The requirement is very simple but for me the solution is not !!

    Thanks once again for helping me and looking forwared to solve this issue with your inputs

     

    With Regards

     

     

    Wednesday, September 26, 2007 12:59 PM
  • I think that your action needs to also re-run the query. 

     

    In addition to what you did, add another action called "Query a data connection".  It should then list your SQL connection and hopefully, it will do what you want.

     

    You may need to do a 3rd action, which is to assign the value from the just re-queried data source to the target field or that may happen automatically.

     

    Thursday, September 27, 2007 1:24 AM
  • Thanks Paul for the reply and for the efforts you are taking to solve my problem

    Actually i tried with different ways also..  For the TextBox (name : field1) i set Rule Condtion as field1="9" (here 9 is the Position Number of EMPID Column in TaskList) and set Action as field1=@EMPID (EMPID is the Column Name in Task List) But still i am not able to fetch the Exact value

    For my satisfaction, i will brefi below once again my requirement

    I have a Task List having Items like below

    Tile          Assigned To          Status                  EMPID   

    Task1      Milind                    Not Started             001

    Task2      XYZ                       Not Started             002

     

    Then I have a InfoPath Form with only ONE Text Box. which i mapped with the EMPID Field of the TaskList

     

    If User clicks Task1, the InfoPath Form Text box should display the value '001', and if user clicks Task2, the InfoPath Form Text box should display value '002. but its howing always '001' if any task is selected.

     

    The requirement is very simple but for me the solution is not !!

    Thanks once again for helping me and looking forwared to solve this issue with your inputs

     

    With Regards

     

     

    Hello Umesh, 

    I would like to support you if you havnt had a solution to this but if you have, kindly post the answer and mark it to benefit others on this thread. 

     


    Wisdom is of God. James 1:5
    Friday, July 9, 2010 10:32 AM