Infopath Submitting to SQL Database -- Multiple Selection List Box? - InfoPath Dev
in

InfoPath Dev

Having trouble finding a blog or post that answers your question? Check out our Custom Search Page

Infopath Submitting to SQL Database -- Multiple Selection List Box?

Last post 04-21-2017 03:20 PM by ShelbyH. 12 replies.
Page 1 of 1 (13 items)
Sort Posts: Previous Next
  • 04-20-2017 04:19 PM

    Infopath Submitting to SQL Database -- Multiple Selection List Box?

    I currently have a filler form with query and submit features to a SQL Server database (directly. not with a web service). I'm noticing when I'm dragging my SQL tables onto my form, regardless of how the table is set up, the control is a text box. I've gotten into the habit of changing them, but a lot of my original form design uses multiple selection. We have a table in SQL set up with all the values, and unique IDs, but the form will not let me use the multiple selection control. Here is what I've tried so far. 1) I've tried manually changing the field control to the multiple selection control. It states the field has to be repeating. 2) I've tried going into the properties of the field, but everything is grayed out and I cannot change the field to repeating 3) I've tried manually creating my own multiple selection control, having it query the selections from the database then trying to bind it or move it into the field it needs to be. I either get told that the group cannot accept changes, or that I have to change the field to repeating, which it does not let me do. I've seen forms in the past with multiple selection list boxes but I haven't found a SQL specific tutorial to do so. ANY help or points in the right direction would be amazing.
  • 04-21-2017 12:04 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    Anyone have any ideas?
  • 04-21-2017 12:13 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    In InfoPath, a multiple selection list box is bound to a repeating field. But with a database bound form, if you had an associated table to store the values from the multi select, you'd have a repeating group for the associated table, and you won't be able to bind a multiple selection list box to that. I can't think of a way to mimic this functionality without code (or qRules) - you could add a repeating field to the main data source, outside the SQL bound groups, then hook a multi select to that, then on submit populate the related SQL group (inserting the number of needed rows and populating values). Then when you queried, you'd have to do something similar to populate your multi select. Honestly? The easiest approach would be to stick a dropdown in a repeating section so the user can just insert rows and select values.
    Hilary Stoupa

  • 04-21-2017 01:18 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    Thanks! I've tried this just now and I'm getting the following error "InfoPath cannot submit the form. An error occurred while the form was being submitted. Violation of PRIMARY KEY constraint 'PK_tblDistrList'. Cannot insert duplicate key in object 'dbo.tblDistrList'. The duplicate key value is ( )." Is this because of the duplicate drop downs?
  • 04-21-2017 02:04 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    Are you submitting blank values in them? Is the drop down bound to the primary key field? Or does the table have an identity column?
    Hilary Stoupa

  • 04-21-2017 02:10 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    My SQL guy did show me that we need some type of unique ID for each selection, or it wont go through. The drop down is bound to a primary key field and when I add a unique ID it doesn't work if I do more than one option. Is there another approach I need to take?
  • 04-21-2017 02:15 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    Any chance you could attach a copy of your form to a reply under Options?
    Hilary Stoupa

  • 04-21-2017 02:21 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    Sure thing. Its simply a test form so it's very raw. I only have what I'm trying to test in it currently.
  • 04-21-2017 02:37 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    Also. Is there a way to create an automatically incrementing ID in infopath that I could possibly use to avoid duplicates in the drop down?
  • 04-21-2017 02:51 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    Your DB admin should make distListID an identity type in the database. Then when the form is submitted, you'll automatically get a unique ID in the DB.
    Hilary Stoupa

  • 04-21-2017 03:08 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    We do have that. Do I need to tie the drop down control to that instead of the Name field?
  • 04-21-2017 03:18 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    No. Your dropdown should be bound to the name, as it is. Is the ID column your primary key? Because your form looks right and your error message indicates an issue with the PK field. If the ID field is an auto-incrementing integer and you are submitting new rows via the form, the submit should succeed.
    Hilary Stoupa

  • 04-21-2017 03:20 PM In reply to

    Re: Infopath Submitting to SQL Database -- Multiple Selection List Box?

    I can definitely test it out. Thank you so much for confirming this with me. I'll keep you posted on if this solves this on monday.
Page 1 of 1 (13 items)
Copyright © 2003-2017 Qdabra Software. All rights reserved.
View our Terms of Use.