SQL Database Integration

Re: How to use SQL Server stored procedures in repeating tebles/sections for INSERT's, DELETE's, etc.?

Francis — Wed, 28 Oct 2009 17:21:13 GMT

Thanks for the references. I took a look at them, but may hold attempting this. We're getting a C# programmer; so maybe he can help. I'm going to finish up on the rest of the tables that have data that will need to be inserted, etc. At the end of the week, even if I'm not finished, I'll let the users start to test it. So far I have 4 tables set up and they work well enough (some fine tuning of controls though). I also have 2 secondary data connections to display data. After I get some results back from users, I'll let you know how it's going.

Thanks again.

Re: How to use SQL Server stored procedures in repeating tebles/sections for INSERT's, DELETE's, etc.?

Hilary Stoupa — Tue, 27 Oct 2009 16:04:35 GMT

You can use code and rules, however, rules run before code (in general -- as usual, there is a way around this if you want to get clever). I don't think that it would work for your scenario, though. If you want a way to cancel the delete, I'd think you'd want to do the whole thing in code. Here's a link to a little message box tutorial that also includes info on how to use the dialog result.

Re: How to use SQL Server stored procedures in repeating tebles/sections for INSERT's, DELETE's, etc.?

Francis — Tue, 27 Oct 2009 11:14:59 GMT

Moving the Delete button to the opposite side is a great suggestion. I'm using IP 2007, and it's being published to a SharePoint site and doesn't need to be browser compatible. Every employee has MS Office 2007 with InfoPath. Can a script or coding be used alondside rules? Is there any standard script/code that can beused to allow users to cancel the delete?

After all this, I'm starting to learn C#. Even if I don't use it that much, my manager will think I'm taking the initiative on this (LOL).

Re: How to use SQL Server stored procedures in repeating tebles/sections for INSERT's, DELETE's, etc.?

Hilary Stoupa — Mon, 26 Oct 2009 23:59:31 GMT

I'm afraid that "Show a dialog box message" won't give the user a chance to cancel. That means you'd need to use code for your pop up.... However, what about moving your buttons to opposite sides? So that your delete button is on one side and your update on another? That would cut down on the user error factor...

If you are going to use code, are you using IP 2003? or 2007? It makes a difference.... Also, does this form need to be browser compatible? We can't use message boxes in browser forms, unfortunately.

Re: How to use SQL Server stored procedures in repeating tebles/sections for INSERT's, DELETE's, etc.?

Francis — Thu, 22 Oct 2009 16:14:34 GMT

I apologize for taking so long to reply for your help. We had some big issues on our SQL Servers, and I wasn't able to work on this. Also, I got sidetracked on other assignments.

After a few "Gotchas", I was able to get the buttons for inserting, updating and deleting rows to work -- so a huge THANK YOU!! I even got the update and delete buttons to only show on existing rows of data, and the insert button to only show on the empty row for entering new data.

The "Gotchas" had me stumped for a while. This form is really a data entry form for 9 tables, and as a reminder, I'm using SQL Server stored procedures for everything data-related, including the drop-down list boxes. After getting it to work on the first table using the main data connection, I started using secondary data connections for the other tables -- thinking that since I was using stored procedures I could get by some of the receiving/submitting aspects of data connections. When I found that I couldn't insert a new row into a repeating section, I added another repeating section below this just for inserts. This didn't seem to work either.

The solution that appears to work so far is to do a "UNION ALL" query in the stored procedure used in the main data connection using all the tables for each part of the query. Some fields are common in all tables, so that wasn't a problem. But I had to add fields in the query with default values, e.g., 0 AS ExchangeRate, or '' AS ExchangeRateDate. I had some problems when these extra fields were "NULL", so hence 0 or '' (blank). I also added a new field named "TableType" that I then used as a way to filter the rows. I was then able to create repeating sections using the main data connection for 2 tables as a test and then to filter the data in each section using "TableType".

However another issue popped up as I was testing the buttons. I would sometimes accidently hit the delete button when I wanted to hit the update button. I only have rules on the buttons to "Submit using a data connection" but am not using any script or code. Is there a way to have a rule to have a message pop up after clicking the delete button giving the user a chance to reconsider the delete and cancel the delete (as a reminder, I'm a database developer and not a programmer)? I see there are actions for "Show a dialog box message" or expression. Can either of these be used? If script or code is necessary, how difficult is that?

PS I had previously used the name "aaqqqa". You have helped me a lot on a couple of other questions I've had in the recent past.

Re: How to use SQL Server stored procedures in repeating tebles/sections for INSERT's, DELETE's, etc.?

Hilary Stoupa — Sat, 19 Sep 2009 00:37:51 GMT

Where is your Delete button? If it is not in the repeating table with your rows, it will always get the first value. A simple solution would be to place the button in the table (you can make it look pretty by changing the text to an 'X', for example, coloring it red and removing borders and shading) so that each row has its own Delete button. Then, when you go to set your parameter with the ID from that row, you'll get the correct value.

How to use SQL Server stored procedures in repeating tebles/sections for INSERT's, DELETE's, etc.?

Francis — Thu, 17 Sep 2009 15:11:29 GMT

I have a form in which I connect to a SQL Server 2005 database using a SQL Server endpoint as the web service (it's not a .NET web service). I'm using

stored procedures with input parameters for SELECT's, UPDATE's, INSERT's and DELETE's. The stored procedures for the SELECT's work fine -- I use them in

drop-down list boxes and repeating tables or repeating sections.

However, I'm not able to click on or highlight a row to get the values of fields in that row to use for the input parameters for the UPDATE, INSERT and

DELETE stored procedures. For example the stored procedure for DELETE's uses the primary key of that row, but it will only use the primary key of the first

row in the repeating table/section and delete that row from the database, even if I click on another row (I have a "Delete" button to submit the stored

procedure).

I read articles on the current() function and tried this, but it either didn't like the way I put it in, or still used the primary key from the first row.

Is there something I'm missing, is there another function I could try, or is there something about bringing a resultset into InfoPath using an endpoint

and/or a stored procedure that is causing the problem?

I'm not a programmer, so is there a way to do this without coding (hence, the endpoint instead of a .NET webservice)? If coding needs to be used, can

someone supply an example.