G
Guest
I don't know if it's me or .net is just getting more complicated. I am trying
to call a sp and pass a parameter and return the data using VB2005. First I
couldn't find any docs with samples. Second I tryed this
<%@ WebService Language="VB" Class="GetList" %>
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
<WebService(Namespace := "http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class GetList
Inherits System.Web.Services.WebService
<WebMethod()> _
Public Function GetProducts(ByVal sName As String) As SqlDataReader
Try
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim oReader As SqlDataReader
conn = New
SqlConnection("Server=machine\Enterprise;Database=Staging;User
ID=me;Password=pass")
conn.Open()
cmd = New SqlCommand("GetProdList", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim param As SqlParameter =
cmd.Parameters.AddWithValue("@productName", sName)
conn.Open()
oReader = cmd.ExecuteReader()
Return oReader
Catch ex As Exception
End Try
End Function
End Class
and get the error
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.SqlClient.SqlDataReader 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.SqlClient.SqlDataReader 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.SqlClient.SqlDataReader 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
'GetProductsResult'.]
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 GetList.GetProducts 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
Microsoft.Web.Services.ScriptHandlerFactory.GetHandler(HttpContext
context, String requestType, String url, String pathTranslated) +246
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
to call a sp and pass a parameter and return the data using VB2005. First I
couldn't find any docs with samples. Second I tryed this
<%@ WebService Language="VB" Class="GetList" %>
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
<WebService(Namespace := "http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class GetList
Inherits System.Web.Services.WebService
<WebMethod()> _
Public Function GetProducts(ByVal sName As String) As SqlDataReader
Try
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim oReader As SqlDataReader
conn = New
SqlConnection("Server=machine\Enterprise;Database=Staging;User
ID=me;Password=pass")
conn.Open()
cmd = New SqlCommand("GetProdList", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim param As SqlParameter =
cmd.Parameters.AddWithValue("@productName", sName)
conn.Open()
oReader = cmd.ExecuteReader()
Return oReader
Catch ex As Exception
End Try
End Function
End Class
and get the error
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.SqlClient.SqlDataReader 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.SqlClient.SqlDataReader 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.SqlClient.SqlDataReader 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
'GetProductsResult'.]
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 GetList.GetProducts 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
Microsoft.Web.Services.ScriptHandlerFactory.GetHandler(HttpContext
context, String requestType, String url, String pathTranslated) +246
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