InfoPath Dev

I'm trying to use a webservice that hooks to SQL data and will need to return a multidimensional array so that I can bind the SQL data to a drop down list box. I'm able to return a single column of data(client name) and successfully bind to the drop down listbox, but I need to return 2 values(client name and number) and am not sure how to do it. The closest I have been able to get my webservice to work was when I tried using jagged arrays, but the speed was super slow(1,700 records). The speed is fast however when returning a single column array. Let me know if you have any thoughts or if you can guide me to an example of multidimensional arrays. Here is my code for the single column array:

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}

Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports System.ComponentModel

Imports System.Data.SqlClient

Imports System.Data

Imports System.Xml.Serialization

<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _

<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> <ToolboxItem(False)> _

Public Class Service1

    Inherits System.Web.Services.WebService

    <WebMethod()> Public Function GetMyClients(ByVal AlphaCode As String) As String()

        Dim connStr As String = "Server=cldb161;Database=NMTest;Trusted_Connection=False;"

        Dim sqlStr As String = "select clname, clnum from l_clntlist where clname like '" & AlphaCode & "%' order by clname"

        Dim returnString As String() = Nothing

        Dim sqlConn As SqlConnection = New SqlConnection(connStr)

        Dim SqlCmd As SqlCommand = New SqlCommand(sqlStr, sqlConn)

        Dim sqlReader As SqlDataReader

        Try

            sqlConn.Open()

            sqlReader = SqlCmd.ExecuteReader()

            ReDim Preserve returnString(0)

            Do While sqlReader.Read

                ReDim Preserve returnString(UBound(returnString) + 1)

                returnString(UBound(returnString)) = sqlReader("clname")

            Loop

            sqlReader.Close()

            sqlConn.Close()

        Catch ex As Exception

        End Try

        Return returnString

    End Function

Hi,

Have you considered looking at our Web Services Solution Pack (WSSP)?  WSSP is a web service and does what you are looking for.  WSSP also includes "Query Builder" which is a tool which allows you to easily create the queries to use in InfoPath (it includes easy copy & paste for the data source sample value, a query you can use in your rules & a query you can use in code).

This demo video explains the tool nicely: http://www.screencast.com/users/qdabra/folders/Default/media/ae6728ea-3273-4202-bc9b-173ff701d8a9

Our customers who have purchased WSSP have been very happy because it saves a lot of time and will resolve your performance problems when doing a query on a large data source

Ted

This article helped us find our resolution.

http://blogs.msdn.com/infopath/archive/2006/12/20/using-sql-server-2005-web-services-with-infopath.aspx

SQL End Points are a tightly coupled solution with the same problems I have posted about many times before. Good luck. We'll stop preaching the gospel to you since you seem to be following another religion.

Hi Patrick - Can you be more specific about SQL End Points being a tightly coupled solution? Also, I did a quick search on this site and I did not see any posts from you about endpoints. I did find one post where Hilary talked about endpoints and recommended using the same solution we ended up finding/using.

In all fairness, in the same post above, there is a mention that SQL End Points will be deprecated in SQL 2008. That is something we can address when we upgrade our SQL servers.

SQL End Points are represented as Web Methods with a schema that is parameterized. When you create an InfoPath form from this schema (or a receive data connection) InfoPath infers a schema. That schema is stored in the XSN as an XSD file. If the Web Methods change (because a parameter changes), guess what? You have to convert your main data source, or modify your data connections. In the former case, that means XML stored with the old schema need to be migrated (and that's a big pain in the butt). In both cases it means that you have to have planned down time as you update the XSN. If someone leaves InfoPath running, they may experience problems since the interface has changed. Also, you may need to create separate end points for every single XSN you have (if you're using to submit) since the data will differ. Loosely coupled solutions don't have these problems. In the old days, everyone thought loosely coupled solutions were anathema because it allowed invalid data to be added to your DB, but now with InfoPath, guess what? It validates the schema on the client so long as you are using that client (as opposed to some random web page a dev through together), you don't have this issue. Loosely coupled solutions pass XML strings that contain the query parameters or the data payload to be saved into the DB. Building a loosely coupled solution is more complex than building a tightly coupled solution (involves parsing, security checks, etc.), but we've already done that for you with our Web Service Suite (DBXL). One more thing, SQL end points have bugs and issues and that's one more reason to stay away from them.