locked
Query with InfoPath2010 and SQl Server RRS feed

  • Question

  • Hey in Infopath im wondering if the following is possible

    1) I am able to pull First and Last name from a user via a webservice, and I prefill my First Name and Last Name textbox from this. (This works)

    2) Pull these prefilled details from textboxes, and use them in a SQL query in a Database to pull out value from another table i.e. select customerId from table x where firstname = 'valuefirstnameinfopath' and lastname = 'valuelastnameinfopath'

    3) Insert the retrieved identifier into a seperate SQL Server table ?

    Just confused what template/method to approach in infopath to achieve this ?

    Thursday, December 29, 2011 5:57 PM

All replies

  • Hi Velnias,

    Yes, you can do this.  First off how will you be pulling the SQL data - will it be direct SQL secondary data connection or will you be using a web service?

    Also, same question for submitting the data - do you plan on using a web service to submit the data to the other SQL table or were you hoping to do so by a direct SQL connection from the form?

    Thanks,

    Melissa

     

    Thursday, December 29, 2011 6:17 PM
  • Hey Melisaa,

    I will be pulling data from a SQL Data connection I guess, I dont have any webservices for this

     

    For submitting I would like to use a direct SQL Connection again to

    Thursday, December 29, 2011 6:32 PM
  • Hi Velnias,

    Ok, this is still doable.  BUT in order to submit to a SQL database it must be bound to the main datasource.  You will need to start your form over and choose the "Database" form option then select the DB & table you want to submit to.  You will of course still be able to create other fields to hold data outside of the database.  Kind of a pain, I know... I had to do the same thing with the first database form I ever made.  There use to be a way (at least in InfoPath 2007) that you could modify the main datasource so that you wouldn't lose all the fields and logic you have in your form.  I don't see that option in 2010 but I may just be missing it.

    As far as setting the matching value you will be pulling more information from the table than you need in the query, but we'll single out the customerid you need by use of filtering. 

    Step1 for you though is to get the SQL table you're submitting to in the main datasource.  I'll post more later on the setting the filtered value.

    Melissa

    Thursday, December 29, 2011 6:46 PM
  • Hey Melissa,

    Can I submit via Code aswell, which would kind of ignore the form template used and just do all the good stuff in the form on submit click ?

    Or in the Database form option lets say 20 columns and I only need to ask the user to enter values for 5 can I hide the other 15 and assign default values to these ?

    Thursday, December 29, 2011 7:10 PM
  • Hi Velnias,

    Yes, you can do both of these.  I have never tried submitting to a db via code with a browser form FYI.  Is this a browser form or filler form by the way?

    With the database form option you can most definitely only put the fields you want the user to fill out and or see on the form and the rest of the fields are "hidden".  And you can assign default values to the hidden fields, or you can set the values of these fields based on other selections within in the form.

    But, if you know how to write code may I highly suggest writing a web service for retrieving and submitting the data?  It's much more versatile and it's faster.

    Thursday, December 29, 2011 7:18 PM
  • Hey Melissa,

    Question in my Database template i assigned to main datasource which is a table of lets say 15 columns, and I only want the user to enter data to 5 columns and enter default values for the other 10 and make them hidden from the user how would I do this ?

    Thursday, December 29, 2011 7:19 PM
  • Hi Velnia,

    You simply just don't add the fields to your form's design.  The user will only see what you put on the form.  All of the fields will be in the main datasource but it's up to you to put fields that are bound to them into the view of the form.  The user will never see the main datasource or its structure.

    Thursday, December 29, 2011 7:29 PM
  • Ok what if I dont want to add them to the form but they need a value...do I just add a default value to myFields --> Datafields --> Select Column and give it a default value ?

    Also is it possible to do this all in the code editor I.e. read all the values from the submit and then do whatever I want in the button onclick event ?

    Thursday, December 29, 2011 7:36 PM
  • Yep, that's right - you'd select the column and give it a default value.

    Yes to the 2nd question as well.  In the code editor you can read the values from your main datasource and use them as parameters to submit to SQL to avoid the Database bound form.

    You would read the values like this:

    //Creates the Navigator
    XPathNavigator root = MainDataSource.CreateNavigator();
    
    //Selects the field from the main datasource
    XPathNavigator field1 = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:field1", NamespaceManager);
    

    The your SQL parameter would look something like:

     

    SqlParameter vField1 = new SqlParameter("@vField1", SqlDbType.Char)
    vField1.Value = field1.value
    cmd.parameters.add(field1)
    

    Thursday, December 29, 2011 7:44 PM
  • Hey Melissa,

    Maybe code is the way to go for me, I can make heads or tails of the Infopath structure.\

    Do you have any full examples of code of how you were able to read values from the form textboxes and submit to SQL Server ?

    Thursday, December 29, 2011 7:58 PM
  • Can I convince you to go the web service route at all???

    Here's a small sample of form code:

    //Creates the Navigator
    XPathNavigator root = MainDataSource.CreateNavigator();
    
    //Selects the field from the main datasource
    XPathNavigator field1 = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:field1", NamespaceManager);
    
    XPathNavigator field2 = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:field2", NamespaceManager);
    
    XPathNavigator field3 = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:field3", NamespaceManager);
    
    SqlConnection sqlConnectionString = new SqlConnection("Data Source=DBSERVER;Initial Catalog=DATABASENAME;Persist Security Info=True;User ID=dbUser;Password=dbPassword");
    
    SqlCommand cmd = new SqlCommand();
    SqlDataReader reader;
    cmd.CommandText = 
    "Inser Into tableName (field1, field2, field3) " +
    "VALUES(@vfield1, @vfield2, @vfield3)";
    
     SqlParameter vfield1 = new SqlParameter("@vfield1", SqlDbType.Char);
    vfield1.Value = field1.value.tostring();
    cmd.Parameters.Add(vfield1);
    
    SqlParameter vfield2 = new SqlParameter("@vfield2", SqlDbType.Char);
    vfield2.Value = field2.value.tostring();
    cmd.Parameters.Add(vfield2);
    
    SqlParameter vfield3 = new SqlParameter("@vfield3", SqlDbType.Char);
    vfield3.Value = field3.value.tostring();
    cmd.Parameters.Add(vfield3);
    
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    

     

     

     


    Thursday, December 29, 2011 8:11 PM
  • Hey Melissa,

    Quick question on the code example :

    When you say //Selects the field from the main datasource
     Is that selecting a value from database or is reading a value from a textbox on the form ?

    Thursday, December 29, 2011 8:19 PM
  • Hi Velnias,

    When I refer to the main datasource it means a field on the form.  So, you will need to replace:

    /my:myFields/my:field1                   


    With the actual xpath from your form.  To get the xpath view your fields in the main datasource > click to the right of the field name to get a dropdown > then click "copy xpath"  and paste that over the above.

    Do you have programming experience?  Don't worry I'll still walk you through it if you don't, it's just best if I know what level I'm working with so I know how detailed (or not detailed) I need to be.

    Thanks!

    Melissa

    P.S. I'm going to be unavailable from 5:00pm EST today until Tuesday, January 3rd

    Thursday, December 29, 2011 9:26 PM
  • Yeah I have programming experience.

    Just tried adding a data connection but it doesn't seem to allow

    using

    System.Data.SqlClient;

    Saying namespace data isn't part of client, is there another way of doing this in infopath ?

    Thursday, December 29, 2011 9:49 PM
  • Did you add System.Data.SqlServerCe to your references?
    Thursday, December 29, 2011 9:53 PM
  • Yeah ok done.

    So so a question in general since this is a database form it cannot be browser, but only filler form right ?

    And then because I am adding custom code to it, does everyone who opens it need .net installed on their PC's aswell and have windows authentication to the database ?

    Thursday, December 29, 2011 9:55 PM
  • Yes, that's correct - it can only be a filler form since it has direct database connections.

    Everyone will need at least .net 1.0 installed.  They will also need the .Net Programmability Support for Microsoft Office InfoPath (go to Control Panel > Microsoft Office > Change > Add or Remove Features > Expand InfoPath > Select .Net Programmability Support.  <--- This will have a red "x" on the icon if they do not have at least .net 1.0 installed (this needs to be installed before adding the .net programmability support).  Some users may already have the .net programmability support if a full install was done on their machine and they have .net 1.0 or above.

    You will not need to give them windows authentication to the database if you use a connection string that uses a username and password.

    Wednesday, January 4, 2012 1:46 PM