SQL Webservice and InfoPath Web Form

[Natalie Lewis]

I used this article:
http://blogs.msdn.com/infopath/arch...l-server-2005-web-services-with-infopath.aspx
and the appendix in Professional InfoPath forms to create a web service for
a web based Infopath form.

My stored procedure works when I execute it on the server, but when I call
it from the web service in InfoPath, the rowset is empty. I've been
searching all day for a solution - anyone have any insight into what I'm
doing wrong?

Here's the code:
CREATE ENDPOINT get_Orders
STATE=STARTED
AS HTTP(
SITE = 'server',
PATH = '/sql/demo',
AUTHENTICATION = ( INTEGRATED ),
PORTS = ( CLEAR )
)
FOR SOAP (
WEBMETHOD
'http://server:12345/'.'Order'
(NAME='SamplesOrderForm.dbo.GetOrderandDetails2'),
BATCHES = ENABLED,
WSDL = DEFAULT
)

GetOrderandDetails2:
@SampleID Int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

-- Insert statements for procedure here
SELECT
Orders.Department,
Orders.Requestor,
Orders.DeliveryDate,
Orders.Carrier,
Orders.PickUpLoc,
Orders.Contact,
Orders.ContactPhone,
Orders.DeliverHrs,
Orders.DeliverNotes,
Orders.Truck,
Orders.Comments,
Orders.PickupDate,
Orders.CarrierContact,
Orders.CarrierPhone,
OrderDetails.DetailID,
OrderDetails.Sku,
OrderDetails.Item,
OrderDetails.Qty,
OrderDetails.Pallet,
OrderDetails.LotCode
FROM Orders, OrderDetails
WHERE Orders.SampleID = @SampleID AND OrderDetails.SampleID =@SampleID
END


THANK YOU IN ADVANCE!!!

 

[Clay Fox]

These types of things often come down to syntax and can be a bit tricky to
debug. It is best to start simple and get one working first.

If you would like to see some examples of working web services for reference
or as an alternative let me know.

 

[Gavin McKay]

Hi Natalie,

Apologise if I'm completely off base here, I've never used this method to
call stored procs in InfoPath (I always write web services, no matter what -
it's the nerd in me I guess - but are you able to tell what the value of
the @SampleID is that is being passed in by InfoPath?

You could also try using Sql Profiler to see whether Sql Server is getting
the information.

HTH

Gavin.

 

[Natalie Lewis]

Hi Gavin,

Thanks for the response. I actually started out by writing a .net web
service which was easier than I thought it would be for the updating records
piece of code. I have been having issues returning get records from linked
tables in sql and it was suggested that I use sql 2005 to create the web
service. I know that the statement is working in some fashion. The sqlbatch
that is created allows does allow me to enter a SampleID and returns batch
information; ie. number of rows, etc. I will try the sqlProfiler to see if
that gives me any hints. If you have any suggestions on returning records
from sql via a web service - I'd be grateful!

thanks,
Natalie

 

[Gavin McKay]

Hi Natalie,

IMHO web services are a better option because you can debug them a lot
easier, but it's up to a person's skillset which one they use. If you're a
SQL-geek, go with sql web services. Code-geek, go with .NET web services.
*shrugs*

As a (quite average) code-geek, I use Windows Communication Foundation (WCF)
for all my web services, but they are not for the faint-of-heart. Anyone
that has configured WCF and waded through configuration files, endpoints etc
knows exactly what I mean!!! WCF is the most powerful and most flexible
option for web services IMO. However, you can still do exactly what you want
with good old .NET asmx web services - depends again on what you're
comfortable with.

Anyway, I'll get off my soap-box now

If you write a web service that returns a simple string of Xml, InfoPath
should be happy with that. Once you have your dataset call (C#):

string returnData = myDataset.GetXml()
return returnData;

and that will return your DataSet as an Xml string which InfoPath can
consume. It's not a very nice Xml structure that you will get, but it will
work. If you want more control over the Xml that's returned, either use WCF
or craft your own Xml manually or via Serialization.

(Sorry for the long post!)

Gavin.

 

[Natalie Lewis]

Hi Gavin,

I get it. I actually went back to me .Net web service this afternoon. I
realized I was calling the wrong class to execute the sql command. I'm still
stuck on dealing with the detail table; I can get my main table to return
fine - but the child table has multiple entries for each one entry in the
main table. I keep searching for samples online, but I'm not finding what
I'm looking for!

Thanks again for the help!
natalie

 

[Clay Fox]

If you are interested in inexpensive, installable, web services which you can
dynamic query or submit to any table let me know. It could save you a lot of
time and stress and you could be using them in less than 15 minutes.

http://www.infopathdev.com/members/clayfox.aspx