Trying to populate a datagrid from webservice

Oct 12, 2009 at 10:35 AM

Hi,

I have been banging my head on this one for 2 days now and was hoping someone can give me a hand please?

I am basicially trying to return a list of results from a database stored procudure using a webservice function and populate a datagrid.

In the Webservice I have the following code....

In the DataProvider.vb:

Public MustOverride Function IWeb_TaxiRoute_Get_List_Of_Fares() As IDataReader

 

In the sqlDataProvider.vb :

Public Overrides Function IWeb_TaxiRoute_Get_List_Of_Fares() As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "IWeb_GetListOfTaxiFares"), IDataReader)
        End Function

 

I have tested the stored procedure and it does return results but I cannot see how to get this data from the webservice and then populate a datagrid.

Can anyone shed any light on this please?

Thanks

Trev

Coordinator
Oct 12, 2009 at 12:17 PM

I'm sorry we have no sample code that uses stored procedures.

Oct 14, 2009 at 3:51 PM
Edited Oct 14, 2009 at 7:04 PM

Hi,

Im really sorry, Im back again and im still stumped. Could I ask a favour? Could you have a quick look at my code and see if you can see what I am doing wrong please?

I have managed to get IWeb to work with an update and a insert stored procedure but i cannot seem to work out how to return a list of records from a database. I have done my very best to follow your tutorials but nothing seems to work for me.

This is what I have done so far....

I have created a IWebTaxiRoute.vb file in the IWebMethods directory and added the following code:

<WebMethod(Description:=" Returns a list of Fares within X Miles *DotNetNuke* |IWEB Core| #IWEB Misc# !Portal! "), SoapHeader("IWebCredentials")> _
        Public Function TaxiRoute_Get_List_Of_Fares() As DataSet
            Dim ListOfFares As DataSet = Nothing
            Try
                Dim objIWebAuthendication As New IWebAuthendication(IWebCredentials)
                If Not objIWebAuthendication.ValidAndAuthorized() Then
                    'Return "Not Authorized"
                End If

                ListOfFares = IWeb.DataProvider.Instance.IWeb_TaxiRoute_Get_List_Of_Fares()


            Catch ex As Exception
                'ListOfFares = ex.Message
            End Try

            Return ListOfFares
        End Function

 

In my DataProvider.vb I have added the following code:

Public MustOverride Function IWeb_TaxiRoute_Get_List_Of_Fares() As IDataReader

In my IWebController.vb I have added the following code:

<DataObjectMethod(DataObjectMethodType.Select)> _
        Public Shared Function TaxiRoute_Get_List_Of_Fares() As List(Of IWebInfo)
            Return CBO.FillCollection(Of IWebInfo)(DataProvider.Instance().IWeb_TaxiRoute_Get_List_Of_Fares())
        End Function

At the top of IWebInfo.vb I have added the following code:

'TaxiRoute property declarations
        Private _LocationId As Integer
        Private _LocationName As String
        Private _Address As String
        Private _City As String
        Private _State As String
        Private _Zip As String
        Private _Latitude As Long
        Private _Longitude As Long
        Private _XAxis As Long
        Private _YAxis As Long
        Private _ZAxis As Long

And then further towards the bottom of IWebInfo.vb I have added the following code:

'TaxiRoute Stuff

        ' public properties
        Public Property LocationId() As Integer
            Get
                Return _LocationId
            End Get
            Set(ByVal Value As Integer)
                _LocationId = Value
            End Set
        End Property

        Public Property LocationName() As String
            Get
                Return _LocationName
            End Get
            Set(ByVal Value As String)
                _LocationName = Value
            End Set
        End Property

        Public Property Address() As String
            Get
                Return _Address
            End Get
            Set(ByVal Value As String)
                _Address = Value
            End Set
        End Property

        Public Property City() As String
            Get
                Return _City
            End Get
            Set(ByVal Value As String)
                _City = Value
            End Set
        End Property

        Public Property State() As String
            Get
                Return _State
            End Get
            Set(ByVal Value As String)
                _State = Value
            End Set
        End Property

        Public Property Zip() As String
            Get
                Return _Zip
            End Get
            Set(ByVal Value As String)
                _Zip = Value
            End Set
        End Property

        Public Property Latitude() As Long
            Get
                Return _Latitude
            End Get
            Set(ByVal Value As Long)
                _Latitude = Value
            End Set
        End Property
        Public Property Longitude() As Long
            Get
                Return _Longitude
            End Get
            Set(ByVal Value As Long)
                _Longitude = Value
            End Set
        End Property

        Public Property XAxis() As Long
            Get
                Return _XAxis
            End Get
            Set(ByVal Value As Long)
                _XAxis = Value
            End Set
        End Property

        Public Property YAxis() As Long
            Get
                Return _YAxis
            End Get
            Set(ByVal Value As Long)
                _YAxis = Value
            End Set
        End Property

        Public Property ZAxis() As Long
            Get
                Return _ZAxis
            End Get
            Set(ByVal Value As Long)
                _ZAxis = Value
            End Set
        End Property

 

And in my SqlDataProvider.vb I have added the following code:

Public Overrides Function IWeb_TaxiRoute_Get_List_Of_Fares() As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "IWeb_GetListOfTaxiFares"), IDataReader)
        End Function

 

I have then added the webreference to my Mobile Application and tried to retrieve the data using the following code:

Private Function TaxiRoute_Populate_Taxi_Fare_List()
        Dim ws As New TaxiRoute.TaxiRoute_Webservice.WebService()
        ws.Url = "http://www.taxiroute.co.uk/DesktopModules/IWeb/webservice.asmx"
        ws.IWebAuthendicationHeaderValue = IWebAuthendication.AttachCredentials()
        Dim dataReader As IDataReader = ws.TaxiRoute_Get_List_Of_Fares()
        While dataReader.Read()
            Console.WriteLine(String.Format("{0}, {1}", dataReader(0), dataReader(1)))
        End While
        DataGrid1.DataSource = dataReader
        'Dim reader As SqlDataReader = Command.ExecuteReader()
    End Function

 

I get no errors on compilation but whenever I run the code in the mobile app it just returns a null reference when it gets to the "While dataReader.Read()" line.

Please can you help? I am really stuck.

Any help would be very much appreciated

Trev

Coordinator
Oct 14, 2009 at 3:58 PM

I think the problem is that a data reader can not be passed through a web service. I could be wring. I simply don't know.

What I woudl do is, inside your IWeb method, loop through all the records in your data reader and create a List<TaxiRoute> and send that (you must mark the class TaxiRoute as [Serializable].

You can find code to convert the datareader to a collection here:

http://www.adefwebserver.com/DotNetNukeHELP/Misc/UserLogViewer/UserLogViewer.htm

 

Oct 15, 2009 at 11:32 AM

Hi,

Thanks!!!! - you are correct from what I have read.

I have managed to get it working using a dataset.

Trev

Nov 3, 2011 at 6:24 PM

Hello,

I've been working for a few days trying to do the same thing.  I have VB.NET and I'm trying to pass a serialized generic list back (obtained from a stored procedure datareader to datatable).

Would you share code to accomplish this?  I'm specifically looking at the web method code.

Thank you.