returning a DataSet in a Web Service method

Hi experts,

I'm using Visual Studio 2010 and VB in a Web Application and SQL Server 2008.
I'm using the Employees table of the Northwind Database.

I have a stored procedure called GetEmployeeDetailsByID that uses 1 parameter called @EmployeeID.  

The code for the stored procedure is this:

CREATE PROCEDURE [dbo].[GetEmployeeDetailsByID] 
	@EmployeeID int = 0
AS
BEGIN
	SET NOCOUNT ON;
	SELECT FirstName, LastName, BirthDate, City, Country 
	FROM Employees WHERE EmployeeID=@EmployeeID
END

Open in new window


I have a Web Service called EmployeeByIDWebServiceDS.asmx

My code looks like this:

Imports System
Imports System.Collections.Generic
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data.SqlClient

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class EmployeeByIDWebServiceDS
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function HelloWorld() As String
        Return "Hello World"
    End Function

    <WebMethod(Description:="Simple Example")> _
    Public Function GetEmployeeDetails(EmployeeID As Integer) As DataSet

        'Create objects
        Dim MyDataSet As DataSet
        Dim MyConnection As SqlConnection
        Dim MyDataAdapter As SqlDataAdapter
        Dim comm As SqlCommand

        'Create a connection to the SQL Server.
        '********************* Windows Authentication ************ FCOE-DPN1TR1 ***********************************
        MyConnection = New SqlConnection("Data Source=FCOE-DPN1TR1;Initial Catalog=Northwind;Integrated Security=True")
        '**********************************************************************************************************

        MyConnection.Open()

        comm = New SqlCommand()

        'Set the command type as StoredProcedure.
        comm.CommandType = System.Data.CommandType.StoredProcedure
        comm.CommandText = "GetEmployeeDetailsByID"
        comm.Parameters.AddWithValue("@EmployeeID", EmployeeID)

        'Create a DataAdapter, and then provide the name of the stored procedure.
        'adapter called myDataAdapter - name of stored procedure - connection
        MyDataAdapter = New SqlDataAdapter("GetEmployeeDetailsByID", MyConnection)

        'Create a new DataSet to hold the records.
        MyDataSet = New DataSet()

        'Fill the DataSet with the rows returned.
        MyDataAdapter.Fill(MyDataSet, "Employees")

        Try

        Catch ex As Exception
            Throw ex
        Finally
            MyConnection.Dispose() 'Dispose of the DataAdapter.
            MyConnection.Close() 'Close the connection.
        End Try

        Return MyDataSet

    End Function

End Class

Open in new window


When I run this service in the web browser I get this:

when i run my web service
If I click on the GetEmployeeDetails  method link I get this:

GetEmployeeDetails method
If I type in 1 for the EmployeeID and then click Invoke I get this:

result of my web service
That only shows the column titles returned by my stored procedure.
It's missing the values.

In my example when I type in 1 for Employee ID it should return one record in xml format for Nancy Davolio.


It should return something like this (this pic is from another example just to show what the xml returned by the web service might look like):

correct result from a different example
I think I'm not doing the MyDataAdapter.Fill  correctly.

Anyone know where the error in my code is?
LVL 1
maqskywalkerAsked:
Who is Participating?
 
manbehindthecurtainCommented:
MyConnection.Open()

        comm = New SqlCommand()

        'Set the command type as StoredProcedure.
        comm.CommandType = System.Data.CommandType.StoredProcedure
        comm.CommandText = "GetEmployeeDetailsByID"
        comm.Parameters.AddWithValue("@EmployeeID", EmployeeID)

        comm.Connection = MyConnection

        'Create a DataAdapter, and then provide the name of the stored procedure.
        'adapter called myDataAdapter - name of stored procedure - connection
        MyDataAdapter = New SqlDataAdapter(comm)

        'Create a new DataSet to hold the records.
        MyDataSet = New DataSet()
0
 
Rose BabuSenior Team ManagerCommented:
Try the below code which works well for the response you expected...

<WebMethod(Description:="Simple Example")> _
Public Function GetEmployeeDetails(ByVal EmployeeID As Integer) As DataSet

	'Create objects
	Dim MyDataSet As DataSet
	Dim MyConnection As SqlConnection
	Dim MyDataAdapter As SqlDataAdapter
	Dim comm As SqlCommand

	'Create a connection to the SQL Server.
	'********************* Windows Authentication ************ FCOE-DPN1TR1 ***********************************
	MyConnection = New SqlConnection("Data Source=FCOE-DPN1TR1;Initial Catalog=Northwind;Integrated Security=True")	
	'**********************************************************************************************************

	MyConnection.Open()


	comm = New SqlCommand("GetEmployeeDetailsByID", MyConnection)

	'Set the command type as StoredProcedure.
	comm.CommandType = System.Data.CommandType.StoredProcedure
	'comm.CommandText = "GetEmployeeDetailsByID"
	comm.Parameters.AddWithValue("@EmployeeID", EmployeeID)


	'Create a DataAdapter, and then provide the name of the stored procedure.
	'adapter called myDataAdapter - name of stored procedure - connection

	'MyDataAdapter = New SqlDataAdapter("GetEmployeeDetailsByID", MyConnection)
	MyDataAdapter = New SqlDataAdapter(comm)

	'Create a new DataSet to hold the records.
	MyDataSet = New DataSet()

	'Fill the DataSet with the rows returned.
	MyDataAdapter.Fill(MyDataSet, "Employees")

	Try

	Catch ex As Exception
		Throw ex
	Finally
		MyConnection.Dispose() 'Dispose of the DataAdapter.
		MyConnection.Close() 'Close the connection.
	End Try

	Return MyDataSet

End Function

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.