Hi there,
I am a newbie to asp.net and i am using asp 2005. Now i am trying to create a webservice site. My plan to to create a webservice function that will reaturn a oledatareader and beleow is my code:

<%@ WebService Language="VB" Debug="true" Class="WebService" %>

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.OleDb

<WebService(Namespace := "http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class WebService
Inherits System.Web.Services.WebService
Dim constr As String = "Provider=Microsoft.jet.oledb.4.0;Data Source=C:\asp\webservice\database\data.mdb"
<WebMethod()> Public Function GetDr(ByVal sql As String) As OleDbDataReader
Dim wcn As OleDbConnection = New OleDbConnection(constr)
Dim wcmd As New OleDbCommand(sql, wcn)
wcn.Open()
Dim wdr As OleDbDataReader = wcmd.ExecuteReader()
wcn.Close()
Return wdr
End Function
End Class


When I test the program using ctrl F5 i got this error.

Server Error in '/webservice' Application.
--------------------------------------------------------------------------------

To be XML serializable, types which inherit from IEnumerable must have an implementation of Add(System.Object) at all levels of their inheritance hierarchy. System.Data.OleDb.OleDbDataReader does not implement Add(System.Object).
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: To be XML serializable, types which inherit from IEnumerable must have an implementation of Add(System.Object) at all levels of their inheritance hierarchy. System.Data.OleDb.OleDbDataReader does not implement Add(System.Object).

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[InvalidOperationException: To be XML serializable, types which inherit from IEnumerable must have an implementation of Add(System.Object) at all levels of their inheritance hierarchy. System.Data.OleDb.OleDbDataReader does not implement Add(System.Object).]
System.Xml.Serialization.TypeScope.GetEnumeratorElementType(Type type, TypeFlags& flags) +731
System.Xml.Serialization.TypeScope.ImportTypeDesc(Type type, MemberInfo memberInfo, Boolean directReference) +1996
System.Xml.Serialization.TypeScope.GetTypeDesc(Type type, MemberInfo source, Boolean directReference, Boolean throwOnError) +135
System.Xml.Serialization.XmlReflectionImporter.ImportMemberMapping(XmlReflectionMember xmlReflectionMember, String ns, XmlReflectionMember[] xmlReflectionMembers, Boolean rpc, Boolean openModel) +78
System.Xml.Serialization.XmlReflectionImporter.ImportMembersMapping(XmlReflectionMember[] xmlReflectionMembers, String ns, Boolean hasWrapperElement, Boolean rpc, Boolean openModel) +280

[InvalidOperationException: There was an error reflecting 'GetDrResult'.]
System.Xml.Serialization.XmlReflectionImporter.ImportMembersMapping(XmlReflectionMember[] xmlReflectionMembers, String ns, Boolean hasWrapperElement, Boolean rpc, Boolean openModel) +881
System.Xml.Serialization.XmlReflectionImporter.ImportMembersMapping(String elementName, String ns, XmlReflectionMember[] members, Boolean hasWrapperElement, Boolean rpc, Boolean openModel, XmlMappingAccess access) +112
System.Web.Services.Protocols.SoapReflector.ImportMembersMapping(XmlReflectionImporter xmlImporter, SoapReflectionImporter soapImporter, Boolean serviceDefaultIsEncoded, Boolean rpc, SoapBindingUse use, SoapParameterStyle paramStyle, String elementName, String elementNamespace, Boolean nsIsDefault, XmlReflectionMember[] members, Boolean validate, Boolean openModel, String key, Boolean writeAccess) +203
System.Web.Services.Protocols.SoapReflector.ReflectMethod(LogicalMethodInfo methodInfo, Boolean client, XmlReflectionImporter xmlImporter, SoapReflectionImporter soapImporter, String defaultNs) +3391

[InvalidOperationException: Method WebService.GetDr can not be reflected.]
System.Web.Services.Protocols.SoapReflector.ReflectMethod(LogicalMethodInfo methodInfo, Boolean client, XmlReflectionImporter xmlImporter, SoapReflectionImporter soapImporter, String defaultNs) +6633
System.Web.Services.Description.SoapProtocolReflector.ReflectMethod() +134
System.Web.Services.Description.ProtocolReflector.ReflectBinding(ReflectedBinding reflectedBinding) +2512
System.Web.Services.Description.ProtocolReflector.Reflect() +626
System.Web.Services.Description.ServiceDescriptionReflector.ReflectInternal(ProtocolReflector[] reflectors) +560
System.Web.Services.Description.ServiceDescriptionReflector.Reflect(Type type, String url) +117
System.Web.Services.Protocols.DocumentationServerType..ctor(Type type, String uri) +159
System.Web.Services.Protocols.DocumentationServerProtocol.Initialize() +335
System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing) +99

[InvalidOperationException: Unable to handle request.]
System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing) +258
System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(Type type, HttpContext context, HttpRequest request, HttpResponse response) +93

[InvalidOperationException: Failed to handle request.]
System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(Type type, HttpContext context, HttpRequest request, HttpResponse response) +240
System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpContext context, String verb, String url, String filePath) +106
System.Web.HttpApplication.MapHttpHandler(HttpContext context, String requestType, VirtualPath path, String pathTranslated, Boolean useAppConfig) +328
System.Web.MapHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +139
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +146




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42


kindly help me pls. NOTE: My plan is to add this webservice into my website.
thx. in advance

Newvbguy

you cannot pass a datareader because it is a connection to a database and a webservice can only pass data which can be serialized such as xml.
The best thing to pass is a dataset. because a dataset can be serialized being that it generates xml.

thanks for your advice plazmo. you're a big help.

newvbguy

hi,

Looking at your code,the best way to use webservice with databases is to use dataset.store the values in a dataset and return the dataset from the function.

hope this helped.

Thanks
Regards

Exelio

Can a datareader passed as a parameter from a sub to a function?
I am trying to pass some values via a datareader that reads from an sql database tabel and also a listbox where I want one column of the data to be displayed. Here is my partial code:

In the calling function for button click event:

SitesSelected(myReader, topicSelectionListBox)

In the SitesSelected function:
function SitesSelected(ByRef myreader As Object, ByRef paramlistboxvalue As ListBox)
Dim listboxStr As String()
Dim i As Integer
While myreader.Read()
Response.Write("The received values are: " & myreader & "," & paramlistboxvalue.SelectedItem.Text)
paramlistboxvalue.Items.Add(myreader.GetValue(0))
End While
End function


Thanks

Yes it can. Like:


What is the problem you face?

Thanks for your post. I have been out of commission for sometime and could not reply earlier.

When I tried to pass the datareader, my listbox display was empty, but now with passing a dataset, something comes up, though it is not what is expected. Here is a partial screenshot. In my query at page_load, I am selecting only one column from the datatable.
Select a Site from list box

System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow Here is the partial code in the calling function:
SitesSelected(DS, siteSelectionListBox, 2)
Inside the SitesSelected function:
SitesSelected(ByVal ds As DataSet, ByVal paramlistboxvalue As ListBox, ByVal index As Integer)
For Each row In ds.Tables("dtwaterquality_all").Rows
paramlistboxvalue.Items.Add(ds.Tables(0).Rows.Item(index).ToString)
Next

I would greatly appreciate any help or pointers

do you see what you are doing wrong here?
your looping thru the rows and not even using the object your looping with, 'row'. and if its only 1 column your itemarray index should be 0 always

do
paramlistboxvalue.Items.Add(row.ItemArray[0].ToString())


Another way of doing this in less lines of code is 'databinding'. read up on it. most controls in asp.net can be databound and it makes this kind of stuff really easy.

Vow, that worked!!. Thank you so much for your hint. I had tried row.item(0).tostring, but that had not worked.

By using databinding, do you suggest that I can just bind the control to the datasource, like so?
paramlistboxvalue.DataSource = ds
paramlistboxvalue.DataBind()