Solved

returning a DataSet in a Web Service method

Posted on 2013-05-30
2
456 Views
Last Modified: 2013-05-31
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?
0
Comment
Question by:maqskywalker
2 Comments
 
LVL 1

Accepted Solution

by:
manbehindthecurtain earned 250 total points
ID: 39209688
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
 
LVL 16

Assisted Solution

by:Rose Babu
Rose Babu earned 250 total points
ID: 39209958
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
STDEVP in SQL 2 38
Help with SQL Query 23 39
Group by and order by clause 28 36
Scheduling Jobs for Execution: 4 16
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now