InfoPath Dev

Public Function GetRemainingBudgetValue(ByVal JobNumberID As Integer, ByVal CSIID As Integer) As Decimal

Dim sqlConn As New SqlConnection(connString)

Dim sqlReader As SqlDataReader

Dim sqlDataset As New DataSet

Try

'Return sqlDataset

' End editing here

Throw New Exception("Error updating database", ex)

Finally

End Try

End Function

Hi --

Looks like your web service takes two parameters? You should be able to set the values of those parameters based off rules or code in your form and then run your query. Is that what you are doing?

Thank you Hilary for responding.  Yes thats correct, 

I have a drop down list that receives from SQL using data connection (that sets the ID number to varJobID) and another field (varCSIID) contains a permanant ID that is exactly the same in the sql table for another category for filters.  It works fine. 

 I have job 1 and a category of 22.   I would choose from drop down job 1 and it automatically drops all the information needed.  I choose the job 2, it is blank.  The filters are based like this...   sqlJobID = varJobID and sqlCSIID = varCSIID.  If I modify my data connection webservice to retrieve , and I change the sample value to 2, then run it again, it works for job 2 and not job 1.   I've tried not automatically retrieve, actions to set queries - tried different scenarios and nothing works unless i set the actual value of ID. I am just stuck here...

Do you mind attaching your form template to a reply to this post (under the Options tab there is a place you can attach a file).

Not a problem.  VarJobNumID and VarCategory it what it uses.  Remaining budget fields is what I need and it does automatically retrieve. The dropdown at the very top sets the value to varJobNumID and varCategory is always set as that never changes.  Thank you again, I guess I need a fresh mind, as I have been doing this form (28 forms) for so long.. I can't see outside the box.   Thank you so much for taking the time.

I'm not seeing where you set the query fields in your GetRemainingBudgetValue to the values in VarJobNumID and VarCategory. I'd expected to see rules on those fields that would set the query fields in the GetRemaningBudgetValue data source. Are you doing this in code? If so, can you reply with the code?

Its on the remainingbudget field.  There is no code.  It works all the time only when its looks for the same ID that is set in the web service data source.... are you saying I need to set it on the varJobNumID?

Here is what I'm trying to understand. When are you querying your secondary data connection to your web service? When you set the sample value and you set it up, IP queries to create the data source. But the next step in the web service data connection for a receive connection, as far as I know, doesn't allow you to specify form fields for your web service query field values.... And I see you have your web service set to query on load. So, it is going to use the sample values you put in on set up.

It seems to me that at some point you need to set some rules to modify the query fields in your web service data connection to use values from your form, right? And then query the data connection again to get the correct data. Because currently, you are only going to have the data in that data connection where the job number id and CSIID are equal to the sample value. Unless you change the query fields in the data connection to use new values and query again.

I suggest you temporarily drag that GetRemainingBudgetValue data source onto your form. Try setting the query fields to different values, and re-running the query. You shouldn't need to filter the Remaining Budget Field's value, because your web service takes those two parameters -- your only going to have the data for the remaining budget that matches on job number id and CSIID. I've added a debug view to your form that you can use. Just try putting some different values in for the two query fields and re-running the query. Hopefully that will show you what I mean.

To save the file, right click the file in the header and select save as. Then, when you have the copy saved locally, right click the file and select Design to open it in design mode.

I will work on that tomorrow at my office..its such a beautiful day in houston...thank you very much and it makes sense on what you explained to me... I will let you know soon if I figure that part out!  I did use the debugging.. now i just need to figure out how to override or get rid of that message about the soap error - connection to sql database not working properly...to be continued!

Thank you Hillary, I just needed an outside 3rd party intervention :) I was totally doing it all wrong, it worked the other way, but this way, I just said to myself why did I do that!!  Thank you.  Have a great day!