SQL Database Integration

Re: Retreiving Values from SQL DB

Rachel20J — Mon, 02 Nov 2009 15:33:43 GMT

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!

Re: Retreiving Values from SQL DB

Rachel20J — Sun, 01 Nov 2009 16:07:40 GMT

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!

Re: Retreiving Values from SQL DB

Hilary Stoupa — Sun, 01 Nov 2009 15:13:20 GMT

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.

Re: Retreiving Values from SQL DB

Rachel20J — Sun, 01 Nov 2009 14:50:38 GMT

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?

Re: Retreiving Values from SQL DB

Hilary Stoupa — Sun, 01 Nov 2009 14:39:47 GMT

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?

Re: Retreiving Values from SQL DB

Rachel20J — Sun, 01 Nov 2009 14:21:53 GMT

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.

Re: Retreiving Values from SQL DB

Hilary Stoupa — Sun, 01 Nov 2009 13:24:02 GMT

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).

Re: Retreiving Values from SQL DB

Rachel20J — Sat, 31 Oct 2009 14:32:00 GMT

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...

Re: Retreiving Values from SQL DB

Hilary Stoupa — Fri, 30 Oct 2009 23:09:41 GMT

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?

Retreiving Values from SQL DB

Rachel20J — Wed, 28 Oct 2009 20:09:42 GMT

I am hoping its simpler than what I am doing.... I have set up a data connection to retrieve from Web Service and to give me the results that I need for a field, now it works perfectly,only if you set the actual value (sample value) that it asks you to use in the Data connection... problem is, if i choose an item from drop down, and the value does not match the value that was set up in the webservice data connection, it will not query the item that I need. It wants the exact ID number that was set in the Sample value. I thought it was just as a reference and i can just choose from a drop down and it will automatically put that information in there. I have pasted my webservice code on here...is it something that needs to be corrected on here? Do you have to use the exact ID number that the sample value is using in the Data connection? I was hoping, whatever item I choose from Drop down, that it would grab that result and set that queried value into another field for me. I even have query action used on the drop down. On the field control that needs the value in it, i have that query action, and using a button, it all boils down to using that exact ID number in the sample value. Please help and thank you for your time.

<WebMethod()> _

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

Dim connString As String = ConfigurationManager.ConnectionStrings("USBIConnectionString").ToString

Dim sqlConn As New SqlConnection(connString)

Dim sqlCmd As New SqlCommand()

Dim sqlReader As SqlDataReader

Dim sqlAdapter As New SqlDataAdapter

Dim sqlDataset As New DataSet

Try

sqlAdapter.SelectCommand = New SqlCommand(String.Format("EXEC [dbo].[GetRemainingBudget] @JobNumberID = {0}, @CSIID = {1};", JobNumberID, CSIID), sqlConn)

sqlAdapter.Fill(sqlDataset)

'Return sqlDataset

Return sqlDataset.Tables(0).Rows(0).Item("RemainingBudget")

' End editing here

Catch ex As Exception

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

Finally

sqlConn.Close()

End Try

End Function